SCD Type2 Mapping Design flow

Let us drive the point home using a simple scenario. For eg., in the current month i.e., (01-01-2010) we are provided with a source table with the three columns and three rows in it like (Empno, Ename, Sal). There is a new employee added and one change in the records in the month (01-02-2010). We are going to use the SCD-2 style to extract and load the records in to target table.

The thing to be noticed here is if there is any update in the salary of any employee then the history of that employee is displayed with the current date as the start date and the previous date as the end date.

Source Table: (01-01-11)

Emp no	Ename	Sal
101	A	1000
102	B	2000
103	C	3000

Target Table: (01-01-11)

Skey	Emp no	Ename	Sal	S-date	E-date	Ver	Flag
100	101	A	1000	01-01-10	Null	1	1
200	102	B	2000	01-01-10	Null	1	1
300	103	C	3000	01-01-10	Null	1	1

Source Table: (01-02-11)

Emp no	Ename	Sal
101	A	1000
102	B	2500
103	C	3000
104	D	4000

Target Table: (01-02-11)

Skey	Emp no	Ename	Sal	S-date	E-date	Ver	Flag
100	101	A	1000	01-02-10	Null	1	1
200	102	B	2000	01-02-10	Null	1	1
300	103	C	3000	01-02-10	Null	1	1
201	102	B	2500	01-02-10	01-01-10	2	0
400	104	D	4000	01-02-10	Null	1	1

In the second Month we have one more employee added up to the table with the Ename D and salary of the Employee is changed to the 2500 instead of 2000.

Step 1: Is to import Source Table and Target table.

·         Create a table by name emp_source with three columns as shown above in oracle.

·         Import the source from the source analyzer.

·         Drag the Target table twice on to the mapping designer to facilitate insert or update process.

·         Go to the targets Menu and click on generate and execute to confirm the creation of the target tables.

·         The snap shot of the connections using different kinds of transformations are shown below.

·         In The Target Table we are goanna add five columns (Skey, Version, Flag, S_date ,E_Date).

Step 2: Design the mapping and apply the necessary transformation.

·         Here in this transformation we are about to use four kinds of transformations namely Lookup transformation (1), Expression Transformation (3), Filter Transformation (2), Sequence Generator. Necessity and the usage of all the transformations will be discussed in detail below.

Look up Transformation: The purpose of this transformation is to Lookup on the target table and to compare the same with the Source using the Lookup Condition.

·         The first thing that we are going to do is to create a look up transformation and connect the Empno from the source qualifier to the transformation.

·         The snapshot of choosing the Target table is shown below.

·         Drag the Empno column from the Source Qualifier to the Lookup Transformation.

·         The Input Port for only the Empno1 should be checked.

·         In the Properties tab (i) Lookup table name à Emp_Target.

(ii)Look up Policy on Multiple Mismatch à use Last Value.

(iii) Connection Information àOracle.

·         In the Conditions tab (i) Click on Add a new condition

(ii)Lookup Table Column should be Empno, Transformation port should be Empno1 and Operator should ‘=’.

Expression Transformation: After we are done with the Lookup Transformation we are using an expression transformation to find whether the data on the source table matches with the target table. We specify the condition here whether to insert or to update the table. The steps to create an Expression Transformation are shown below.

·         Drag all the columns from both the source and the look up transformation and drop them all on to the Expression transformation.

·         Now double click on the Transformation and go to the Ports tab and create two new columns and name it as insert and update. Both these columns are goanna be our output data so we need to have unchecked input check box.

·         The Snap shot for the Edit transformation window is shown below.

·         The condition that we want to parse through our output data are listed below.

Insert: IsNull(EmpNO1)

Update: iif(Not isnull (Skey) and Decode(SAL,SAL1,1,0)=0,1,0) .

·         We are all done here. Click on apply and then OK.

Filter Transformation: 

·         We need two filter transformations the purpose the first filter is to filter out the records which we are goanna insert and the next is vice versa.

·         If there is no change in input data, then filter transformation 1 forwards the complete input to Exp 1 and same output is goanna appear in the target table.

·         If there is any change in input data, then filter transformation 2 forwards the complete input to the Exp 2 then it is going to forward the updated input to the target table.

·         Go to the Properties tab on the Edit transformation

(i)                   The value for the filter condition 1 is Insert.

(ii)                 The value for the filter condition 2 is Update.

·         The closer view of the connections from the expression to the filter is shown below.

Sequence Generator: 

·         We use this to generate an incremental cycle of sequential range of number. The purpose of this in our mapping is to increment the Skey in the bandwidth of 100.

·         We are going to have a sequence generator and the purpose of the sequence generator is to increment the values of the skey in the multiples of 100 (bandwidth of 100).

·         Connect the output of the sequence transformation to the Exp 1.

Expression Transformation:

Exp 1: It updates the target table with the skey values. Point to be noticed here is skey gets multiplied by 100 and a new row is generated if there is any new EMP added to the list. Else the there is no modification done on the target table.

·         Drag all the columns from the filter 1 to the Exp 1.

·         Now add a new column as N_skey and the expression for it is going to be Nextval1*100.

·         We are goanna make the s-date as the o/p and the expression for it is SYSDATE.

·         Flag is also made as output and expression parsed through it is 1.

·         Version is also made as output and expression parsed through it is 1.

Exp 2: If same employee is found with any updates in his records then Skey gets added by 1 and version changes to the next higher number,F

·         Drag all the columns from the filter 2 to the Exp 2.

·         Now add a new column as N_skey and the expression for it is going to be Skey+1.

·         Both the S_date and E_date is going to be SYSDATE.

Exp 3: If any record of in the source table gets updated then we make it only as the output.

·         If change is found then we are going to update the E_Date to S_Date.

·         Update Strategy: This is place from where the update instruction is set on the target table.

·         The update strategy expression is set to 1.

·         Step 3: Create the task and Run the work flow.

·         Don’t check the truncate table option.

·         Change Bulk to the Normal.

·         Run the work flow from task.

·         Create the task and run the work flow.

Step 4: Preview the Output in the target table.