SCD(Slow changing Dimension) in Data Stage

SCD(Slow changing Dimension)

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

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 29^th dec 2010 This data is stored in Target   Ware House   Before Data

• Source (After data) :-

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

• 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 columnInsert
• If there are any changes in value column edit

  Output :-     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  Edit 22          B            GNT  copy 44          G            PUNE  insert 32          E            Cal edit  

• 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

    Dimensional  Tables CUST id,   C NAME, C ADD , Gender Fact Tables  C id, BAL, AREA, Trane Type , Data  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

• Introduction to DataStage
• Architecture of Data Stage
• Oracle Enterprise in Data Stage
• SCD(Slow changing Dimension) in Data Stage
• DataStage Tutorials