Understanding the Job Dimension (W_JOB_D) in HR Anaytlics

A key entity in the Oracle BI Applications (OBIA) HR Analytics data model is the Job dimension.  The OBIA data warehouse table W_JOB_D contains information related to the various jobs in an organization.   A job is different from a position as  it would be considered at a higher summary level than a specific position.   For example,  a job might be "Vice President" while a position might be "Vice President Sales - Northern Region".


The primary columns used in the job table include:

W_JOB_D Column Name	Description	Peoplesoft 9.1 Source Column
ROW_WID	Warehouse primary key	Generated by ETL
JOB_CODE	Job Code Identifier	PS_JOBCODE_TBL.JOB_CODE
JOB_NAME	Job Short Name	PS_JOBCODE_TBL.DESCR_SHORT
JOB_DESC	Job Description	PS_JOBCODE_TBL.DESCR
JOB_FAMILY_CODE	Code for Job grouping	PS_JOBCODE_TBL.JOB_FAMILY
JOB_FAMILY_NAME	Job Family grouping name	PS_JOB_FAMILY_TBL.DESCR
JOB_FAMILY_DESCR	Job Family grouping description	UNMAPPED
JOB_LEVEL	May identify different levels within the same job	PS_JOBCODE_TBL.MANAGER_LEVEL
JOB_LEVEL_DESC	Job Level description	UNMAPPED
JOB_FUNCTION_CODE	Job Function code	PS_JOBCODE_TBL.JOB_FUNCTION
JOB_FUNCTION_NAME	Job Function short name	PS_JOB_FUNCTION_TBL.DESCR
JOB_FUNCTION_DESC	Job Function description	UNMAPPED

The W_JOB_D table has  two sets of standard domain columns.  Domain values are a set of common values used to calculate prepackaged metrics in the OBIA repository.  These values are provided by Oracle Business Analytics Warehouse to allow merging data from disparate source systems into a common set of values.   Domain value columns are typically identified by the prefix W_ in the warehouse table.  

During the ETL load process, the values from the Peoplesoft source system will be translated into the common domain values.  The domain value columns for W_JOB_D include:

FLSA Status:

W_FLSA_STAT_CODE	W_FLSA_STAT_DESC
Exempt	Exempt
Non Exempt	Non Exempt
Unknown	Unknown

EEO Job Category:

W_EEO_JOB_CAT_CODE	W_EEO_JOB_CAT_DESCR
1	First/Mid Level Officials and Managers
2	Professionals
3	Technicians
4	Sales Workers
5	Administrative Support Workers
6	Craft Workers
7	Operatives
8	Laborers and Helpers
9	Service Workers
10	Executive/Senior Level Officials and Managers
999	No EEO Reporting

 

ETL Load Process:

The Job dimension staging table (W_JOB_DS) is loaded from Peoplesoft 9.1 using a mapping called SDE_PSFT_JobDimension.   

The actual extract of the source Peoplesoft rows is done in the Business Component mapplet named mplt_BC_PSFT_JobDimension.

The source table is PS_JOBCODE_TBL with additional lookups to PS_JOBFUNCTION_TBL and PS_JOB_FAMILY_TBL to get descriptions.

The only filter applied to the selection of the columns is for the incremental load where PS_JOBCODE_TBL.LAST_UPDATE_DATE >= $$LAST_EXTRACT_DATE is used to only select rows updated since the last ETL run.

Note: W_JOB_D is considered a Slowly Changing Dimension so it can be configured to process SCD-2 style updates where a new row is generated if any of the important columns are changed in the source.