SCD(Slow changing Dimension) in Data Stage

  • Share this blog:

SCD(Slow changing Dimension)

EX:- Suppose we have an customer Table, we have some fields which are frequently, ofliny,  slowly, Rarely, Rapidly changed   Screenshot_70  

Different types of loadings


  1. Initial
  2.  incremental


  • Initial data (source) :-

C ID           CN      Add 11               A           HYD                      stored in Achieve Data base or OLTP or Source 22               B           SEC 33               C          DEL   The information is should from 2000 to 29th dec 2010 This data is stored in Target  7 Ware House  7 Before Data

  • Source (After data) :-

CID          CN                    Add   7value column 11              A                      HYD  7Copy 22              B                   GNT   7Edit 33               E                   VZD   7insert  

  • Refresh the target data with source data based on type 1, type 2, type 3
  • Differences :-

Compare Before Data with After Data Extract                                  Extract Target                                    Source Data                                        Data  

  • If there are any changes in key column7Insert
  • If there are any changes in value column7 edit

  Output :- Screenshot_71     Table 1 :- EX:  Before Data(Target) CID           CN               Add 11             A                  HYD 22             B                 GNT 33             C                 DEL 32             D                 VZD   After Data: (Source)   CID       CN             Add 11           A          ASM 7 Edit 22          B            GNT 7 copy 44          G            PUNE 7 insert 32          E            Cal 7edit  

  • Type 1 will not maintain historical data, maintains only current data .
Get through the interview bar with our selected interview questions for DataStage enthusiasts

Output :- CID           CN              Add 11               A               ASM 22              B              GNT 33              C               DEL 44             G              PUNE 32              E               CAL  

  • Before Data will not Maintain history


Type 1 implementation will always  happen in ODS

Screenshot_72     Dimensional  Tables7 CUST id,   C NAME, C ADD , Gender Fact Tables 7 C id, BAL, AREA, Trane Type , Data 7 Maintained History  

Type 2  :-  (Maintain History)

  After Data :- (Source)                    Before  Data :- (Target)   CID   CN    ADD                                    CID   CN      ADD 11    A         HYD                                     11    A         HYD 22   B            GNT                                   22     B         SEC 44    E            PUN                                   33    C           DEL   Output :-   CID   CN    ADD 11    A         HYD 22     B        GNT 33      C       DEL ----------------------------------- 22           B               GNT 44             E                PUN  

  • Here the key column is repeating, so we insert an new constraint
  • Surrogate key

For indepth understanding of DataStage click on

About Author
Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.