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.

Understanding the Payment Type Dimension in HR Analytics

One of the key dimensions in the Oracle Business Intelligence HR Applications is the Payment Type.    This dimension categorizes the different line items that are included in the Payroll fact table.  

For HR Analytics implementations sourced from Peoplesoft, the Payment Type dimension table (W_PAY_TYPE_D) is loaded from eight separate SDE (Source Dependent Extract) mappings.  Each mapping extracts a different Payment Type category.   The eight mappings are:

• SDE_PSFT_PayTypeDimension_Deductions
• SDE_PSFT_PayTypeDimension_Earnings
• SDE_PSFT_PayTypeDimension_FederalTaxes
• SDE_PSFT_PayTypeDimension_LocalTaxes1
• SDE_PSFT_PayTypeDimension_LocalTaxes2
• SDE_PSFT_PayTypeDimension_StateTaxes1
• SDE_PSFT_PayTypeDimension_StateTaxes2
• SDE_PSFT_PayTypeDimension_Total

Each of those SDE mappings inserts rows in the Payment Type Dimension staging table (W_PAY_TYPE_DS) which is then loaded into the W_PAY_TYPE_D table thru the SIL (Source Independent Load) mapping SIL_PayTypeDimension.

Each of the SDE mappings has a similar structure and flow:

The only difference between the mappings is the inital BC (Business Component) mapplet that references the specific Peoplesoft tables required to populate a particular payment type category.

An example of the mapplet flow for one of the local taxes mappings:

 
For each mapplet, we will now examine the source Peoplesoft tables and the SQL used to extract the rows for a particular Payment Type.

Mapplet:  mplt_BC_PSFT_PayTypeDimension_Deductions

SQL: 

SELECT PS_DEDUCTION_TBL.PLAN_TYPE,
       PS_DEDUCTION_TBL.DEDCD,
       PS_DEDUCTION_TBL.EFFDT,
       PS_DEDUCTION_TBL.DESCR,
       PS_DEDUCTION_CLASS.DED_CLASS,
       PS_DEDUCTION_CLASS.DED_SLSTX_CLASS
  FROM PS_DEDUCTION_TBL, PS_DEDUCTION_CLASS
 WHERE
{
    PS_DEDUCTION_TBL LEFT OUTER JOIN PS_DEDUCTION_CLASS ON
                PS_DEDUCTION_CLASS.DEDCD = PS_DEDUCTION_TBL.DEDCD
    AND PS_DEDUCTION_CLASS.PLAN_TYPE = PS_DEDUCTION_TBL.PLAN_TYPE
    AND PS_DEDUCTION_TBL.EFFDT = PS_DEDUCTION_CLASS.EFFDT
 }

Mapplet: mplt_BC_PSFT_PayTypeDimension_Earnings

SQL:  SELECT * FROM PS_EARNINGS_TBL   (no filters applied)




Mapplet: mplt_BC_PSFT_PayTypeDimension_FederalTaxes

SQL: 

SELECT PS_STATE_TAX_TBL.STATE
                  ,PS_STATE_TAX_TBL.EFFDT
                  ,PS_STATE_TAX_TBL.EFF_STATUS
FROM
 PS_STATE_TAX_TBL
WHERE STATE IN ('$E' ,'$U')

Mapplet: mplt_BC_PSFT_PayTypeDimension_LocalTaxes1

SQL:  SELECT * FROM PS_LOCAL_TAX_TBL   (no filters applied)



Mapplet: mplt_BC_PSFT_PayTypeDimension_LocalTaxes2

SQL:  

SELECT PS_STATE_TAX_TBL.STATE,
       PS_STATE_TAX_TBL.EFFDT,
       PS_STATE_TAX_TBL.EFF_STATUS
  FROM PS_STATE_TAX_TBL
 WHERE STATE LIKE 'Z%'

Mapplet: mplt_BC_PSFT_PayTypeDimension_StateTaxes1

SQL:

 SELECT PS_STATE_TAX_TBL.STATE,
       PS_STATE_TAX_TBL.EFFDT,
       PS_STATE_TAX_TBL.EFF_STATUS
  FROM PS_STATE_TAX_TBL
 WHERE STATE NOT IN ('$E', '$U') AND STATE NOT LIKE 'Z%'

Mapplet: mplt_BC_PSFT_PayTypeDimension_StateTaxes2

SQL:

SELECT PS_ST_OTH_TAX_TBL.STATE,
       PS_ST_OTH_TAX_TBL.EFFDT,
       PS_ST_OTH_TAX_TBL.TAX_CLASS
  FROM PS_ST_OTH_TAX_TBL
 WHERE STATE NOT IN ('$E', '$U') AND STATE NOT LIKE 'Z%'

Mapplet: mplt_BC_PSFT_PayTypeDimension_Total

NOTE: This mapplet generates the four standard total level payment
types which are used on cumulative fact rows.

SQL:

SELECT DISTINCT 'TOTAL_DEDUCTIONS' TOTAL_KEY FROM PS_EARNINGS_TBL
UNION
SELECT DISTINCT 'TOTAL_TAXES' TOTAL_KEY FROM PS_EARNINGS_TBL
UNION
SELECT DISTINCT 'TOTAL_GROSS' TOTAL_KEY FROM PS_EARNINGS_TBL
UNION
SELECT DISTINCT 'NET_PAY' TOTAL_KEY FROM PS_EARNINGS_TBL