Understanding the Employment Dimension in HR Analytics

The Employment dimension is one of the key dimension used for reporting in the Oracle HR Analytics data model.  The dimension allows for reporting on various aspects of the type of employee who either works for or is contracted to an organization.   It is associated in the logical data model with Workforce Profile, Workforce Event, Payroll, and Absence Event facts.   The physical data warehouse table which stores the employment data is W_EMPLOYMENT_D.


















The Employment dimension staging table is loaded from Peoplesoft using the Informatica mapping SDE_PSFT_EmploymentDimension.

Withing that mapping, the mapplet mplt_BC_PSFT_EmploymentDimension performs the actual data extraction from the Peoplesoft PS_JOB table by selecting the necessary columns and doing a GROUP BY to return all the unique combinations of values for attributes like Employee Status, Full Time Flag, etc.  

The SQL in the mapplet is:

SELECT
C.EMPL_STATUS,
MAX(C.FULL_PART_TIME) FULL_PART_TIME,
C.FULL_OR_PART_TIME,
C.EMPL_TYPE,
C.EMPLOYMENT_SUB_STAT_CODE,
C.HOURLY_OR_SALARIED,
C.UNION_CODE,
C.EMPLOYEE_CAT_CODE,
MAX(C.FLSA_STATUS) FLSA_STATUS,
C.UNION_OR_NON_UNION,
C.PAY_CYCLE_CODE,
C.EXEMPT_OR_NON_EXEMPT
FROM
(
SELECT
B.EMPL_STATUS,
B.FULL_PART_TIME,
CASE WHEN B.FULL_PART_TIME = 'F' THEN 'Y' WHEN B.FULL_PART_TIME = 'P' THEN 'N' ELSE '?' END FULL_OR_PART_TIME,
B.EMPL_TYPE,
B.EMPL_STATUS EMPLOYMENT_SUB_STAT_CODE,
CASE WHEN B.EMPL_TYPE = 'H' OR B.EMPL_TYPE = 'E' THEN 'Y' ELSE 'N' END HOURLY_OR_SALARIED,
B.UNION_CD UNION_CODE,
B.EMPL_CLASS EMPLOYEE_CAT_CODE,
B.FLSA_STATUS,
CASE WHEN B.UNION_CD = '?'  THEN 'N' ELSE 'Y' END UNION_OR_NON_UNION,
B.COMP_FREQUENCY PAY_CYCLE_CODE,
CASE WHEN B.FLSA_STATUS = 'N' THEN 'N' ELSE 'Y' END EXEMPT_OR_NON_EXEMPT
FROM
(
SELECT
CASE WHEN A.EMPL_STATUS IS NULL THEN '?' WHEN A.EMPL_STATUS = ' ' THEN '?' ELSE A.EMPL_STATUS END EMPL_STATUS,
CASE WHEN A.FULL_PART_TIME IS NULL THEN '?' WHEN  A.FULL_PART_TIME = ' ' THEN  '?' ELSE A.FULL_PART_TIME END FULL_PART_TIME,
CASE WHEN A.EMPL_TYPE IS NULL THEN '?' WHEN A.EMPL_TYPE = ' ' THEN  '?' ELSE A.EMPL_TYPE END EMPL_TYPE,
CASE WHEN A.EMPL_CLASS IS NULL THEN '?' WHEN A.EMPL_CLASS = ' ' THEN '?' ELSE A.EMPL_CLASS END EMPL_CLASS,
CASE WHEN A.COMP_FREQUENCY IS NULL THEN '?' WHEN A.COMP_FREQUENCY = ' ' THEN '?' ELSE A.COMP_FREQUENCY END COMP_FREQUENCY,
CASE WHEN A.UNION_CD IS NULL THEN '?' WHEN A.UNION_CD = ' ' THEN  '?' ELSE A.UNION_CD END UNION_CD,
CASE WHEN A.FLSA_STATUS IS NULL THEN '?' WHEN A.FLSA_STATUS = ' ' THEN '?' ELSE A.FLSA_STATUS END FLSA_STATUS
FROM
PS_JOB A ) B ) C
GROUP BY
C.EMPL_STATUS,
C.FULL_OR_PART_TIME,
C.EMPL_TYPE,
C.EMPLOYMENT_SUB_STAT_CODE,
C.HOURLY_OR_SALARIED,
C.UNION_CODE,
C.EMPLOYEE_CAT_CODE,
C.UNION_OR_NON_UNION,
C.PAY_CYCLE_CODE,
C.EXEMPT_OR_NON_EXEMPT

The mapping also includes a mapplet mplt_SA_PSFT_EmploymentDimension which does the translation of the Peoplesoft column values to generic domain values for  Exempt Flag,Employment Status code,Full TIme Flag and Employee Category code.   The lookups for these columns are done using the CSV files that would have been configured as part of the initial setup of the OBIA application.