How to implement the SCD type 3 in Datastage 7.0 ...

kumarreddy · Post by **kumarreddy** » Sun Apr 11, 2004 10:16 pm

Hi all....
How to implement the slowly changing dimension type 3 in Data stage 7.x...
if possible give me one example....that is great help for me...
Thanks
Kumar G

kcbland · Post by **kcbland** » Mon Apr 12, 2004 5:40 am

1. Read the source row.
2. Transform the souce row.
3. Reference the row in the target table (either prestage to hash or do OCI lookup)
4. Update all non-SCD type 3 columns if they are different.
5. Compare SCD type 3 columns against their current value. If different, then move version 3 value to version 4 value, version 2 value to version 3 value, current value to version 2, and new value into the current column. Repeat this for all SCD type 3 columns.
6. If any columns were updated, load the row into the target.

chulett · Post by **chulett** » Mon Apr 12, 2004 5:41 am

Which is it, 6x or 7x?

Do you know how to update an existing record with new information? Not really all that different. Pull all existing relevant information into a Hash and use it as a Reference Lookup. Insert or Update from there.

How much of an 'example' do you need? Is that enough to get you going? I'm assuming you know what a Type 3 Dimension is, it's just the DataStage part you need help with...

If you do need more help, there's always Ken's post. Me too slow.

vmcburney · Post by **vmcburney** » Mon Apr 12, 2004 5:45 pm

If you want an example just upgrade to 7.1 and use the intelligent assistant, it can be used to create a set of example jobs for you. From memory I don't think this assistant was in the 7.0 release.