Turnover Rate % metric in HR Analytics

One of the more widely used metrics in HR Analytics is the Turnover Rate %.  This can be an effective high level indicator on an organizational level to drive deeper analysis into why certain departments or jobs are able to retain employees more effectively than others.

In the Oracle BI HR Analytics application, the metric Employee Turnover Rate % is defined in the metadata repository (RPD) within the logical fact table:  Fact - HR - Compound Employee Event Information and Compensation (Workforce).

The metric is a derived metric which means it is based on other metrics.   In this case, Employee Turnover Rate % is defined as:


CASE  WHEN "Core"."Fact - HR - Operation (Workforce)"."Average Employee Headcount" <> 0 THEN 100.0 * "Core"."Fact - HR - Event Information (Workforce)"."Employee Termination Count" / "Core"."Fact - HR - Operation (Workforce)"."Average Employee Headcount" END 

or to put it more simply:

Total Employee Terminations / Average Employee Headcount  
 

The top half of the equation is a metric based on the Workforce Event fact table and a metric named Termination Count which has a definition of:

 CASE  WHEN "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_EMPLOYMENT_D"."W_EMPLOYEE_CAT_CODE" = 'EMPLOYEE' THEN "Oracle Data Warehouse"."Catalog"."dbo"."Fact_W_WRKFC_EVT_MONTH_F_Event"."TERM_EVENT_IND" ELSE 0 END 

That definition translates to "Sum up the Termination Event Indicator (1,0) for all events where the associated Employee is designated as a regular employee."

The bottom half of the equation is another derived metric called Average Employee Headcount.   It is calculated by adding the headcount for the period being reported on to the headcount of the prior period and dividing by two.  NOTE:  It is important to understand that the period and prior period are dependent on the filtering of the query used to display the metric.   If the query is at a year level, the prior period will be the end of the previous year.   If the query is at the month level, the prior period will be the end of the previous month.  

The calculation for Employee Headcount is:

 CASE  WHEN "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_EMPLOYMENT_D"."W_EMPLOYMENT_STAT_CODE" = 'A' AND "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_EMPLOYMENT_D"."W_EMPLOYEE_CAT_CODE" = 'EMPLOYEE' THEN "Oracle Data Warehouse"."Catalog"."dbo"."Fact_W_WRKFC_EVT_MONTH_F_Snapshot"."HEADCOUNT" ELSE 0 END 

Only active employees are included in the metric.

Here is an example of the definition of Prior Period Headcount.    This calculation is repeated for each Logical Table Source for each Time dimension level.   The only difference between this calculation and the one for Employee Headcount is the physical table alias used for the fact.   In this case, the Period_Ago alias is used.

 
   The SQL generated by the Employee Turnover Rate % metric can be very complex due to the multiple table sources involved and the necessity to use the LAST function on a series of rows to determine the last headcount value for a period.  A minimum of three SQL statements will be generated to calculate the metric.

Some organizations take the opposite view when looking at retention by examining retention rates instead.    In that case, the retention rate is calculated by subtracting the turnover rate from the value 1.

It is common in OBIA HR Analytics implementations to customize the Turnover metrics by using different underlying metrics to calculate the headcount or the average headcount or defining a more restrictive set of conditions to count terminations.