Understanding the Job Persisted Staging table in HR Analytics

In Oracle HR Analytics for Peoplesoft, one of the key tables used within the Workforce Profile subject area is the Workforce Event Fact table (W_WRKFC_EVT_MONTH_F).   The process of loading W_WRKFC_EVT_MONTH_F is very complex, particularly when it comes to incremental loads.

This diagram captures all of the tables and the data flow involved in populating the Workforce Event fact:

Source: Implementing HR Analytics using PeopleSoft Adaptors (Oracle Support)


The starting point for the workforce event fact load begins with extracting data from the Peoplesoft PS_JOB table and loading it into a persisted staging table called W_PSFT_WEVT_JOB_PS.   The PS table reflects the same structure as the PS_JOB table with additional columns added to for calculations and lookup values to improve ETL load processing. 

This diagram shows the flow for both the initial full load and incremental load of the persisted staging table:

 Source: Implementing HR Analytics using PeopleSoft Adaptors (Oracle Support)

The initial full load brings in columns primarily from PS_JOB with additional columns coming from PS_PER_ORG_INST and PS_PER_ORG_ASGN.  The data is loaded directly into W_PSFT_WEVT_JOB_PS.   During an incremental load, the same data is extracted into a temporary table, W_PSFT_WEVT_JOB_TMP, and then the temporary table is compared to the existing PS table to determine what changes have occurred.  The reason for this approach is to ensure that any incremental changes are detected and the results of those changes are properly applied to any subsequent job records that had been loaded previously.   The persisted stage table is never truncated.  

The persisted stage table maintains a number of "change indicator" flags on each row.  This is because a single transaction in Peoplesoft may result in multiple changes between consecutive PS_JOB rows.   For example, if an employee transferred into a new position in a new department, the persisted stage table would set flags for a Job Change and Department Change on the new record.

From a DAC perspective the ETL processing of the Persisted Stage table is handled by the following workflows/tasks:

For the initial full load:

SDE_PSFT_PersistedStage_WorkforceEvent_Job_Full inserts all PS_JOB records into W_PSFT_WEVT_JOB_PS.
For subsequent incremental loads: 

SDE_PSFT_PersistedStage_WorkforceEvent_Job_Incr  loads all PS_JOB records into  W_PSFT_WEVT_JOB_TMP

SDE_PSFT_PersistedStage_WorkforceEvent_SupOld  manages the tracking of supervisor position changes 

SDE_PSFT_PersistedStage_WorkforceEvent_Job_Mntn compares the _TMP table to the  _PS tables to identify differences and update the _PS table accordingly.

Note:  In the out of the box configuration for the mappings,  the incremental load process loads all rows from PS_JOB into the _TMP table each time it runs.   This is a potential cause of performance issues on the incremental load.     To alleviate that performance issue, Oracle's recommended solution is:
 
1. Add a filter to the incremental extract SQL in mapping SDE_PSFT_PersistedStage_WorkforceEvent_Job_Incr to only process changed records where PS_JOB.LASTUPDDTTM >= $$LAST_EXTRACT_DATE

2. Add a delete trigger in Peoplesoft to PS_JOB to update the last update timestamp of the job record whenever a delete occurs