In this SCD Type 2 historical data is maintained along with current data. SCD Type 2 concept can be implemented with 3 different methods. Different methods are Version, Flag and Effective Date.
First we will focus on Version method.
Create a mapping and name it.
- Drag Emp source into the mapping area.
- Create a Lookup transformation on target table.
- On Primary key columns you should define your condition, because its the matching column between source and target.
- Create an Expression, drag the ports from lookup transformation and name as prev record. This tells us that records are old ones which are used to compare with new ones.
- Drag the comparision keys from source qualifier to Expression
- Create two ports as Newflag and Changedflag and give syntax as Isnull (Cust_key) and Not Isnull(Cust_key) and (Prev_Empn != Empno OR Prev_Ename != Ename OR Prev_Sal != Sal)
- First syntax will check for nulls. If there are null, lookup will insert data into the target table.
- Second syntax will check for existing rows. If there are rows, lookup will update data into the target table.
- Create Filter1 and drag all the ports from source qualifier and give filter condition as NEWFLAG
- Connect update strategy and give the 'Update Strategy Expression' as DD_INSERT
- Create Filter2 and drag Cust key and Version ports from expression and comparision keys from source and give filter condition as CHANGEFLAG
- Create another Expression, connect all ports from filter2 and create output assign +1 value to Version port as below.
- Connect to update strategy transformation.
- Connect update strategy and give the 'Update Strategy Expression' as DD_UPDATE
- Make two instances of target, Connect update strategy 1 to target 1 and update strategy 2 to target 2.
- Create Seq Gen and connect to Expression with version port and assign value as 1. Connect it to Target
- Final mapping looks like below screen shot
Note 1 : Lookup condition should be on logical key column
Note 2 : lookup transformation should contain Logical key, Primary key & Comparison key columns.
Note 3 : Second pipeline in the mapping contains comparison key, primary key & changed flag columns.
Note 4 : Expression transformation should contain only primary key, comparision keys along with related source keys and new and changed flag.
First we will focus on Version method.
Create a mapping and name it.
- Drag Emp source into the mapping area.
- Create a Lookup transformation on target table.
- On Primary key columns you should define your condition, because its the matching column between source and target.
- Create an Expression, drag the ports from lookup transformation and name as prev record. This tells us that records are old ones which are used to compare with new ones.
- Drag the comparision keys from source qualifier to Expression
- Create two ports as Newflag and Changedflag and give syntax as Isnull (Cust_key) and Not Isnull(Cust_key) and (Prev_Empn != Empno OR Prev_Ename != Ename OR Prev_Sal != Sal)
- First syntax will check for nulls. If there are null, lookup will insert data into the target table.
- Second syntax will check for existing rows. If there are rows, lookup will update data into the target table.
- Create Filter1 and drag all the ports from source qualifier and give filter condition as NEWFLAG
- Connect update strategy and give the 'Update Strategy Expression' as DD_INSERT
- Create Filter2 and drag Cust key and Version ports from expression and comparision keys from source and give filter condition as CHANGEFLAG
- Create another Expression, connect all ports from filter2 and create output assign +1 value to Version port as below.
- Connect to update strategy transformation.
- Connect update strategy and give the 'Update Strategy Expression' as DD_UPDATE
- Make two instances of target, Connect update strategy 1 to target 1 and update strategy 2 to target 2.
- Create Seq Gen and connect to Expression with version port and assign value as 1. Connect it to Target
- Final mapping looks like below screen shot
Note 1 : Lookup condition should be on logical key column
Note 2 : lookup transformation should contain Logical key, Primary key & Comparison key columns.
Note 3 : Second pipeline in the mapping contains comparison key, primary key & changed flag columns.
Note 4 : Expression transformation should contain only primary key, comparision keys along with related source keys and new and changed flag.
No comments:
Post a Comment