OBIA 11g: SILO_SIL_EMPLOYEEDIMENSION Error Out With Duplicate Records 'ORA-00001: unique constraint (PROD_DW.W_EMPLOYEE_D_U1) violated'
Last updated on MAY 23, 2017
Applies to:Business Intelligence Applications Consumer - Version 188.8.131.52.1 to 184.108.40.206.1 [Release 11g]
Information in this document applies to any platform.
On : ODI 220.127.116.11.0 version, DataWarehouse
java version "1.7.0_55"
OBI APPS: Build: 18.104.22.168.1.BISERVER_22.214.171.124.0OBIEEBP_LINUX.X64_130919.2217
Release Version: Oracle Business Intelligence 126.96.36.199.1
EBS 12.1.3 is the source instance
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')
SELECT INTEGRATION_ID, EFFECTIVE_FROM_DT FROM W_EMPLOYEE_D)
You have implemented the tech note from OBIA 188.8.131.52.1 - SIL_EmployeeDimension Fails with Error “ORA-00001: unique constraint (DW.W_EMPLOYEE_D_U1) violated” ( Doc ID 1984358.1 ).
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