SCD Type 3

Hi Gurus, 

      Here we are trying to implement the “Slowly Changing Dimension Type 3”  functionality which is not available in ODI 11g.   

     To implement this functionality we have to customize the IKM Oracle Incremental Update which is detailed here.  

      In the following steps we try to capture the updates/inserts in the SCOTT.EMP table SAL Column and store it in TRG_EMP4 table.

1.     Create a target table for this exercise from SCOTT Model  as TRG_EMP4 (or as done here from another table TRG_EMP3 by ‘Duplicate Selection’).

 2.     In Columns tab create another column PREV_SAL and move it near SAL Column and rename the SAL as CURR_SAL.

  3.     Create an interface INF_SCOTT_EMP4 (or anything you like) and in mapping drop the EMP and TRG_EMP4 tables. 

4.     a. Make EMPNO as the Update KEY and map the SAL column to CURR_SAL and PREV_SAL in Target (TRG_EMP4) which will define the EMPNO as UK (unique Key).

b. Select the PREV_SAL and CURR_SAL as UD1 and UD2 respectively and unselect the insert and update boxes for PREV_SAL column.

 
 5.       Import the IKM Oracle Increment Update and set it to the Target Mapping.

6.     Set the Flow control to FALSE and CREATE_Targ_Table = true ,TRUNCATE=false.
7.     To catch the changes in target table change one of the curr_sal  value.

8.     Write a query in SQL editor to track the changes in EMP.SAL Column  and execute it to verify that it updates the TRG_EMP4 with those changes.

  9.       Run the interface to verify that it updates the column. The columns are not updated in the TRG_EMP table! (we have not yet implemented it).

  10.       To catch this change we have to include this logic in IKM.

  11.       Set the UD1 and UD2 as True for PREV_SAL and CURR_SAL and  remove the checks for the insert and update for PREV_SAL.

 12.    Create another IKM by duplicating the IKM Oracle Incremental Update  as IKM Oracle Incremental Update SCD3.

13.   Create another step before the Update existing rows step indicated above. Observe how the ODIref variables used in this step.

  14.   Add the SQL developed before by adding the ODI variables by analyzing the code in the above update step.   

a.     The Target table is changed with “TARG_NAME” and “A” for automatic mapping of the schema.

 b.     Change the PREV_SAL column with UD2 as 

 c.     Change the target CURR_SAL column as         

 d.     T.EMPNO will be replaced with UK as below AND for the SCOTT.EMP retrieve the Integration parameter value INT_NAME. (we need to get it from the interface I$ table).

UPDATE  <%=odiRef.getTable("L", "TARG_NAME", "A")%> T

   SET <%=odiRef.getColList("", "T.[COL_NAME]", ",\n\t", "", "(UD2)")%> =<%=odiRef.getColList("", "T.[COL_NAME]", ",\n\t", "", "(UD1)")%>

WHERE    (<%=odiRef.getColList("","T.[COL_NAME]", ", ", "", "UK")%>)

   IN    (

SELECT  <%=odiRef.getColList("","T.[COL_NAME]", ",\n\t\t\t", "", "UK")%>

FROM  <%=odiRef.getTable("L", "TARG_NAME", "A")%> T ,

<%=odiRef.getTable("L", "INT_NAME", "A")%> S

WHERE  <%=odiRef.getColList("","T.[COL_NAME]", ", ", "", "UK")%> =<%=odiRef.getColList("","S.[COL_NAME]", ", ", "", "UK")%>

AND  <%=odiRef.getColList("", "T.[COL_NAME]", ",\n\t", "", "(UD1)")%>!=

                       <%=odiRef.getColList("", "S.[COL_NAME]", ",\n\t", "", "(UD1)")%>

                        )

15.   Set the Flow_Control =false, TRUNCATE=false, CREATE_TARG_TABLE= true, DELETE_TEMPORARY_OBJECTS=false.

16 .   Make some changes in EMP table and commit.
17.      Execute the interface and verify that the changes are updated in SCD.

18.   Check the I$ table for update flag.

 19.   Verify the no. of rows updated or inserted by the Interface and the query generated.

  20.  The update in SAL column in EMP is stored in the TRG_EMP4.Prev_SAL in the same row with the new value (Slowly Changing Dimension Type 3 behavior).

Thanks

Mohan Ramaswamy

Sr Analyst (Contractor)

Snap-On Business Solutions

Richfield, OHIO, US 44286