My Oracle Support Banner

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

Last updated on JUNE 01, 2020

Applies to:

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


During a data load of the BI Applications 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.




To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.