ORACLE DATA INTEGRATOR (ODI)- SLOWLY CHANGING DIMENSIONS - SCD in ODI

Slowly Changing Dimension in ODI

Download DDL:-

Download Custom KM for SCD2:-

SCD1

Overwrite with the new values:-

Source:- TBLSCD1

Target:- TBLSCD1_TARG

ODI à Use “Oracle Incremental Update” KM

SCD1 Results:-

Source:- 

After ODI execution :-

SCD 2 :-

Create a new record for the update ,

also store Start & End timestamps

& Also mark the latest record

Source:- TBLSCD2

TARGET:- TBLSCD2_TARG

1.       Create table TBLSCD2_TARG

2.       Create a sequence for Surrogate key in db

--------------------------------------------------------

--  DDL for Sequence SEQ_SCD2

--------------------------------------------------------

   CREATE SEQUENCE  "TEST2"."SEQ_SCD2"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 61 CACHE 20 NOORDER  NOCYCLE ;

ODI :-  here in this example SCD2 is based on “Country” column

1.       Create an ODI sequence for surrogate key

2.       In model specify the Surrogate key, Start timestamp, End timestamp, Current record indicator,  

 

 3.       Odi interface

SCD2 Results:-

Source:-

After ODI Execution:-

Now after changes to the “Country” column in the source

After odi execution

SCD3

Additional columns to maintain the previous and current values

Source :- TBLSCD3

Target:-

ODI:-

1.       Models

2.       Customize the KM

a.       Take the “Oracle Incremental Update” KM and call it as “CUSTOM_SCD3_IKM Oracle Incremental Update”

b.      New step with the below code

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

set <%=odiRef.getColList("", "T.[COL_NAME]", ",nt", "", "(UD2)")%> =

<%=odiRef.getColList("", "T.[COL_NAME]", ",nt", "", "(UD1)")%>

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

    in    (

select    <%=odiRef.getColList("","T.[COL_NAME]", ",nttt", "", "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]", ",nt", "", "(UD1)")%>!=

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

c.       Move the step before the “Update existing rows”

3.       Interface add the below

a.      

b.      Make the CURRENT_COUNTRY column as “UD1”

c.       For the “Previous country “ mapp it as “UD2” with INSERT & UPDATE disabled.

 

d.      Choose the new custom KM

SCD3 Results:-

Source:-

Target:-

Example  2

Now changing the value in source

After odi run :-

After odi execution