Oracle Enterprise in Data Stage

Oracle Enterprise

Table name = dim 1

Write method = upset Upset order  = update then insert Compile and RUN In SQL plus, Select * from dim 1;   Skip      

S no       S            name

1             111          shilpa

2             222        Renuka

3             333       Archama  

To insert new values delete SRC;

• Insert in to SRC Values (222, ‘Anil’); Insert in to SRC Values (444, ‘Sravani’);
• Commit;
• Compile and Run job (a)
• Compile and Run Job (b)
• Select * from dim 1;
• Skip S no S name

1             111               shilpa

2              222              Renuka

3               333             Archama

4                 444           Sravani    

SCD – type 2 Table 1 Create table SRC  (S no  number  (3), S name  varchar2  (25)  ); Table 2  Create Table dim 2 (skid number  (2), S no number  (3), S name varchar2 (25), Es – date data, EE – data date, ACF varchar2 (2)); 5 types for SCD – Type 2 S 1 :- Select output link as fact link S2 :- Map the look up key S No S3 :-Source type  = Flat file =Source Name  = (Empty file name) S4:-   Identify  the purpose of each field Skid = Surrogate key S no= business Key S name = type 2 Es –date = Effective date EE – date  = Expiration date ACF  = Current Indicator  (“Y”)

Derivation

Skid = Surrogate key S no=  S no S name = S name Es –date = Current date EE – date  = “9999 – 12-31”  (default DB/2  format) ACF  = Current Indicator  (“Y”)

Expire

EE – date   = Date from Julian Day ((Julian Day from Date  (Current Date  ())  -1)) ACF = “N” S5 :- Map required fields to fact table

Load properties

S1 :- Set Data set properties S2 :-Set oracle properties Table Name  = dim 2 Write method = upset (update and insert) Upset order = update then Insert    

Oracle enterprise 0 Load dim 2 table à column à change date to Times stamp  

Oracle enterprise 1  Load SRC table  

S no              S name 111               shilpa 222                 Renuka 333                 Archama

Learn DataStage by Tekslate - Fastest growing sector in the industry.
Explore Online DataStage Training and course is aligned with industry needs & developed by industry veterans.
Tekslate will turn you into DataStage Expert.

Transformer

Drag and drop the fields Times stamp to date   (before. Es. date)   = Es – date Times stamp to date   (before. Es. date)   = Ee – date In before   change Es – date, Ee – date to date   SCD 1)  Select fact link 2)  Map the look up Key SNO 3) Source type = flat file Source name = E:/shilpa/SCD 2. Txt; 4)Select  all Right click  clear derivation   Derivation               key               purpose                     expire Double click  Surrogate key ()        Skid               Surrogate key   S no                business key S name          type 2   Current date ()         Es – date      effective date “9999 – 12 - 31”       Ee –date       Expiration date “Y”                               ACF               Current indicator   Ee – date = Date from Julian Day   ((Julian Day from Date  (Current Date  ())  -1))  

1. Map required fields to fact table

Compile and Run Output Skid            S no                   S name                  Es – date       Ee- date        ACF 1                     111                    Shilpa                                                                         Y 2                    222                   Reruka                                                                      Y 3                     333                 Archana                                                                     Y   (b)  Data set ---------------à oracle enterprise Copy the path of DFM Data set  (a)  - Columns -Save --(SCD) 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