Tuesday 26 April 2011

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.

clip_image002

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)

clip_image004

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.

clip_image006

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.

clip_image008

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.clip_image010

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.

clip_image012

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

clip_image014

No comments :

Post a Comment