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
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:-
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
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 execution
