OBIA 11g: SILO_SIL_EMPLOYEEDIMENSION Error Out With Duplicate Records 'ORA-00001: unique constraint (PROD_DW.W_EMPLOYEE_D_U1) violated' (Doc ID 2141159.1)

Last updated on MAY 23, 2017

Applies to:

Business Intelligence Applications Consumer - Version 11.1.1.7.1 to 11.1.1.8.1 [Release 11g]
Information in this document applies to any platform.

Symptoms

On : ODI 11.1.1.7.0 version, DataWarehouse
java version "1.7.0_55"
OBI APPS: Build: 11.1.1.7.1.BISERVER_11.1.1.7.0OBIEEBP_LINUX.X64_130919.2217
Release Version: Oracle Business Intelligence 11.1.1.7.1
Package: 130919.1113.000

EBS 12.1.3 is the source instance

ACTUAL BEHAVIOR
---------------

Receiving an error when running the ETL against an Oracle EBS 12.1.3 instance related to SILO_SIL_EMPLOYEEDIMENSION.

Failed with following error:

Change employee data result in duplicate issues in the BI data warehouse.

For example of an edit that "breaks" the ETL is changing an employee's supervisor.  Upon changing employee data to reflect this change, the duplicate error began appearing. This issue now occurs with every run of the ETL.

You are following temporary workaround  by deleting the duplicate rows in the data warehouse

DELETE from w_employee_ds
WHERE (INTEGRATION_ID, SRC_EFF_FROM_DT) IN ( SELECT S.INTEGRATION_ID, SRC_EFF_FROM_DT
from PROD_DW.I$_1028134510_1 S
inner join PROD_DW.W_EMPLOYEE_T1_D SCD1 on (SCD1.DATASOURCE_NUM_ID = S.DATASOURCE_NUM_ID and
 SCD1.INTEGRATION_ID = S.INTEGRATION_ID)
where S.IND_UPDATE IN ('I','S')
INTERSECT
SELECT INTEGRATION_ID, EFFECTIVE_FROM_DT FROM W_EMPLOYEE_D)



You have implemented the tech note from OBIA 11.1.1.8.1 - SIL_EmployeeDimension Fails with Error “ORA-00001: unique constraint (DW.W_EMPLOYEE_D_U1) violated” ( Doc ID 1984358.1 ).

Changes

NA

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