Understanding the W_PAYROLL_A aggregate table

Oracle's HR Analytics solution includes a subject area devoted to analyzing Compensation metrics.   Within that subject area is a Payroll star schema which is built off paycheck data.   For Peoplesoft implementations, the primary source tables used to populate the Payroll fact are:

• PS_PAY_CHECK
• PS_PAY_DEDUCTION
• PS_PAY_EARNINGS
• PS_PAY_OTH_EARNS
• PS_PAY_TAX

After the W_PAYROLL_F table is loaded, a Post Load Process mapping is run in Informatica to populate an aggregate table named W_PAYROLL_A which rolls up the Payroll fact at the Month level by default as well as the dimensions Employee Demographics, Job Category, and Pay Type Groups.   The purpose of this aggregate table is to improve query performance for those reports that do not require more detailed level data.  

Payroll Aggregate Star Schema

In order to load the W_PAYROLL_A table at a different time grain, you must set the GRAIN parameter in DAC (Data Warehouse Administration Console).      Out of the box, the parameter is set to MONTH but other possible values are DAY, WEEK, QUARTER, and YEAR.     Your business requirements and report performance will determine the proper setting for this parameter.  To change the GRAIN parameter follow these steps:

1. In DAC, go to the Design view, and select a container
2. From the Tasks tab, find the task named  PLP_PayrollAggregate_Load.
3. Display the Parameters subtab, and add a parameter with the name $$GRAIN.
4. Define the value as one of the following: 'DAY', 'WEEK', 'MONTH', 'QUARTER' or 'YEAR'.
5. Select Static as the parameter type.
6. Save the task.

W_PAYROLL_A is loaded from the base table W_PAYROLL_F in the initial full load ETL run by the workflow PLP_PayrollAggregate_Load_Full.  On subsequent incremental runs, the aggregate table is not rebuilt but is instead updated by adding new records or applying changed rows to the existing aggregate rows.   

The incremental process is done in two steps:

1. There are new records in the W_PAYROLL_F table, which were inserted since the last ETL load. The new records are inserted into the W_PAYROLL_A_TMP table. This step is part of the post oad-processing workflow, and the mapping is called 'PLP_PayrollAggregate_Extract'.
 
2. The W_PAYROLL_A_TMP table is processed by joining it with the W_PAYROLL_A aggregate table to insert new or update existing rows to the aggregate table. This step is part of the post load-processing workflow, and the mapping is called 'PLP_PayrollAggregate_Load'.   The incremental refresh process is based on the fact that there are typically no updates to the existing fact rows, just new rows that may reverse or adjust other payroll data.   Using the ITEM_AMT column from W_PAYROLL_F, the existing aggregate row is updated by adding that amount to the current aggregate amount.   

Aggregate ITEM_AMT = Old ITEM_AMT (from W_PAYROLL_A) + New ITEM_AMT (from W_PAYROLL_A_TMP)