Implement SCD type3 in ODI 11g\12c

Ø  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.

Ø  Type 3 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, that means only recent data stored in table.

			Create project   Go to Designer Navigator. Select new project.       Import Knowledge Modules
			For this SCD TYPE3, The Knowledge Modules (KMs) that are required for this are IKM SQL Incremental Update & CKM Oracle To import the KMs, click the Project tab in the left panel and expand the Knowledge Modules folder. Right-click on KM and select Import Knowledge Modules.  On the screen that follows, in the File Name, navigate to xml reference directory as shown in the following screenshot. Click Open. The files to import should appear in the Import Knowledge Modules window. Press and hold the CTRL key to select  IKM SQL Incremental Update & CKM Oracle. Click OK.
			On Import Report window, click Close. Expand the IKM & CKM folder
			Add the command to IKM for impenting scdtype3: Change IKM SQL Incremental Update to IKM SQL Incremental Update SCD TYPE3 Add command to historical update rows is as follows         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)")%>         )       Create Data model for source & target tables:    Source table : create table scdtype3_source as select * from employee         desc scdtype3_source   TABLE scdtype3_source  Name                                      Null?    Type                         ----------------------------------------- -------- ----------------------------  EMPNO                                              NUMBER(4,)                   ENAME                                              VARCHAR2(10)                 JOB                                                VARCHAR2(9)                  MGR                                                NUMBER(4,)                   HIREDATE                                           DATE                         SAL                                                NUMBER(7,2)                  COMM                                               NUMBER(7,2)                        DEPTNO                                             NUMBER(2,) Target Table:     CREATE TABLE scdtype3_target( EMPNO NUMBER(4) PRIMARY KEY, ENAME VARCHAR2(10),JOB VARCHAR2(9),MGR NUMBER(4),HIREDATE DATE,CURSAL NUMBER(7,2),PREVSAL NUMBER(7,2),COMM NUMBER(7,2),DEPTNO NUMBER (2))         desc scdtype3_target     TABLE scdtype3_target  Name                                      Null?    Type                         ----------------------------------------- -------- ----------------------------  EMPNO                                     NOT NULL NUMBER(4,)                   ENAME                                              VARCHAR2(10)                 JOB                                                VARCHAR2(9)                  MGR                                                NUMBER(4,)                   HIREDATE                                           DATE                         CURSAL                                             NUMBER(7,2)                  PREVSAL                                            NUMBER(7,2)                  COMM                                               NUMBER(7,2)                  DEPTNO                                             NUMBER(2,)   Required output: For example,  if sal changes 800 to 40000 then cursal, prevsal as EMPNO ENAME      JOB MGR HIREDATE CURSAL PREVSAL COMM DEPTNO EFFECTIVEDATE 7369 SMITH CLERK 7902 17-DEC-80 40000 800   20 31-AUG-2015 7499 ALLEN SALESMAN 7698 20-FEB-81 1600   300 30 31-AUG-2015         Create Interface for SCD TYPE3: Step 1) Open the Designer, expand the project. Right click on interfaces and click on new interface. Give the name as SCD TYPE3 Step 2) Drag and drop source(SCDTYPE3_SOURCE)  from the SCD model on to the source pane & Drag and Drop target(SCDTYPE3_TARGET) table from  SCD model on to the target pane. Click ok to perform Automatic mapping. For last three columns put     Step 3) Click on FLOW tab , on Target to see the IKM target properties. Select IKM SQL Incremental Update in the flow tab. Similarly CKM Oracle in the control tab. Set the "flow_control" option to true . Click on save. Click on execute.           Step 4) Go to operator log. Check the log for this session. Open it and check the number of inserts & updates. Here there is no previous data.     Step 5) Go to Designer tab, click the source & target tables.     Modify the Source table: There are 14 rows inserted. Let’s do some modification in the source table to know whether its working properly or not. Here I am updating the salary of SMITH to 30000. Here new record should not be added instead it should be updated as we have selected overwrite on change. After changes commit the data store.   update scdtype3_source set sal=30000 where empno=7788       Check the source :   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.         Output: