Skip to main content

Slowly Changing Dimension

Slowly changing dimension

  • This type Of Integration We usually used for Track record of data.
  • It is used to keep track of changes on specific attributes
  • Suppose in Some Attribute Changes slowly According Dimension.  
    i.e. Employee master data table There are some Attribute Location, Department This attributes are changing According to  dimension so we need to track all data So for that purpose we use Slowly Changing dimension Integration Strategy.
SCD Type 1:
  •  Slowly Changing Dimension, the new information simply overwrites the
    original information.
  •  In other words, no history is kept for Previous data.
  • This is Same As Incremental update Strategy which we have already implemented Previously
SCD Type 2:
  •  Type 2 Slowly Changing Dimension, a new record is added to the table to represent the new information. New Record id identified by
    Current flag
  • Therefore, both the original and the new record will be present. The new record gets its own primary key.
  • Two implement SCD type 2 Using ODI We require

— Surrogate Key (Sequnces)
— Natural Key
— Current Flag
— Starting and ending time stamp

Scenario:
Suppose we have we have Employee Department data

Now what if employee changes department and we need Employee’s
Previous department details and current Department details.

solution: Using SCD Type 2 You can achieve this

Steps to Implement SCD type using ODI 12C

Step 1: Create Data server and physical schema
(Previously we already created data server for target)

step 2: Create new data store for target
(Note: Source we will use Department table from Oracle DB.)

o1

step 3: Give data store name

o1

step 4: Define Attribute for target

Two implement SCD type 2 Using ODI We require
— Surrogate Key (Sequences)
— Natural Key
— Current Flag
— Starting and ending time stamp

o1
For surrogate key we need to create sequence

Creating sequence in Oracle SQL Developer(we will use it in ODI)

g1
Creating sequence in ODI

g2
Defining native sequence and assigning oracle DB sequence

g3

step 5: Source: Department
Target: SCD_target

o1
step 6: Creating new mapping

o1
step 7: Give mapping name

o1
step 8: Click on Serial_no Attribute and assign sequence for it.

o1
step 9:Defining sequence

o1
step 10:Defining flag value
(For current value defining flag value 1

o1
step 11: Define integration type as slowly changing dimension

o1
step 12: Serial no is our key

o1
step 13: Select IKM

o1
step 14: Define CKM as oracle insert

o1
step 15: Validating mapping

o1
step 16: Run mapping by clicking on run button

o1
step 17: Seeing session in operator

o1
step 18: Right click on target select data to view result

o1

step 19:

o18

step 20: Now we will change source data
In order to change source data right click on source and select
data

o1

step 21: Changing source data
o1

step 22: Again Run mapping
o1

step 23: seeing session in operator
o1

step 24: You can see current data with flag value 1
and historical data with flag value 0
o23