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
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))
- 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)