Wednesday, November 29, 2017

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.