Type 3 SCD - Informatica

View previous topic View next topic Go down

Type 3 SCD - Informatica

Post  Balas on Thu Jul 19, 2012 11:52 pm

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

Balas

Posts : 11
Join date : 2012-04-26

View user profile

Back to top Go down

Re: Type 3 SCD - Informatica

Post  ngalemmo on Fri Jul 20, 2012 3:31 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Type 3 SCD - Informatica

Post  Balas on Fri Jul 20, 2012 8:29 am

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

Balas

Posts : 11
Join date : 2012-04-26

View user profile

Back to top Go down

Type 3 SCD - Informatica

Post  Balas on Mon Jul 23, 2012 1:16 pm

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.


Balas

Posts : 11
Join date : 2012-04-26

View user profile

Back to top Go down

Re: Type 3 SCD - Informatica

Post  ngalemmo on Mon Jul 23, 2012 4:10 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Type 3 SCD - Informatica

Post  LAndrews on Mon Jul 23, 2012 7:50 pm

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.

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Type 3 SCD - Informatica

Post  ngalemmo on Mon Jul 23, 2012 8:38 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Type 3 SCD - Informatica

Post  Balas on Tue Jul 24, 2012 4:49 pm

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.



Balas

Posts : 11
Join date : 2012-04-26

View user profile

Back to top Go down

Re: Type 3 SCD - Informatica

Post  vickyejain on Mon Aug 20, 2012 4:30 am

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.

vickyejain

Posts : 7
Join date : 2012-08-20

View user profile

Back to top Go down

Re: Type 3 SCD - Informatica

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum