How to implement SCD type 2 in Abinitio

SCD-type 2 in Abinitio | SCD type 2 in Abinitio | How to implement SCD type 2 in Abinitio





Lets take an example dataset to understand the SCD type 2 in Abinitio.

For example . we have an input dataset which is already loaded in as dimension table already.

vehicle dimension dataset

sk, veh_cd,     curr_driver,hist_driver,start_dt,         end_dt,             curr_flag
1, veh01, dr0,  dr0,            19000101000000,39991231235959,Y
2, veh02, dr1,  dr1,            19000101000000,39991231235959,Y
3, veh03, dr2,  dr2,           19000101000000,39991231235959,Y
4, veh04, dr3,  dr3,           19000101000000,39991231235959,Y
5, veh05, dr5,          dr4,           19000101000000,20210220235959,N
6, veh05, dr5,          dr5,           20210221000000,39991231235959,Y

Sk is Logical Surrogate Key created by Abinitio Process
veh_cd - vehicle code of the driven vehicle
curr_driver - current driver of the vehicle
hist_driver - driver who drove the vehicle in past between start_dt and end_dt
curr_flag - Y/N , based on the current record of the vehicle

curr_flag=Y meaning the vehicle is currently driven by the curr_driver
curr_flag=N, meaning the vehicle was driven by hist_driver between start_dt and end_dt

Now, 

lets say we received the current dataset from the source as below:
we will not have hist_driver and curr_flag in the incoming dataset.
Assumption is that we received the current dataset on 5th June 2021.

sk, veh_cd,curr_driver,start_dt,         end_dt,                     
1, veh01, dr0,    19000101000000,39991231235959      
2,  veh02, dr1,    19000101000000,39991231235959
3, veh03, dr2,    19000101000000,39991231235959
4, veh04, dr3,    19000101000000,39991231235959
5,     veh05,     dr6,                     19000101000000,39991231235959 

note: do not gain any meaning of the start_dt,end_dt and curr_flag columns in the current dataset, as 
this data set was prepared to match the layout of the dimension dataset.


Observation:
we see that we received a updated record for the veh05 with driver as dr6.
so we need to close the record #6 of the dimension dataset and insert an new record  with the dr6 as current driver of the vehicle.

Our Target dataset will look like as below:


sk, veh_cd,curr_driver,hist_driver,start_dt,         end_dt,             curr_flag
1, veh01, dr0, dr0,    19000101000000,39991231235959,Y
2, veh02, dr1, dr1,    19000101000000,39991231235959,Y
3, veh03, dr2, dr2,    19000101000000,39991231235959,Y
4, veh04, dr3, dr3,    19000101000000,39991231235959,Y
5, veh05, dr6,         dr4,    19000101000000,20210220235959,N
6, veh05, dr6,         dr5,    20210221000000,20210604235959,N
7,     veh05,     dr6,          dr6,     20210605000000,39991231235959,Y 

we also update all values of  curr_driver for historical records with the new driver.


the design of the Abinitio Graph will look like as below:


input port0: $AI_DML/vehicle.dml  ==>rec1
input port1: $AI_DML/vehicle_incoming.dml===>rec2

Output port of JOIN component:

PDL :  $[join_types(in0_metadata,in1_metadata)]

Join output will be passed through the REFORMAT component:


out:output-indexes(in)=
begin
let date(YYYYMMDD") HIGH_END_DATE ="39991231235959";
out:1: if(in.rec1.curr_driver!=in.rec2.curr_driver and in.rec1.end_dt!=HIGH_END_DATE) [vector 1];
out:2: if(in.rec1.curr_driver!=in.rec2.curr_driver and in.rec1.end_dt==HIGH_END_DATE) [vector 2,3]
out:: [vector 0];
end;


transform0 (no update, exact match )

out:reformat(in)=
begin
out.*::in.rec1.*;
end;


transform1 (update the historical records with curr_driver value)

out:reformat(in)=
begin
out.*::in.rec1.*;
out.curr_driver::in.rec2.curr_driver;
end;


transform12 (close the current record)

out:reformat(in)=
begin
out.*::in.rec2.*;
out.hist_driver::in.rec2.curr_driver;
out.end_date::now1();
out.curr_flag=N;

end;

transform13 (create the new record)

out:reformat(in)=
begin
out.*::in.rec1.*;
out.hist_driver::in.rec2.curr_driver;
out.start_dt::now1();
out.end_dt::HIGH_END_DATE;
out.curr_flag::Y
end;

Collect the output of transform0 to TRASH
Collect the output of transform1,2 to Update the Dimension TABLE.
Collect the output of transform3 to insert into the Dimension TABLE.


Please also view my follow-up video on SCD type 2 
SCD type 2 dimension video for more metaprogramming detail:








Comments

Post a Comment

Popular posts from this blog

Abinitio Interview Question # 1 - Write Multiple Files in Abinitio

Image
 Question :  I get records of various region in huge volume ,  we want to write the records in region specific files.   How to achieve that? View the explanation in the below video: Answer: -WMF WMF: filename::get_filename(in)= begin let string("") l_filename=string_substring(in.data.region); filename::string_concat($AI_SERIAL,"/",l_filename.dat); end; out::reformat(in)= begin out::in.data; end; OUTPUT DML-: DML record string(int) filename; string(integer(4)) file_contents; end; RMF: filename::get_filename(in)= begin filename::string_concat($AI_SERIAL,"/",in.filename); end; out::reformat(read,filename,in)= begin out.file_contents::read; out.filename::filename; end; Please view my youtube channel  DataPundit
Read more
Image
  Metaprogramming in Abinitio 1. How to create output DML dynamically based in INPUT DML 2. How to create intermediate transform( XFR) dynamically based on INPUT DML      Please See my you tube video for explanation : PART 1 PART 2 For Example, let us say, we have an Input DML as below-: cat input.dml record string(",") name; decimal(",") age; decimal(",") salary; string(",") aadharid; date("yyyy-mm-dd")(",") joiningdate; string(",") mobileno=NULL; string(",") dlno=NULL; string(",") passportnumber=NULL; end; (1.1) prepare a new DML based on input DML i.e. add fields, remove fields, modify type Graph based parameters: ===================================================================== input_dml=$DIR/input.dml  [interpretation = constant] input_dml_info =$[read_type(input_dml)]   [interpretation = pdl] /* Define functions in AB_DML_DEFS */ AB_DML_DEFS= $[ out:: dynamic_dml_gen(input_dml_info)= beg
Read more

Next In Sequence in ABinitio | How next_in_sequence() works in MFS

Image
https://youtu.be/JdDDpjZcv-4 next_in_sequence() working in Abinitio: Definition -      within a single process(component) , this gives successive number.   next_in_sequence()>1 i.e. all records whose occurance is 2 and more in the file.      for example dataset below:    employeeName ram shyam ravi hanuman shekhar seeta geeta shayama vidushi vishakha If we process by condition next_in_sequence()>1 then the output will be [first record will be removed] shyam ravi hanuman shekhar seeta geeta shayama vidushi vishakha 2. next_in_sequence() with reformat  - rely -  FieldA 1a 2a 3a 4a 5a 6a 7a Example 1 (serial processing)--> Reformat   count=2      select:   next_in_sequence()<900      Transofom1:   out:reformat(in)=   begin   out.*::in.*;   out.Seq-No::next_in_sequence();   end;      Transform2:   out:reformat(in)=   begin   out.*::in.*;   out.Seq-No::next_in_sequence();   end;      Output_indexes()=   begin   out::if(next_in_sequence()%2==0) [vector 0,1] else [vector 0,1];   e
Read more