I am trying to determine the best way to model the scenario of employee turnover for a Dimensional Model. I am not sure if its best to include the Termination_Count and Headcount in the same measure.
I currently have a headcount measure with both termed and headcount:
**Headcount Measure:**
Employee_id
Department
Employee_count
Termed_count
Month
So each individual employee will have a row created for them if they are active during the month or if they are terminated during the month.
How have other people worked with employee turnover issue.
Don't track Headcount and Turnover in the same table, they have different grains.
Headcount: being a semi-aditive measure you need a snapshot fact table counting employees per department, salary level, bureau and whatever other dimensions you need. It should store these values once per day;
Turnover: have a Hire/Fire transaction table with three measures: employees_hired (0/1), employees_fired (0.1) and net_employee_variation (=1/0/+1). On the employee dimension you can have a "date_hired" and "date_left_ as attributes to allow, for example, counting time between the two events.
But you shouldn't mix what is a transaction fact table with a snapshot fact table.