FYI: OOTB HR Analytics Workforce Fact table Informatica Workflow (Peoplesoft HCM 9.1)

w_wrkfc_evt_month_f - just mentioning it to OBIEE practitioners send shivers down their back.

Why? This behemoth of a fact table is the fact table of OBIA HR Analytics. It is used across all OBIA HR subject areas for cross functional analysis. It has 12 logical aliases in the BMM and it exists in all 3 of Kimball's fact table types: Transactional, period, and accumulating.

There is no other OBIA fact table that spans as many subject areas, reports, and KPIs as w_wrkfc_evt_month_f.

Which brings me to the following point.  

What if the business requirement results in modifications to this fact table?

Proceed with caution. Can the business process you're trying to measure be created using another fact table? Would it be less work to create a new star schema than to modify the workforce event tables?


Oracle provides little documentation on this fact table in terms of impacted Informatica work flows and mappings.  Any modifications to the w_wrkfc_evt_month_f fact table or its cousins ( e.g. _agg), will require your ETL developer to use the existing mappings.

Here is how  w_wrkfc_evt_month_f is populated for OOTB OBIA HR 7.9.6.3 with PSFT HCM 9.1 as the source:

Step 1: Temp table

Informatica Folder: (SDE_PSFT_91_Adaptor)

W_PSFT_WEVT_JOB_PS

1. SDE_PSFT_PersistedStage_WorkforceEvent_Job_Full
2. SDE_PSFT_PersistedStage_WorkforceEvent_Job
3. SDE_PSFT_PersistedStage_WorkforceEvent_Job_Full (Mapping)
4. SDE_PSFT_PersistedStage_WorkforceEvent_Job_Mntn (Mapping)

Step 2: Staging table

W_WRKFC_EVT_FS

1. SDE_PSFT_WorkforceEventFact_Job
2. SDE_PSFT_WorkforceEventFact_Job_Full
3. SDE_PSFT_WorkforceEventFact_Job (Mapping)

Step 3: Fact table (does not exist in Metadata layer, only used for ETL PLP)

Informatica folder: (SILOS)

 

W_WRKFT_EVT_F

1. SIL_WorkforceEventFact
2. SIL_WorkforceEventFact (Mapping)

Step 4:  Fact table PLP 2

W_WRKFC_EVT_MERGE_F

1. PLP_WorkforceEventFact_Merge
2. PLP_WorkforceEventFact_Merge_Full
3. PLP_WorkforceEventFact_Merge (Mapping)
4. PLP_WorkforceEventFact_Merge_Mntn (Mapping)

 

Step 5:  Metadata/RPD fact table
 

W_WRKFC_EVT_MONTH_F

1. PLP_WorkforceEventFact_Month_PositionHierarchy_Update_TD
2. PLP_WorkforceEventFact_Month_PositionHierarchy_Update
3. PLP_WorkforceEventFact_Month_Full
4. PLP_WorkforceEventFact_Month
5. PLP_WorkforceEventFact_Month_PositionHierarchy_Update_TD (Mapping)
6. PLP_WorkforceEventFact_Month_PositionHierarchy_Update (Mapping)
7. PLP_WorkforceEventFact_Month_Mntn (Mapping)
8. PLP_WorkforceEventFact_Month (Mapping)

For those of you following along, that's right, the data has the possibility of flowing through 21 different mappings! You must consider this before you attempt to modify this fact table. 

Unless absolutely critical, I recommend creating a new star schema.