Tuesday, February 19, 2013

Understanding the Workforce Event Type configuration file for HR Analytics

Configuring the Workforce Event Type CSV file in HR Analytics is one of the most critical steps in order to successfully capture the events related to employee assignments.      This file assigns each combination of Action/Reason Code to a set of pre-defined domain values to identify Hires, Terminations, and other job related activities.   For each combination, a set of flags is also configured to identify if the Action/Reason results in an increase or decrease in headcount, an organization change, or other event which may be used in HR analytics metrics.
For Peoplesoft implementations the file domainValues_Wrkfc_EventType_psft.csv file is used to populate persisted staging table, W_PSFT_DMN_WEVT_TYP_PS, which is then used to load data into the dimension table, W_WRKFC_EVENT_TYPE_D.

The default domainValues_Wrkfc_EventType_psft.csv file contains event groups, sub groups, and events, which are supported by the workforce event dimension. Using the columns EVENT_TYPE, EVENT_REASON, ORG_CHANGE_FLG, JOB_CHANGE_FLG, POS_CHANGE_FLG, GRD_CHANGE_FLG, LOC_CHANGE_FLG, and SUP_CHANGE_FLG, map a combination of source system attribute changes to a conformed warehouse event.

The default domain values for the event types include;

By default, the following events are defined in the domainValues_Wrkfc_EventType_psft.csv file under the column heading, EVENT_NAME:

  •     International Transfer End
  •     Additional Assignment End
  •     Layoff
  •     Assignment Change
  •     Promotion
  •     Renew Contract
  •     Assignment Start
  •     International Transfer
  •     Transfer
  •     New Hire
  •     Rehire
  •     Involuntary Termination
  •     Retirement
  •     Resignation
The first step in configuring the Event Type domain file is to collect the list of all Action/Action Reason combinations from the source Peoplesoft system using this SQL:

    ORDER BY 1;

Then edit the file $PMServer\LkpFiles\domainValues_Wrkfc_EventType_psft.csv file and map each of the combinations above to one of the combinations of:
  •     EVENT_TYPE
  •     EVENT_CODE
  •     EVENT_NAME
The value in the EVENT_CODE column should be unique for each combination of the EVENT_TYPE and EVENT_REASON columns. 

Note: In the CSV file there is a column called EXCLUDE_FLG.   Setting this value to Y for  a PeopleSoft Action/Action Reason will prevent it  from being picked up as events in the Workforce Event Fact are loaded. Excluding some events that are not required for analysis helps limit the size of the Workforce Event Fact table.

1 comment:

  1. Do we have to poulate domainValues_Pay_Type_Grp_Code_psft.csv file ? Looks like the sql is missing in the configuration guide for bi apps Btw, great blog. Enjoyed reading it.