Type II Dimension Implementation Using DS 7.1

[Abhijeet1980]

Friends,

How to implement Type II Dimension using DS 7.1.

Note: No help to be taken from underlying DBMS.

Regards,
Abhijit

[chulett]

Same way you'd do it in any other version.

First step would be to document the logical steps involved, then implement them in a job. It doesn't take anything fancy, just 'normal' DataStage techniques... and not really all that hard to work out.

You might want to explain what this is supposed to mean. Your DBMS will obviously be involved in all this, but no need for triggers or procs or anything, if that's what you are worried about.

Note: No help to be taken from underlying DBMS.

[ray.wurlod]

Procrastinate until verion 8.0 is available and use its SCD stage.

[vmcburney]

They are on server edition, they would need to procrastinate and come up with a bundle of money to upgrade to enterprise edition. The tricky part is change data capture. You need to check to see if incoming rows supersede existing rows. You can either write a long statement in a transformer comparing every column or use the CRC32 function to encode and compare the entire row. Do a forum search for CRC32, there are threads on how to use it and a thread on possible glitches.

[Abhijeet1980]

Firstly CRC check will have to be taken care of by a custom function. As per my knowledge CDC part is not the part of the server edition and have to be taken separately.

Secondly how will i generate equential no's required or the surrogate key needed to implement the type II.

Chulett : Can you pls explain me how is it possible using normal datastage techniques.

[chulett]

You don't need a 'custom function' to use CRC32. And no, CDC is not part of the Server product, so you'll need to do CDD - which is where the CRC32 routine comes in. Search for it and you'll find plenty of discussions on the subject.

On the 'how do I do it' front - put some thought into it. What are the logical steps you would need to do? Lay it out for a single row. Once you do, you'll see that it's not that difficult to actually implement.

[vmcburney]

I think in the current version of DataStage Server jobs you can find the CRC32 function on the right mouse click menu within a Transformer stage. It is now part of the standard DataStage install. Generating surrogate keys or sequence numbers is easy, just create a Stage Variable in your transformer and increment it for new rows. Pass in a starting value via a job parameter. You can also use the SDK surrogate key routines that should be in your routines folder.