• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

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

Summary
Review Date
Reviewed Item
SCD(Slow changing Dimension) in Data Stage
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on SCD(Slow changing Dimension) in Data Stage"

Leave a Message

Your email address will not be published. Required fields are marked *

Site Disclaimer, Copyright © 2016 - All Rights Reserved.