SCD Type 3

SCD –Type 3:

                                                                 

This Method has limited history preservation, and we are goanna use skey as the Primary key here.

Source table: (01-01-2011)

Empno	Ename	Sal
101 102 103	A B C	1000 2000 3000

Target Table: (01-01-2011)

Empno	Ename	C-sal	P-sal
101 102 103	A B C	1000 2000 3000	- - -

Source Table: (01-02-2011)

Empno	Ename	Sal
101 102 103	A B C	1000 4566 3000

Target Table (01-02-2011):

Empno	Ename	C-sal	P-sal
101 102 103 102	A B C B	1000 4566 3000 4544	- Null - 4566

So hope u got what I’m trying to do with the above tables:

Step 1: Initially in the mapping designer I’m goanna create a mapping as below. And in this mapping I’m using lookup, expression, filter, update strategy to drive the purpose. Explanation of each and every Transformation is given below.

Step 2: here we are goanna see the purpose and usage of all the transformations that we have used in the above mapping.

Look up Transformation: The look Transformation looks the target table and compares the same with the source table. Based on the Look up condition it decides whether we need to update, insert, and delete the data from being loaded in to the target table.

• As usually we are goanna connect Empno column from the Source Qualifier and connect it to look up transformation. Prior to this Look up transformation has to look at the target table.
• Next to this we are goanna specify the look up condition empno =empno1.
• Finally specify that connection Information (Oracle) and look up policy on multiple mismatches (use last value) in the Properties tab.

Expression Transformation:

We are using the Expression Transformation to separate out the Insert-stuff’s and Update- Stuff’s logically.

• Drag all the ports from the Source Qualifier and Look up in to Expression.
• Add two Ports and Rename them as Insert, Update.
• These two ports are goanna be just output ports. Specify the below conditions in the Expression editor for the ports respectively.

Insert: isnull(ENO1 )

Update: iif(not isnull(ENO1) and decode(SAL,Curr_Sal,1,0)=0,1,0)

Filter Transformation: We are goanna use two filter Transformation to filter out the data physically in to two separate sections one for insert and the other for the update process to happen.

Filter 1:

• Drag the Insert and other three ports which came from source qualifier in to the Expression in to first filter.
• In the Properties tab specify the Filter condition as Insert.

Filter 2:

• Drag the update and other four ports which came from Look up in to the Expression in to Second filter.
• In the Properties tab specify the Filter condition as update.

Update Strategy: Finally we need the update strategy to insert or to update in to the target table.

Update Strategy 1: This is intended to insert in to the target table.

• Drag all the ports except the insert from the first filter in to this.
• In the Properties tab specify the condition as the 0 or dd_insert.

Update Strategy 2: This is intended to update in to the target table.

• Drag all the ports except the update from the second filter in to this.
• In the Properties tab specify the condition as the 1 or dd_update.

Finally connect both the update strategy in to two instances of the target.

Step 3: Create a session for this mapping and Run the work flow.

Step 4: Observe the output it would same as the second target table