SCD Type2 in ODI:
Hi folks!! Today we will discuss about SCD Type 2 implementation in ODI. So lets get started.
ODI Version: 220.127.116.11
OS: Windows 7
Create source table:
CREATE TABLE EMP_SCD_SRC AS SELECT * FROM EMP
Create Target Table:
CREATE TABLE SCOTT.EMP_SCD_TEST
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
Now reverse these two tables in ODI and create one interface as INT_SCD_TYPE2
Do the normal mapping in interface. Don’t forget to select the key column that is EMPNO 😀 .For last three column put
END_DATE= to_date(‘29990101’, ‘YYYYMMDD’)
CURRENT_FLAG= 0 (0:old record, 1: Latest record)
Now open your target datastore and proceed as given in below screenshots. Select the OLAP type as Slowly Changing Dimension.
Then expand your datastore to get all columns. Open required column one by one and do the changes as given in below screenshots.
Next save everything and select IKM Oracle Slowly Changing Dimension in the flow tab. Similarly CKM Oracle in the control tab.
Save interface and run it.
Now check the loaded data on target.
Hmmm. There are 14 rows inserted. Lets do some modification in the source table to know whether its working properly or not. Here I am modifying SMITH name to SMITHHHHHH. Its means a new row/record should be added when name changes. Similarly update the salary of SCOTT to 9000. Here new record should not be added instead it should be updated as we have selected overwrite on change. After changes commit the datastore.
Run the interface again. Here you can see the same thing reflected as per our expectation. Its your turn now. Do the changes on source side and run the interface to get these data on target.
Thats all for today 🙂 Have a good day.