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

 

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