Implementation of SCD Type-3 in ODI 11g

                    

In SCD type-3, it consists of  two columns to indicate the particular attribute i.e, one indicates the previous value, and the another one indicates the current value.

First in database, duplicate emp table as considered as our target table and we added three  new columns i.e prev sal , curr sal and eff_date as shown below:

 

Create new data server for Source in Oracle Technology

Edit JDBC connections as shown:

Click on test connection

Create new physical schema and logical schema

In the same way create data server, physical and logical schemas for target table in oracle technology

Click on test connection

Now click on designer navigator and create source model

Next reverse engineer the source table

Create a model for target table and reverse engineer

After reverse engineering the source & target tables columns are as follows:

Create new interface and drag & drop the source & target tables on to the interface

Map sal with curr_sal & pre_sal. Edit eff_date to sysdate as shown

And save the interface.

Then import IKM oracle incremental update knowledge module

Now make a copy of  the knowledge module

Rename as scd type3

Go to details & duplicate the 'update existing rows' command & rename it as 'update prevsal with currsal'

Replace codes in command on target tab with the codes given below

Save the edited KM

Now open the interface & click on flow tab & select IKM 'scd type3'

Select flow control as false

Now click on quick edit tab and deselect the insert and update checkboxes and select UD2 user defined flag for pre_sal. Also enable insert, update and UD1 checkboxes for curr_sal as shown:

Then save & run the interface.

Check the loaded date on target table. You can see the PRE_SAL column is blank as this is the 1st run.

Now edit any of the salaries on source side & save. Then run the interface.

Then check the loaded data.

Here you can see the PRE_SAL column updated with 1600 as this is the old salary for ALLEN. Similarly we can change the salary on source side and it will get reflected on target side.

Thus successfully executed scd type3.