How to handle BIAPPS ETL failures due to duplicate data issues in Oracle DW
Author: Amit Kothari
Sometimes there are duplicate data rows in the source tables, due to this while running a ODI Load Plan (LP) in BI Applications 220.127.116.11.1 the ODI sessions may error out while creating the unique indexes. It is always recommended that the source data issues be fixed first but that may not be feasible sometimes or you may just want to fix the data in the warehouse and continue with the LP run. This blog shows you one way to do that – first we find the table and the indexed columns for the failed index and then we substitute it in the delete sql to delete the duplicate rows.
ODI-1217: Session EXEC_TABLE_MAINT_PROC (12383500) fails with return code 20000.
ODI-1226: Step TABLE_MAINT_PROC fails after 1 attempt(s).
ODI-1232: Procedure TABLE_MAINT_PROC execution fails.
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_D_U1=> ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
ORA-06512: at line 152
From sql developer run this sql in your ODI repository schema based on the failed indexed name which we got from the error above, it will give you the indexed
columns for the failed index, for eg -
FROM SNP_TABLE st, SNP_KEY sk, snp_key_col skc, snp_col sc
WHERE sk.key_name ='W_EMPLOYEE_D_U1'
AND sk.I_table = st.I_table AND skc.I_key = sk.I_key AND
sc.I_col= skc.i_col AND st.I_table = sc.i_table
When we run the above sql we get the table name as W_EMPLOYEE_D and columns as – EFFECTIVE_FROM_DT , DATASOURCE_NUM_ID,INTEGRATION_ID
These are the columns which has the duplicate rows resulting in the failure of the unique index creation. Now we can proceed to delete the duplicate rows via a delete sql, make sure you backup the table data before issuing the delete statement.
From sql developer run this sql in your Oracle target schema and then simply restart the ODI load plan - a sample delete sql based on the above index is as follows. Just substitute the columns and the table name for your use case.
DELETE FROM W_EMPLOYEE_D A
WHERE ROWID > (SELECT MIN(ROWID) FROM W_EMPLOYEE_D B WHERE A.INTEGRATION_ID
= B.INTEGRATION_ID AND A.DATASOURCE_NUM_ID = B.DATASOURCE_NUM_ID AND
A.EFFECTIVE_FROM_DT = B.EFFECTIVE_FROM_DT) ;