The below steps shows how SCD 2 is configured in ODI 11g
Pre requisites :
ODI installed and configured with Topology for source and target is created.
If not please check the steps to create the SOURCE and TARGET here.
Create the source and target model in ODI designer tab and map the model to respective Logical schema.
Create the MODELS [SOURCE and TARGET]
Reverse Engineer the SOURCE and TARGET tables from the designer tab.
Create the SOURCE table in the source database
Create the target table in the TARGET schema, it should contain START_DATE, END_DATE & ACTIVE FLAG column for SCD 2
Create the TARGET table :
CREATE TABLE "TARGET"."CUSTOMERS_SCD2"
( "CUST_KEY" NUMBER,
"NAME" VARCHAR2(255 CHAR),
"TYPE_KEY" VARCHAR2(255 CHAR),
"FST_CONTACT_DT" DATE,
"SEGMENT_KEY" NUMBER,
"INCOME_LVL" VARCHAR2(255 CHAR),
"STATUS_KEY" VARCHAR2(255 CHAR),
"LST_ORDER_DT" DATE,
"ADDRESS_KEY" NUMBER,
"MARITAL_ST" VARCHAR2(20 BYTE),
"PREV_MARITAL_ST" VARCHAR2(20 BYTE),
"PREV_MARITAL_ST_DT" DATE,
"BIRTH_DT" DATE,
"CRDT_RATE" NUMBER(*,0),
"START_DATE" DATE,
"END_DATE" DATE,
"ACTIVE_FLAG" VARCHAR2(1 BYTE)
)
Create an INTERFACE from the designer tab and map the SOURCE to target columns and map the SCD2 TYPE columns as below
START_DATE - SYSDATE
END_DATE - SYSDATE
ACTIVE_FLAG - '1'
Configure SCD type 2 For the target table
Navigate to the target model from the designer tab and open the target table and change the OLAP type as 'Slowly Changing Dimension' as shown below
Open the column in the target table and in the description tab, change the 'Slowly changing dimension behavior' field as shown below
The Key column should be mapped as Natural key. (if the surrogate key is not available in target)
For the column for which the SCD TYPE 2 is to be configured, change the behavior to 'Add Row on Change'
For the other columns change the behavior to 'Overwrite on Change'
For the ACTIVE FLAG column, change the behavior to 'Current Record Flag'
For END_DATE change the behavior as ENDING TIMESTAMP as shown below
For START_DATE column change the behavior as STARTING TIMESTAMP
Open the interface and in the flow tab change the IKM to IKM Oracle Slowly changing Dimension. Also change the flow control to false.
Verify the data in the source table and in this example we will try to update the NAME of one record which is shown below
Verify that the target table is empty
Execute the interface from the designer tab
The target table is now populated with the source data
Now in the source data set, change the NAME of the first record to a different name as shown below and execute the interface again.
Verify the target data and now the new record with changed name is available in the target table and the ACTIVE FLAG is set to 1 for this row and the old record is changed to 0 .
The start and end date in the target table is also updated as shown below.
Now the SCD TYPE 2 configuration is complete
Thanks,
Sandeep
Pre requisites :
ODI installed and configured with Topology for source and target is created.
If not please check the steps to create the SOURCE and TARGET here.
Create the source and target model in ODI designer tab and map the model to respective Logical schema.
Create the MODELS [SOURCE and TARGET]
Reverse Engineer the SOURCE and TARGET tables from the designer tab.
Create the SOURCE table in the source database
Create the target table in the TARGET schema, it should contain START_DATE, END_DATE & ACTIVE FLAG column for SCD 2
Create the TARGET table :
CREATE TABLE "TARGET"."CUSTOMERS_SCD2"
( "CUST_KEY" NUMBER,
"NAME" VARCHAR2(255 CHAR),
"TYPE_KEY" VARCHAR2(255 CHAR),
"FST_CONTACT_DT" DATE,
"SEGMENT_KEY" NUMBER,
"INCOME_LVL" VARCHAR2(255 CHAR),
"STATUS_KEY" VARCHAR2(255 CHAR),
"LST_ORDER_DT" DATE,
"ADDRESS_KEY" NUMBER,
"MARITAL_ST" VARCHAR2(20 BYTE),
"PREV_MARITAL_ST" VARCHAR2(20 BYTE),
"PREV_MARITAL_ST_DT" DATE,
"BIRTH_DT" DATE,
"CRDT_RATE" NUMBER(*,0),
"START_DATE" DATE,
"END_DATE" DATE,
"ACTIVE_FLAG" VARCHAR2(1 BYTE)
)
Create an INTERFACE from the designer tab and map the SOURCE to target columns and map the SCD2 TYPE columns as below
START_DATE - SYSDATE
END_DATE - SYSDATE
ACTIVE_FLAG - '1'
Configure SCD type 2 For the target table
Navigate to the target model from the designer tab and open the target table and change the OLAP type as 'Slowly Changing Dimension' as shown below
Open the column in the target table and in the description tab, change the 'Slowly changing dimension behavior' field as shown below
The Key column should be mapped as Natural key. (if the surrogate key is not available in target)
For the column for which the SCD TYPE 2 is to be configured, change the behavior to 'Add Row on Change'
For the other columns change the behavior to 'Overwrite on Change'
For the ACTIVE FLAG column, change the behavior to 'Current Record Flag'
For END_DATE change the behavior as ENDING TIMESTAMP as shown below
For START_DATE column change the behavior as STARTING TIMESTAMP
Open the interface and in the flow tab change the IKM to IKM Oracle Slowly changing Dimension. Also change the flow control to false.
Verify the data in the source table and in this example we will try to update the NAME of one record which is shown below
Verify that the target table is empty
Execute the interface from the designer tab
The target table is now populated with the source data
Now in the source data set, change the NAME of the first record to a different name as shown below and execute the interface again.
Verify the target data and now the new record with changed name is available in the target table and the ACTIVE FLAG is set to 1 for this row and the old record is changed to 0 .
The start and end date in the target table is also updated as shown below.
Now the SCD TYPE 2 configuration is complete
Thanks,
Sandeep
0 comments:
Post a Comment