Design to calculate CHURN (movement of employees between business units)

All,

I am working on a HR Analytics datawarehouse and I need to design (ETL and Data Model) a way to capture what is called "churn". Currently, my DW has the following: Workforce Event Fact, Cost Center Dimension, Cost Center Dimension Hierarchy. The CC Dim Hierarchy is joined to the CC Dimension which is directly joined to the Workforce Event Fact table. The CC Hierarchy is a flattened 15 level SCD that has CC ID as the node values along with the description. The CC Dimension is also a SCD Type 2 table.

The business has defined "Churn" as employees who have moved from one "Business Unit" to another. "Business Unit" is equivalent to Level 2 of the CC Dim Hierarchy. So an employee can move from one CC to another..but in order to qualify as Churn..he/she must move across level 1 business unit values (as opposed to deeper within the same level 1 business unit).

I am already capturing Cost Center based on each Workforce event (promotion, location change, etc). The requirement is to capture Current "Business Unit", "Previous Business Unit" as well as some form of aggregate count of "Churn" for each Business Unit. I assume this means totals # of transfers INTO and OUT OF a particual Business Unit.

Any thoughts on how to approach this from a design perspective?

Thanks.

If I have understood your problem correctly, you mean if X2, Y2 are level2 of A1 level1 unit and C2, D2 are level2 of B1 level unit, then a churn is when there is a movement from X2 to C2/D2 or other way round, but movement from X2 to Y2 or other way round will not be considered as a churn as both these belong to the same level1.

to select a churn, you can use below type of query

select t1.emp, old.level1ccid, new.level1ccid
from
table1 (whichever is your source to find out movement) t1,
CCDH old,
CCDH new
where
t1.movementdate=
and t1.employee ccid=old.level2 ccid
and t1.employee ccid=new.level2 ccid
and old.level1ccid <> new.level1ccid

if you have a table with 5 fields, emp, previous unit, current unit, check unit and churn count

in the first load populate, all 3 units with current unit and churn count 0

from there on use the above query to check for churn, and then populate emp, previous unit and current unit

now compare current unit with check unit if it is different update churn count by 1 ( various methods can be used)

after updating churn count, update check unit with current unit so that false updates doesnt occur

Thanks for the response. Essentially I am capturing every event as a fact row. Based on your suggestion, I assume I would lookup to the Dimension Hierarchy table using the BaseID to find the Level1 ID and compare to the last Level1 ID to check if there has been a change. Will try it out. Thanks.

One transaction fact table for employees leaving a business unit. One transaction fact table for employees entering a business unit. You could probably combine the tables as well. Sum Entering - Sum Leaving = Churn.