Hi everyone!
Anyone implements SCD type 2 in DataStage?
What are the approaches thay you use?
If we want to compare an entire record for track changes how do you do? Do you use DataStage or do you call a PL/SQL?
Any suggestions?
Thank you all in advance.
Regards,
Nuno Pimenta
SCD type 2 in DataStage
Ive chosen to do this in DataStage. I keep a copy of my dimension in a hash file so that the processing is done more quickly. I do a lookup into the hash file based on the natural key. Use a stage variable to check to see if there are any differences in the two records. This variable will be a flag set to true if there are differences (or if the lookup was a NULL). Use another stage variable to test that flag. If it is true, call the NexyKey routine provided with the sdk to generate the new surrogate key. Then write out the new record to both the hash file and database (use a constraint to these two output links based on the first stage variable - in this way you generate keys and write records based on the same test).
Nuno Pimenta wrote:
Hi everyone!
Anyone implements SCD type 2 in DataStage?
What are the approaches thay you use?
If we want to compare an entire record for track changes how do you do? Do you use DataStage or do you call a PL/SQL?
Any suggestions?
Thank you all in advance.
Regards,
Nuno Pimenta
---------------------------------
Do You Yahoo!?
Listen to your Yahoo! Mail messages from any phone with Yahoo! by Phone.
Nuno Pimenta wrote:
Hi everyone!
Anyone implements SCD type 2 in DataStage?
What are the approaches thay you use?
If we want to compare an entire record for track changes how do you do? Do you use DataStage or do you call a PL/SQL?
Any suggestions?
Thank you all in advance.
Regards,
Nuno Pimenta
---------------------------------
Do You Yahoo!?
Listen to your Yahoo! Mail messages from any phone with Yahoo! by Phone.
SCDs are straightforward in DataStage. I will forward a document on working with all SCDs to you directly.
Look for a SCD container (similar to the document) coming soon....
-----Original Message-----
From: Nuno Pimenta [mailto:nuno.pimenta@tmn.pt]
Sent: Thursday, September 27, 2001 11:17 AM
To: datastage-users@oliver.com
Subject: SCD type 2 in DataStage
Hi everyone!
Anyone implements SCD type 2 in DataStage?
What are the approaches thay you use?
If we want to compare an entire record for track changes how do you do? Do you use DataStage or do you call a PL/SQL?
Any suggestions?
Thank you all in advance.
Regards,
Nuno Pimenta
Look for a SCD container (similar to the document) coming soon....
-----Original Message-----
From: Nuno Pimenta [mailto:nuno.pimenta@tmn.pt]
Sent: Thursday, September 27, 2001 11:17 AM
To: datastage-users@oliver.com
Subject: SCD type 2 in DataStage
Hi everyone!
Anyone implements SCD type 2 in DataStage?
What are the approaches thay you use?
If we want to compare an entire record for track changes how do you do? Do you use DataStage or do you call a PL/SQL?
Any suggestions?
Thank you all in advance.
Regards,
Nuno Pimenta
If your database implements serial data types or sequences, you can use these to generate the next surrogate key value. Or you can use the @OUTROWNUM system variable and/or a stage variable, as alternate techniques to using the NextKey routine in the sdk.
Be aware of the auditable columns (the ones you need to check for differences). These may be fewer than the entire set of columns. Load as few columns as possible into the job.
One secret to performance in the job that loads the fact table is to load only the natural key and surrogate key into a hashed file to translate between natural key and current surrogate key.
-----Original Message-----
From: Tony Curcio [mailto:tonycurcio@yahoo.com]
Sent: Friday, 28 September 2001 02:27
To: datastage-users@oliver.com
Subject: Re: SCD type 2 in DataStage
Ive chosen to do this in DataStage. I keep a copy of my dimension in a hash file so that the processing is done more quickly. I do a lookup into the hash file based on the natural key. Use a stage variable to check to see if there are any differences in the two records. This variable will be a flag set to true if there are differences (or if the lookup was a NULL). Use another stage variable to test that flag. If it is true, call the NexyKey routine provided with the sdk to generate the new surrogate key. Then write out the new record to both the hash file and database (use a constraint to these two output links based on the first stage variable - in this way you generate keys and write records based on the same test).
Nuno Pimenta wrote:
Hi everyone!
Anyone implements SCD type 2 in DataStage?
What are the approaches thay you use?
If we want to compare an entire record for track changes how do you do? Do you use DataStage or do you call a PL/SQL?
Any suggestions?
Thank you all in advance.
Regards,
Nuno Pimenta
---------------------------------
Do You Yahoo!?
Listen to your Yahoo! Mail messages from any phone with Yahoo! by Phone.
Be aware of the auditable columns (the ones you need to check for differences). These may be fewer than the entire set of columns. Load as few columns as possible into the job.
One secret to performance in the job that loads the fact table is to load only the natural key and surrogate key into a hashed file to translate between natural key and current surrogate key.
-----Original Message-----
From: Tony Curcio [mailto:tonycurcio@yahoo.com]
Sent: Friday, 28 September 2001 02:27
To: datastage-users@oliver.com
Subject: Re: SCD type 2 in DataStage
Ive chosen to do this in DataStage. I keep a copy of my dimension in a hash file so that the processing is done more quickly. I do a lookup into the hash file based on the natural key. Use a stage variable to check to see if there are any differences in the two records. This variable will be a flag set to true if there are differences (or if the lookup was a NULL). Use another stage variable to test that flag. If it is true, call the NexyKey routine provided with the sdk to generate the new surrogate key. Then write out the new record to both the hash file and database (use a constraint to these two output links based on the first stage variable - in this way you generate keys and write records based on the same test).
Nuno Pimenta wrote:
Hi everyone!
Anyone implements SCD type 2 in DataStage?
What are the approaches thay you use?
If we want to compare an entire record for track changes how do you do? Do you use DataStage or do you call a PL/SQL?
Any suggestions?
Thank you all in advance.
Regards,
Nuno Pimenta
---------------------------------
Do You Yahoo!?
Listen to your Yahoo! Mail messages from any phone with Yahoo! by Phone.