The Workforce Deployment Subject Area contains two logical facts: balance and event information.
Workforce Balance Information – for reporting on balances at a point in time for example, Month End Headcount and Salary.
Workforce Event Information – for reporting on events occurring in a period of time for example, Number of Terminations per Year.
Most reports should use either one or the other logical fact, and it is important to know the difference for the report to make sense. It is possible to design reports that use both logical facts together; however, care must be taken over dimension usage.
Workforce Balance Information
The Workforce Balance fact shows status at a given point in time. The time dimension should be used with this fact. If the time dimension is not used, the status for all time periods will be calculated and then only the current one returned. This is very inefficient.
Except for the current period, whichever time period is used (Year, Quarter, Month, and Day) the fact gives the status as of the period end date. For the current period the behaviour can be configured either to show status as of the future period end date or as of the current date. This is controlled by the HR_WRKFC_MAX_EFFECTIVE_DT session variable. For more about this variable, see task 'How to Control Future Transaction Data Visibility'.
This fact does not include terminated workers. The status is given only for workers who have not been terminated. For E-Business Suite this is inclusive of the 'Actual Termination Date' which is the last day of employment where the worker is still active on the system.
Workforce Event Information
The Workforce Event fact shows events that have happened within a given period of time. The time dimension must be used with this fact otherwise all time periods will be shown, which would result in much slower performance. Events are shown which have occurred within each time period. For the current period the behaviour can be configured either to show events up to the current date, or all future events in the period as well. This is controlled by the HR_WRKFC_MAX_EFFECTIVE_DT session variable. For more about this variable, see task 'How to Control Future Transaction Data Visibility'.
This fact does include termination events, and defines the termination event date to be the first day the worker is inactive. For E-Business Suite this is the day after the 'Actual Termination Date' which is the last day of employment where the worker is still active on the system.
Workforce Event Dimension
The Workforce Event Fact joins to the Workforce Event Dimension at detail level. The dimension stores information about each event, such as the type of event (for example, Hire, Termination) along with a number of change flags to indicate whether any of the main dimensions changed (for example, Organization, Job, and Grade).
The Workforce Balance Fact joins to the Workforce Event Dimension at summary level. This means that reports cannot use filters or attributes based on the dimension. However for reports combining balance and event information it is possible to create metrics based on the Workforce Event Dimension.
The Workforce Event Dimension is useful in two main ways:
Creating detail reports such as 'Show me all the terminations in 2011' or 'Show me all the organization changes of workers who have less than 1 year of service'.
Creating event based metrics. For example, suppose you wanted to report on 'Headhunted' events. In the workforce event domain mapping you map terminations with a reason of 'Headhunted' to a new event 'Headhunted' (mapped to sub-group Voluntary Termination and group Termination):
The 'Headhunted' detail report should be straightforward – a listing of attributes where the Workforce Event Dimension Event Code = 'HEADHUNTED'.
The summary report needs to show numbers of terminations, including voluntary, involuntary and headhunted. The other metrics are already defined. Add a new metric 'Headhunted Count' to the event logical fact defined as:
CASE WHEN "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_WRKFC_EVENT_TYPE_D"."W_EVENT_CODE" = 'HEADHUNTED' THEN 1 ELSE 0 END
Set the aggregation rule of this metric to be "Sum".