Best Approach to Calculate Turnover

View previous topic View next topic Go down

Best Approach to Calculate Turnover

Post  ejamnadas on 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

View user profile

Back to top Go down

Re: Best Approach to Calculate Turnover

Post  nick_white on 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 : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Best Approach to Calculate Turnover

Post  BoxesAndLines on Fri Sep 19, 2014 2:37 pm

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

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

View user profile

Back to top Go down

Re: Best Approach to Calculate Turnover

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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