Informatica Reference

Archive for the ‘SCD Type’ Category

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
101102103 ABC 100020003000

Target Table: (01-01-2011)

Empno Ename C-sal P-sal
101102103

 

ABC 100020003000

Source Table: (01-02-2011)

Empno Ename Sal
101102103

 

ABC

 

100045663000

Target Table (01-02-2011):

Empno Ename C-sal P-sal
101102103

102

ABC

B

100045663000

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

Refer : http://informaticatutorials-naveen.blogspot.com/2011/04/scd-type-3.html

Let us drive the point home using a simple scenario. For eg., in the current month ie.,(01-01-2010) we are provided with an 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 gonna 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.

clip_image002

  • 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 gonna 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.

clip_image004

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

clip_image006

  • 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 gonna 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.

clip_image008

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.

clip_image010

  • We are gonna 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.

clip_image012

  • 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 gonna 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 gonna be Skey+1.
  • Both the S_date and E_date is gonna be sysdate.

clip_image014

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

clip_image016

  • If change is found then we are gonna 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.

clip_image018

Step 4: Preview the Output in the target table.

clip_image020

Refer : http://informaticatutorials-naveen.blogspot.com/2011/04/type-2_21.html

Slowly Changing Dimensions (SCDs) are dimensions that have data that changes slowly, rather than changing on a time-based, regular schedule

For example, you may have a dimension in your database that tracks the sales records of your company’s salespeople. Creating sales reports seems simple enough, until a salesperson is transferred from one regional office to another. How do you record such a change in your sales dimension?

You could sum or average the sales by salesperson, but if you use that to compare the performance of salesmen, that might give misleading information. If the salesperson that was transferred used to work in a hot market where sales were easy, and now works in a market where sales are infrequent, her totals will look much stronger than the other salespeople in her new region, even if they are just as good. Or you could create a second salesperson record and treat the transferred person as a new sales person, but that creates problems also.

Dealing with these issues involves SCD management methodologies:

Type 1:

The Type 1 methodology overwrites old data with new data, and therefore does not track historical data at all. This is most appropriate when correcting certain types of data errors, such as the spelling of a name. (Assuming you won’t ever need to know how it used to be misspelled in the past.)

Here is an example of a database table that keeps supplier information:

Supplier_Key Supplier_Code Supplier_Name Supplier_State
123 ABC Acme Supply Co CA

In this example, Supplier_Code is the natural key and Supplier_Key is asurrogate key. Technically, the surrogate key is not necessary, since the table will be unique by the natural key (Supplier_Code). However, the joins will perform better on an integer than on a character string.

Now imagine that this supplier moves their headquarters to Illinois. The updated table would simply overwrite this record:

Supplier_Key Supplier_Code Supplier_Name Supplier_State
123 ABC Acme Supply Co IL

The obvious disadvantage to this method of managing SCDs is that there is no historical record kept in the data warehouse. You can’t tell if your suppliers are tending to move to the Midwest, for example. But an advantage to Type 1 SCDs is that they are very easy to maintain.

Explanation with an Example:

Source Table: (01-01-11) Target Table: (01-01-11)

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

The necessity of the lookup transformation is illustrated using the above source and target table.

Source Table: (01-02-11) Target Table: (01-02-11)

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

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.
  • In the same way as above create two target tables with the names emp_target1, emp_target2.
  • 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.

clip_image002

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, Expression Transformation, Filter Transformation, Update Transformation. Necessity and the usage of all the transformations will be discussed in detail below.

Look up Transformation: The purpose of this transformation is to determine whether to insert, Delete, Update or reject the rows in to target table.

  • The first thing that we are goanna 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.

clip_image004

  • What Lookup transformation does in our mapping is it looks in to the target table (emp_table) and compares it with the Source Qualifier and determines whether to insert, update, delete or reject rows.
  • In the Ports tab we should add a new column and name it as empno1 and this is column for which we are gonna connect from the Source Qualifier.
  • The Input Port for the first column should be unchked where as the other ports like Output and lookup box should be checked. For the newly created column only input and output boxes should be checked.
  • In the Properties tab (i) Lookup table name ->Emp_Target.

(ii)Look up Policy on Multiple Mismatch -> use First 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 check whether we need to insert the records the same records or we need to update the records. 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 gonna be our output data so we need to have check mark only in front of the Output check box.
  • The Snap shot for the Edit transformation window is shown below.

clip_image006

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

Input à IsNull(EMPNO1)

Output à iif(Not isnull (EMPNO1) and Decode(SAL,SAL1,1,0)=0,1,0) .

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

Filter Transformation: we are gonna have two filter transformations one to insert and other to update.

  • Connect the Insert column from the expression transformation to the insert column in the first filter transformation and in the same way we are gonna connect the update column in the expression transformation to the update column in the second filter.
  • Later now connect the Empno, Ename, Sal from the expression transformation to both filter transformation.
  • If there is no change in input data then filter transformation 1 forwards the complete input to update strategy transformation 1 and same output is gonna appear in the target table.
  • If there is any change in input data then filter transformation 2 forwards the complete input to the update strategy transformation 2 then it is gonna 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 1 is Update.

  • The Closer view of the filter Connection is shown below.

clip_image008

Update Strategy Transformation: Determines whether to insert, delete, update or reject the rows.

  • Drag the respective Empno, Ename and Sal from the filter transformations and drop them on the respective Update Strategy Transformation.
  • Now go to the Properties tab and the value for the update strategy expression is 0 (on the 1st update transformation).
  • Now go to the Properties tab and the value for the update strategy expression is 1 (on the 2nd update transformation).
  • We are all set here finally connect the outputs of the update transformations to the target table.

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.

Step 4: Preview the Output in the target table.

clip_image010

Refer : http://informaticatutorials-naveen.blogspot.com/2011/04/scd-type-1.html