hybrid SCD (type 1 and 2 in one dimension)

Luk · Post by **Luk** » Thu Sep 01, 2005 2:33 am

Hello!

I want to implement hybrid SCD. Some fields in my dimension are type 1 and some are type 2. I am using CRC matching to detect changes. Then I want to proceed with type 2 changes and then I want to update all type 1 fields matching on source PK.
What is my question? When I match CRCs I know that something has changed in record but I don't know what it is. Is it type 1 field or type 2? I must know that because I need to do different actions on type 2 columns and differennt on type 1 columns!

I have two ideas to solve this issue:
1) divide source table into SCD type 2 columns, and type1 columns; calculate two CRCs (on divided tables) and proceed
2) merge two files from two extracts (previous and actual); sort it, use RowProcCompareWithPreviousValue() function to detect on which field change occured and proceed

Is there some other solution? Are my solutions proper to my case?Which is better? (I think first - more simple)

Thanks for opinions in advance

elavenil · Post by **elavenil** » Thu Sep 01, 2005 3:42 am

We had the same requirement in my earlier project. We split the source into 2 as you mentioned in your first logic, which is type 1 and type 2 columns and used Change Capture stage to identify the delta.

Would recommend the method 1, which would be easy and easy to maintain as well.

Regards
Saravanan

Luk · Post by **Luk** » Fri Sep 02, 2005 1:07 am

thanks for opinion elavenil! This is the only opinion so I assume that my first logic is the best way to solve this issue

regards