Oracle has posted a document listing the bugs fixed in the recent 7.9.6.4 release of Oracle Buiness Intelligence Applications (OBIA)
If you have an Oracle Support account, check out the following note: [ID 1528774.1]
Bugs specifically related to Human Resources are:
Bug Description
-------- ----------------------------------------------------------------------------------------
12881153 REMOVED / DISABLE FUTURE DATED SECURITY FOR CERTAIN USERS/ROLES
12976826 MODIFIED W_WRKFC_EVT_MONTH_F EMPLOYEE HIRE DATE NOT UPDATING AFTER INCREMENTAL LOAD
13044925 PYRL:EBS12.1.3 LOAD FAILURE TASK - SIL_PAYTYPEDIMENSION
13093949 OBIA HR ANALYTICS - MISALIGNED SUPERVISOR WID IN MONTH FACT SNAPSHOT
13261654 ISSUE WITH WORKFORCE EVENT DURING INCREMENTAL LOAD
13332113 WRKFC:AGG:PLP_WORKFORCEEVENTGROUPDIMENSIONAGGREGATE_LOAD HAS INCORRECT SQL IN SQ
13368371 SOURCE EVENT CATEGORY CODE / NAME (EMPLOYEE CLASS) IS SET INCORRECTLY
13407122 NEW ASSIGNMENT NOT REFLECTING IN W_WRKFC_EVT_F
13490355 PERFORMANCE ISSUE FOR PLP_WORKFORCEEVENTFACT_POW_MNTN AND PLP_WORKFORCEEVENTFACT_AG
13529271 ORA-01452 ERROR CREATING W_CODE_D_U1 WHEN SOURCE DATA HAS SOURCE_CODE=UNKNOWN
13572719 POSITION_DESC GETTING TRUNCATED TO 80 CHARACTERS
13618453 MARKING ROLLBACKED PAYROLL RECORDS AS DELETED, MARKS CALCULATED ROWS AS DELETED
13727955 HEADCOUNT METRIC INCLUDED FUTURE HEADCOUNT CHANGES AT DETAILED LEVEL
14127767 EXISTING RECRUITMENT EVENT RECORD IS NOT UPDATED AFTER MODIFICATION TO EVENT DATE
Wednesday, February 27, 2013
Naked Statistics book recommended by Stephen Few
Stephen Few, THE industry expert on building effective dashboards, has a recent blog post recommending a new book on statistics called:
Naked Statistics: Stripping the Dread from the Data, by Charles Wheelan (W. W. Norton & Company, 2013).
Follow this link for his review of the book: Perceptual Edge Blog Post
Naked Statistics: Stripping the Dread from the Data, by Charles Wheelan (W. W. Norton & Company, 2013).
Follow this link for his review of the book: Perceptual Edge Blog Post
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
This diagram captures all of the tables and the data flow involved in populating the Workforce Event fact:
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:
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
Subscribe to:
Posts (Atom)