Tips on how to implement SCD type 6 in Datastage

[stiantok]

Hi!

Anyone have some tips or best practice on how to implement SCD type 6 dimension loading in Datastage?

My case: I have a large profile table that is incrementally loaded with combination of codes and their corresponding descriptions each day. If a code gets a new description this should be reflected in _all_ the rows in the profile table, not only the ones that are loaded this day or later. I thus have to find a way to update the code description for all the rows where the code is present.

This could be done by doing lookup on all the code columns, comparing them in a transformer (existing desc vs new desc) and then updating one and one column on each link. In my case i have 16 of these code/code descriptions, and thus would need 2*16 = 32 links, which I think is rather messy... Is there a better way to do this?

[ArndW]

Although I'd like to debate wisdom of using a SCD Type 6 in real life rather than in a lecture or book setting I won't - and I can think of any simple way to do this in DataStage. Perhaps a recursive SQL call?

[priyadarshikunal]

Read about having a junk dimension as mini dimension of profile table. If its dimension star schema then I think its better to have that linkage through fact table instead of a mini dimension or an out-rigger. So have a SCD type 1 dimension with all the codes (junk dimension) linked to profile dimension through the fact table, IMO ofcourse. It depends on the requirement though.

Its a modelling tip, not for datastage.

For implementing SCD 6 it in datastage, you will have to do it in two pass,
One to update related records when only SCD1 attribute changes and another
one to insert new records if SCD 2 attributes changes along with SCD 1 attributes.

[ray.wurlod]

Does not the SCD stage allow one to specify some columns for Type 1 handling and others for Type 2?