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