Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Best Approach to Calculate Turnover

3 posters

Go down

Best Approach to Calculate Turnover Empty Best Approach to Calculate Turnover

Post  ejamnadas Fri Sep 19, 2014 12:21 am

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?

ejamnadas

Posts : 1
Join date : 2014-09-19

Back to top Go down

Best Approach to Calculate Turnover Empty Re: Best Approach to Calculate Turnover

Post  nick_white Fri Sep 19, 2014 6:05 am

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

nick_white

Posts : 364
Join date : 2014-01-06
Location : London

Back to top Go down

Best Approach to Calculate Turnover Empty Re: Best Approach to Calculate Turnover

Post  BoxesAndLines Fri Sep 19, 2014 2:37 pm

Try two facts, one fact captures hires, the other fact captures terminations.
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Best Approach to Calculate Turnover Empty Re: Best Approach to Calculate Turnover

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum