SCD Type3 in ODI:
Today we will discuss SCD Type3 in ODI. Before moving to ODI we need to understand what is SCD Type3.
In Type 3 Slowly Changing Dimension, there will be two columns to indicate the particular attribute of interest, one indicating the original value, and one indicating the current value. There will also be a column that indicates when the current value becomes active.
In our example, recall we originally have the following table:
To accommodate Type 3 Slowly Changing Dimension, we will now have the following columns:
- Effective Date
After Scott salary changed to 5000, the original information gets updated, and we have the following table (assuming the effective date of change is January 1, 2010):
– This does not increase the size of the table, since new information is updated.
– This allows us to keep some part of history.
– Type 3 will not be able to keep all history where an attribute is changed more than once. For example, if salary changed 4 times in 4months, the last 3 salary information will be lost.
Type 3 is rarely used in actual practice.
When to use Type 3:
Type III slowly changing dimension should only be used when it is necessary for the data warehouse to track historical changes, and when such changes will only occur for a finite number of time.
Okay. I hope you are clear now. Its time to implement this in ODI. Create the target table as given in below query.
CREATE TABLE EMP_SCD_T3
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
PREVSAL NUMBER(7,2),–Column going to store historical data
DEPTNO NUMBER (2)
Now reverse EMP and EMP_SCD_T3 in your model.Create an interface INT_SCD_TYPE3 and drag source datastore EMP and target datastore EMP_SCD_TYPE3 to their respective place in the interface.Do the normal mapping and select CKM oracle and IKM sql incremental Update.
Now certain changes you have to do as per below screenshot.Open your IKM Sql Incremental Update KM and go to step 140.Duplicate it to create a new step.
In the above step (Update previous Data of T3 Column) put below codes.
( Please download the KM from here. If you are copying the code then replace exiting single,double quote in your keyboard.)
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”)%> )
select <%=odiRef.getColList(“”,”T.[COL_NAME]”, “,\n\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)”)%>
Now click on command on source and delete whatever codes you have there. These codes have no use now.
After modification save it.Then select the PREVSAL column on targetside. In the property inspector deselect INSERT & UPDATE checkbox. Select UD2 user defined flag.
Similarly select CURSAL column on target side and mark the checkbox for UD1.Now save everything.
Now run the interface and check loaded data on target table. You can see the PREVSAL column is blank because this is the 1st run.
This is the code generated by our edited update step in the IKM.
Now change the salary of SMITH to 9000. After the commit run the interface.
That’s all friends. Its pretty simple.
Have a good day 🙂