Moving PeopleSoft HR Data to an OBIA Data Warehouse

An excellent document that describes the inner-workings of the ETL process for Oracle BI Human Resources Analytics for PeopleSoft sources was posted on the Oracle Support site in late September. The document is titled "Implementing HR Analytics using PeopleSoft Adaptors." If you want to understand how HR data moves from the PeopleSoft source to the target OBIA data warehouse, this is the best place to start. To find the document, search on Oracle Support for document 1326742.1.

Workforce Profile Bug

If you are using Oracle BI Application HR Analytics version 7.9.6.3, be aware of the following bug in the Workforce Profile subject area:

In the ETL mapping PLP_WorkforceEventGroupDimensionAggregate_Load there is incorrect SQL in the Source Qualifier which effects the incremental load. The SQL is over-ridden in the full load so this SQL is only run in the incremental load.

The SQL does a lookup to check if a row already exists in W_WRKFC_EVENT_GROUP_D for the key columns. However, the subquery to do the lookup has an incorrect join on one of the columns which results in all the rows being duplicated in W_WRKFC_EVENT_GROUP_D. This in turn impacts the W_WRKFC_EVT_A aggregate fact table causing additional incorrect rows to be inserted into the table.

Lookup SQL:

SELECT
DISTINCT
W_EVENT_GRP_CODE
,W_EVENT_SUBG_CODE
,DATASOURCE_NUM_ID
,TENANT_ID
FROM
W_WRKFC_EVENT_TYPE_D D
WHERE
    D.ETL_PROC_WID = $$ETL_PROC_WID AND
NOT EXISTS
    (
    SELECT 1
    FROM
        W_WRKFC_EVENT_GROUP_D AD
    WHERE
        D. W_EVENT_GRP_CODE = AD.W_EVENT_SUBG_CODE AND
        D. W_EVENT_SUBG_CODE = AD. W_EVENT_SUBG_CODE AND
        D.DATASOURCE_NUM_ID = AD.DATASOURCE_NUM_ID AND
        D.TENANT_ID = AD.TENANT_ID

)

In the above code, the line:

D.W_EVENT_GRP_CODE = AD.W_EVENT_SUBG_CODE AND

Should be:

D. W_EVENT_GRP_CODE = AD.W_EVENT_GRP_CODE AND

Oracle HR Analytics: Recruitment

The Recruitment subject area allows companies to monitor different stages in the hiring process. Analyses are available to monitor the steps from the creation of a requisition to the hiring of a new employee. The Recruitment subject area will provide insight into the organizations with a high vacancy rate. Measuring the effectiveness of a recruiter, or a specific hiring source, is possible with data in this subject area. The use of performance bands can allow for analysis into where the best new hires are coming from, and who is recruiting them. The rate at which potential new hires accept or reject offers can also be analyzed.

The following data warehouse tables are referenced by the Recruitment subject area in the OBIA 7.9.6.3 RPD:

• W_AGE_BAND
• W_BUSN_LOCATION_D
• W_DAY_D
• W_EMP_DEMOGRAPHICS
• W_EMPLOYEE_D
• W_EMPLOYMENT_D
• W_EMPLOYMENT_STAT_CAT_D
• W_GEO_COUNTRY_D
• W_HR_POSITION_D
• W_INT_ORG_D
• W_INT_ORG_DH
• W_JOB_D
• W_JOB_RQSTN_AGE_BAND_D
• W_JOB_RQSTN_D
• W_MONTH_D
• W_PAY_GRADE_D
• W_PERFORMANCE_BAND_D
• W_POSITION_DH
• W_QTR_D
• W_RCRTMNT_APPL_A
• W_RCRTMNT_EVENT_F
• W_RCRTMNT_EVENT_TYPE_D
• W_RCRTMNT_HIRE_A
• W_RCRTMNT_RQSTN_A
• W_RCRTMNT_SOURCE_D
• W_STATUS_D
• W_WRKFC_BAL_A
• W_WRKFC_EVT_MONTH_F
• W_YEAR_D