Type 3 SCD - Informatica

Hi Experts-


Please through your experience to solve the below Issue.


Consider there are 2 tables. One is look up table say, Look_Tab and the other one is Master table say Mast_tab. Please find the Look_tab definitions as below,


Look_tab

Id_col col_1

1 1

2 9

3 7

4 0

5 6

6 8

7 2

8 7

9 6

10 4


Based on the look up table we need to populate the values in the Mast_tab as below. Whenever the ID columns in both the tables are equal then the col_1 value in the Mast_tab needs to be updated with the Look_tab’s col_1.


Select Mast_tab.col1=Look_tab.Col_1

From Look_tab , Mast_tab

Where Mast_tab.Id_col = Look_tab.Id_col


Our Issue will start here, we needs to have a code which can move the Column values from left to right (Col_1 to Col_10) as in the below Mast_tab.


Mast_tab

Id_col col_1 col_2 col_3 col_4 col_5 col_6 col_7 col_8 col_9 col_10

1 1 0 0 0 0 0 0 0 0 0

2 9 1 0 0 0 0 0 0 0 0

3 7 9 1 0 0 0 0 0 0 0

4 0 7 9 1 0 0 0 0 0 0

5 6 0 7 9 1 0 0 0 0 0

6 8 6 0 7 9 1 0 0 0 0

7 2 8 6 0 7 9 1 0 0 0

8 7 2 8 6 0 7 9 1 0 0

9 6 7 2 8 6 0 7 9 1 0

10 4 6 7 2 8 6 0 7 9 1


Since I'm new to Informatica, please provide me step wise/complete solution.

Quick and reliable replies will be greatly appreciable.
Thanks in Advance.


-Balas

The problem with trying to update type 3 dimensions is you need the old values from the type 3 columns if you try to do a simple update.

If your database supports triggers, it is much, much easier to implement a type 3 by using a trigger to roll the values. With a trigger you can treat the table as a type 1 in your ETL process, ignoring the additional type 3 columns and let the trigger worry about them. Rolling columns in a trigger is simple as it has visibility to both the old and new column values.

Thanks for the reply ngalemmo.

I'm working on Sybase IQ.
New to DWH and using triggers.

Please kindly share me an example script to Implement this task.

Thanks,
Balas

I'm really waiting for the solution.

In the below example I have given 10 columns but in my DB I needs to Implement this logic on 360 columns.

Please provide me the way to use Sybase IQ Triggers for implementing Type 3 SCDs with sample script.

Thanks In advance.
Balas.

I haven't written SQL for IQ. You need to look at the database manuals and play with it. IF it supports update triggers, there is usually a method to reference the old and new images. You then need to write code in the trigger to check to see if the new current value is different than the old current value then put the old current value into the new image's previous value column. Ask the DBA for assistance.

Is this really a type-3 transformation?

Your example looks like you are trying to pivot/denormalize your "look_tab".

In my experience, type-3 transformations are quite rare, and usually are not part of a normal etl process.

When they can be automated, as ngallemo suggested, database triggers can be utilized. Typically this provides a simple "prior" value attribute.

Your solution requires 360 type 3 attributes? Perhaps the requirement has been confused, that sounds very extreme.

I agree. After re-reading the post, a type 2 would make a lot more sense.... or even just writing the old rows to another table as a historical log.

Hi Experts-

Since I'm having very less experience in DWH/ETL I need your suggestion with a sample script.

My script needs to take col_1's value to Col_2, from Col_2 to Col_3, from Col_3 to Col_4 ..... Col_359 to Col_360 in the same row. The condition here is, when there is an Update for the Col_1 this data movement should happen else there wont be any data movement.

when A.ID=B.ID and A.Col_1 != B.Col1 then ----> Data movement shold happen. Once the data moved, Col_1 should be updated with new value.

Hopes I will get the required help.

Thanks In advance.

-Balas.

If you're using Informatica for this, the approach should be very straight forward in an Expression transformation - I'm describing a slightly longer path to get to this, you can look for shortcuts depending on your own comfort level with the tool.

- Read all data from the master table in Informatica - this will have your 360 columns + some measure columns.
- Next, use a connected look up to read the value from the Look_tab and bring in all ports into an Expression transformation - this will have all the columns from the Mast_tab + 1 column from the Look_tab.
- Create a variable port (integer) that will contain 0 if the col1 from mast_tab matches with the column you brought from Look_tab or else set it to 1. A 1 would indicate that a shift in the rows is necessary.
- Next set up 360 output ports in the expression transformation starting with the last column with a condition:
new_col360 = iif(variable = 1, col359, col360)
new_col359 = iif(variable = 1, col358, col359)
....

As you can see, this approach is longer and requires all data to be processed each time, once you understand the gist of the approach - you can tune it and find shortcuts that suit your application. Hope this helps.