ODI 12c SCD Type 2 Step by Step Implementation

ODI 12c SCD Type 2 is very easy compare to ODI 11G.

Please find the below steps for SCD Type 2 implementation.

I have created new target table to support SCD behaviour.

CREATE TABLE DEV.SCD
  (
    EMPLOYEE_ID           NUMBER(6,0),
    FIRST_NAME             VARCHAR2(20 BYTE),
    LAST_NAME              VARCHAR2(25 BYTE) NOT NULL ENABLE,
    EMAIL                         VARCHAR2(25 BYTE) NOT NULL ENABLE,
    PHONE_NUMBER     VARCHAR2(20 BYTE),
    HIRE_DATE                DATE NOT NULL ENABLE,
    JOB_ID                       VARCHAR2(10 BYTE) NOT NULL ENABLE,
    SALARY                     NUMBER(8,2),
    COMMISSION_PCT NUMBER(2,2),
    MANAGER_ID           NUMBER(6,0),
    DEPARTMENT_ID     NUMBER(4,0),
    STATUS_FLAG          VARCHAR2(1 BYTE),
    STARTING_DATE      DATE,
    ENDING_DATE          DATE
  )  TABLESPACE SYSTEM ;

Step1:
------
Import IKM Oracle Slowly Changing Dimension  Knowledge module.





























Step2:
--------
Open Target SCD table and Change the SCD Behavior.

I have select SCD Behavior like below options.

Step3:
-------
Creating Mapping for Loading the data from Source table (hr.employees) table to
target table (dev.scd).

These three columns we are not receiving from Source , we need to map at direct target table

I have done mapping STATUS_FLAG=1 ( Default Active-1, Inactive-0),
STARTING_DATE=SYSDATE ,
ENDING_DATE = SYSDATE( But it will take default value from IKM SCD as 01-01-2400.

Selecting LKM SQL to SQL Knowledge module in Physical Tab

Selecting IKM Oracle Slowly Changing Dimension Knowledge Module 

Selecting CKM SQL knowledge module for Data Quality validation at I$table.

Running Interface using Run button in Top Menu

Target table is empty as of now there is not records in target SCD table.

Program executed successfully. We can see the status as Green  .

110 records are inserted as STATUS_FLAG=1 as New Records or active records.

UPDATE hr.employees SET salary=77777 WHERE employee_id=100;

COMMIT;

I have update data in source table and again i am running my mapping.





Program finished successfully.

One record got inserted as salary got changed it will Add row on change behavior

We can see the modified record inserted as new records and old record updated STATUS_FLAG=0
inactive record.  STATUS-FLAG=1 is active for new records.