ODI- Slowly Changing Dimension [SCD TYPE 2] | The Big Data - Business Intelligence by Sandeep Venu

Enter key word

ODI- Slowly Changing Dimension [SCD TYPE 2]

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


SHARE

About Sandeep Venu

    Blogger Comment
    Facebook Comment

0 comments:

Top Links

Upgrading BI Publisher

Add Google Search

OBIEE- LDAP

Change Admin Password

Best Practices

ORACLE 11GR2

Host Files

Customization

OBIEE-SIEBEL

Bug Fixes

Setting Default

OBIEE Installation failed

OBIEE Installation

Oracle Databse

Oracle In Linux

Loopback Adapter

Weblogic Failed

Yum Commands

Weblogic Admin

Linux Static IP

OBIEE LDAP

Admin Paswword

Scaling

Weblogic Starting Error

Localizing BI

Physical Schemas

Multimedia Dashboard

Video in OBIEE

Variables in OBIEE

Ago 30 Days

Email Link

ODI Installation

AGO Function

Sort Pivote table

OBIEE Patch

Reset Sys password

Date Calculation

Add Row in Report

Increase Row limit

OBIEE with Essbase

Reset Weblogic

BIP Login Error

MDS and BI Platform

ORA 28001 Error

ORA 12560 TNS Protocal Error

ORA 12154 TNS Error

OBIEE ON AIX

Pre Requsite

AIX Commands

Commands