20th September, 2018
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 ;
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”)
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”)
EE – date = Date from Julian Day ((Julian Day from Date (Current Date ()) -1)) ACF = “N” S5 :- Map required fields to fact table
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.
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))
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
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 .
Write For Us