Slowly changing Dimension in ODI

Process

1.     Create source and target data stores

2.     On Target datastore change OLAP Type to Slowly Changing dimension

3.     Expand target datastore and open all columns à under description select appropriate you need to perform (for e.g. add row on change, Overwrite on change ….)

[Note: Natural Key is must which column is unique as per source]

4.     Import IKM Oracle slowly changing dimension Knowledge module

5.     Create Interface in drag and drop source datastore from model into source side of editor and target datastore from target side of editor

For Example:

1.     Create Database tables as follows:

Source: CREATE TABLE PRODUCT( PID NUMBER(3,0), NAME VARCHAR2(10),             COST NUMBER(10,2))

Target: CREATE TABLE PRODUCT_DIM( PID NUMBER(3,0), NAME VARCHAR2(10), COST NUMBER(10,2), FLAG VARCHAR2(2), START_TIMESTAMP TIMESTAMP,             END_TIMESTAMP TIMESTAMP)

2.     Reverse engineer those two tables in ODI

a.     Create model with correspondent logical schema selected

b.     Open Model (Double click it)

c.      Go to Reverse Engineer à Mast attribute “PRO%”

d.     Go to Selective Reverse Engineering à Enable selective Reverse Engineering , New Datastores and Objects to reverse Engineering

e.     Select the objects and click on “Reverse Engineer” at top

3.     Open Target datastore (Product_DIM) and select OLAP TYPE as “Slowly change dimension”

4.     Expand Target datastore à Expand columns à Double “PID” à Description Tab à SCD Behavior to “Natural Key”

“Name” à Description Tab à SCD Behavior to “Overwrite on change”

“Cost” à Description Tab à SCD Behavior to “Add row on change”

“Flag” à Description Tab à SCD Behavior to “Current Record Flag”

“Start_Timestamp” à Description Tab à SCD Behavior to “Starting Timestamp”

“End_timestamp” à Description Tab à SCD Behavior to “Ending Timestamp”

5.     Create project

6.     Import Knowledge à IKM Oracle Slowly Changing Dimension

7.     Create Interface and select appropriate Logical Schema

8.     Drag and drop Source datastore into source side and Target datastore into target side

9.     Auto map à Yes and for Flag = 0 , Start_Timestamp = systimestamp and end_timestamp = systimestamp

10.                        Go to Flow tab and select knowledge module as “IKM Oracle Slowly changing dimension” and flow control as “false”