OBIA 11g Duplicates For Employee Dimension (Doc ID 1577858.1)

Last updated on JANUARY 31, 2017

Applies to:

Business Intelligence Applications Consumer - Version 11.1.1.7.0 and later
Information in this document applies to any platform.

Symptoms

During a data load of the BI Applications 11.1.1.7.1 module Project Analytics (Project Resource Management) duplicates are found during the ETL on the Employee dimension.
The load process stops with an error on the U1 index (datasource number, integration ID and source effective from date).

The following error is seen in ODI:

ODI-1228: Task TABLE_MAINT_PROC (Procedure) fails on the target ORACLE connection BIAPPS_DW.
Caused By: java.sql.SQLException: ORA-20000: Error creating Index/Constraint : W_EMPLOYEE_DS_U1 => ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
ORA-06512: at line 213

The load plan has been created for the Fact Group 'Project Resource Management"(PRJRSCMNGT_FG).

When checking the employee data on the source (EBS 11.5.10) table PER_ALL_PEOPLE_F there are no duplicates.

So the question is why the ETL creates duplicate rows?

In which steps does this occur?

The following mappings load data into W_EMPLOYEE_DS

The sde_ora_employeedimension step loads data with Integration ID starting with 'PER~'.
The sde_ora_employeedimension_nonemployee step loads data with Integration ID starting with 'RES~'.

Duplicates are found with integration ID starting with 'PER~', so it looks like these were created with the first step.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms