Best Approach to Calculate Turnover
3 posters
Page 1 of 1
Best Approach to Calculate Turnover
I'm trying to determine the best model to allow for calculating employee turnover.
If i head a Monthly Employee Snapshot with employee_count measure for each row equal to 1, which would give me a headcount.
Terminations are a transaction, which would be in a cube. How would I go about combining these two measures to make a turnover calculation.
Is there a better approach?
If i head a Monthly Employee Snapshot with employee_count measure for each row equal to 1, which would give me a headcount.
Terminations are a transaction, which would be in a cube. How would I go about combining these two measures to make a turnover calculation.
Is there a better approach?
ejamnadas- Posts : 1
Join date : 2014-09-19
Re: Best Approach to Calculate Turnover
Hi - I would have an Employee Fact table that has Dimensions for, amongst other things, Start Date and Termination Date. This then allows you to calculate headcount, turnover, etc. at any point in time and doesn't rely on snapshot tables. So if your turnover calculation was based on the headcount on 31/08/2014 and any termination in August then your queries (this is pseudocode rather than actual SQL) would look something like this:
SELECT SUM(COUNTER) 'HEAD_COUNT' FROM EMP_FACT WHERE START_DATE <= '31/08/2014' AND TERMINATION_DATE >= '31/08/2014
SELECT SUM(COUNTER) AS 'TERM_COUNT' FROM EMP_FACT WHERE TERMINATION_DATE BETWEEN '01/08/2014' AND '31/08/2014'
and then combine the two results to give you your turnover. Any BI tool should be able to handle these three queries easily.
Regards,
Nick
SELECT SUM(COUNTER) 'HEAD_COUNT' FROM EMP_FACT WHERE START_DATE <= '31/08/2014' AND TERMINATION_DATE >= '31/08/2014
SELECT SUM(COUNTER) AS 'TERM_COUNT' FROM EMP_FACT WHERE TERMINATION_DATE BETWEEN '01/08/2014' AND '31/08/2014'
and then combine the two results to give you your turnover. Any BI tool should be able to handle these three queries easily.
Regards,
Nick
nick_white- Posts : 364
Join date : 2014-01-06
Location : London
Re: Best Approach to Calculate Turnover
Try two facts, one fact captures hires, the other fact captures terminations.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» Type 2 Approach
» Design to calculate CHURN (movement of employees between business units)
» Daily net sales - single aggregate fact table or calculate from two
» Value Banding
» Logic behind Top Down and Bottom Up approach
» Design to calculate CHURN (movement of employees between business units)
» Daily net sales - single aggregate fact table or calculate from two
» Value Banding
» Logic behind Top Down and Bottom Up approach
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|