Tuesday, November 24, 2009

IKM Oracle Data Integrator SCD Type 2 Bug

Oracle Data Integrator 10.1.3.5
IKM Slowly Changing Dimension Type 2

Problem Statement:

Implement a SCD Type 2 for a table.
Three columns form the Natural Key for the table and all other columns need to be tracked for change.
If the non Natural Key columns were marked as “Overwrite On Change” in the “Slowly Changing Dimensions Behaviour”, the IKM worked fine but if the non Natural Key columns were marked as “Add Row on Change”, the IKM didn’t do anything.

This is a reported bug no. 8312924 and a patch is available. Metalink note 788747.1 describes the problem and workaround if you cannot deploy the patch.

Solution:

Changed the IKM Step 172 and replaced CX_COL_NAME with EXPRESSION courtesy of forums:

http://forums.oracle.com/forums/thread.jspa?messageID=3816462
and
http://forums.oracle.com/forums/thread.jspa?messageID=3631382&#3631382