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?
Tips on how to implement SCD type 6 in Datastage
Moderators: chulett, rschirm, roy
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?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
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.
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.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: