SCD - Type 1SCD - Type 3 and 4

SCD Type 2

Slowly changing dimension Type 2 is a model where the whole history is stored in the database. An additional dimension record is created and the segmenting between the old record values and the new (current) value is easy to extract and the history is clear.
The fields 'effective date' and 'current indicator' are very often used in that dimension and the fact table usually stores dimension key and version number.

SCD 2 implementation in Datastage

The job described and depicted below shows how to implement SCD Type 2 in Datastage. It is one of many possible designs which can implement this dimension.
For this example, we will use a table with customers data (it's name is D_CUSTOMER_SCD2) which has the following structure and data:

D_CUSTOMER dimension table before loading
CUST_ID CUST_
NAME		 CUST_
GROUP_ID		 CUST_
TYPE_ID		 CUST_
COUNTRY_ID		 REC_
VERSION		 REC_
EFFDT		 REC_
CURRENT_IND
DRBOUA7 Dream Basket EL S PL 1 2006-10-01 Y
ETIMAA5 ETL tools info BI C FI 1 2006-09-29 Y
FAMMFA0 Fajatso FD S CD 1 2006-09-27 Y
FICILA0 First Pactonic FD C IT 1 2006-09-25 Y
FRDXXA2 Frasir EL C SK 1 2006-09-23 Y
GAMOPA9 Ganpa LTD. FD C US 1 2006-09-21 Y
GGMOPA9 GG electronics EL S RU 1 2006-09-19 Y
GLMFIA6 Glasithklini FD S PL 1 2006-09-17 Y
GLMPEA9 Globiteleco TC S FI 1 2006-09-15 Y
GONDWA5 Goli Airlines BN S GB 1 2006-09-13 Y

Datastage SCD2 job design
Datastage SCD2 job design

The most important facts and stages of the CUST_SCD2 job processing:
  • The dimension table with customers is refreshed daily and one of the data sources is a text file. For the purpose of this example the CUST_ID=ETIMAA5 differs from the one stored in the database and it is the only record with changed data. It has the following structure and data:
    SCD 2 - Customers file extract:
    SCD 2 - Customers file extract

  • There is a hashed file (Hash_NewCust) which handles a lookup of the new data coming from the text file.
  • A T001_Lookups transformer does a lookup into a hashed file and maps new and old values to separate columns.
    SCD 2 lookup transformer
    SCD 2 lookup transformer
  • A T002_Check_Discrepacies_exist transformer compares old and new values of records and passes through only records that differ.
    SCD 2 check discrepancies transformer
    SCD 2 check discrepancies transformer
  • A T003 transformer handles the UPDATE and INSERT actions of a record. The old record is updated with current indictator flag set to no and the new record is inserted with current indictator flag set to yes, increased record version by 1 and the current date.
    SCD 2 insert-update record transformer
    SCD 2 insert-update record transformer
  • ODBC Update stage (O_DW_Customers_SCD2_Upd) - update action 'Update existing rows only' and the selected key columns are CUST_ID and REC_VERSION so they will appear in the constructed where part of an SQL statement.
  • ODBC Insert stage (O_DW_Customers_SCD2_Ins) - insert action 'insert rows without clearing' and the key column is CUST_ID.
    D_CUSTOMER dimension table after Datawarehouse refresh
    CUST_ID CUST_
    NAME    		 CUST_
    GROUP_ID    		 CUST_
    TYPE_ID    		 CUST_
    COUNTRY_ID    		 REC_
    VERSION    		 REC_
    EFFDT    		 REC_
    CURRENT_IND
    DRBOUA7 Dream Basket EL S PL 1 2006-10-01 Y
    ETIMAA5 ETL tools info BI C FI 1 2006-09-29 N
    FAMMFA0 Fajatso FD S CD 1 2006-09-27 Y
    FICILA0 First Pactonic FD C IT 1 2006-09-25 Y
    FRDXXA2 Frasir EL C SK 1 2006-09-23 Y
    GAMOPA9 Ganpa LTD. FD C US 1 2006-09-21 Y
    GGMOPA9 GG electronics EL S RU 1 2006-09-19 Y
    GLMFIA6 Glasithklini FD S PL 1 2006-09-17 Y
    GLMPEA9 Globiteleco TC S FI 1 2006-09-15 Y
    GONDWA5 Goli Airlines BN S GB 1 2006-09-13 Y
    ETIMAA5 ETL-Tools.info BI C ES 2 2006-12-02 Y



    Back to the Datastage tutorial