SCD stage
Moderators: chulett, rschirm, roy
SCD stage
I'm trying to use the SCD stage to implement SCD2 logic. The issue I have is that a new surrogate key is generated for both updated records and new records. That is if a records is updated then the result is two record in the dimension update link:
1) entry to expire the old record with the SK and EXP_DT
2) entry to add the new updated record with all columns.
The issue is that for the updated record it doesn't keep the original key from the dimension lookup. It generates a new key. But a new key should ONLY be generated if the record was not found in the dimension lookup.
Is this yet another bug in the SCD stage or am I missing something?
1) entry to expire the old record with the SK and EXP_DT
2) entry to add the new updated record with all columns.
The issue is that for the updated record it doesn't keep the original key from the dimension lookup. It generates a new key. But a new key should ONLY be generated if the record was not found in the dimension lookup.
Is this yet another bug in the SCD stage or am I missing something?
Mike
I just answered my own question.
It is a bug in the SCD stage:
even thought the dimension table is not displayed in the source column pane you can still access it through transformer script.
example:
if isNull(out_odbc_Products.PRODUCT_SK) then
NextSurrogateKey()
else
out_odbc_Products.PRODUCT_SK
It's actually kind of funny because when you type this in then datastage draws a column link going to an invisible table:-)
It is a bug in the SCD stage:
even thought the dimension table is not displayed in the source column pane you can still access it through transformer script.
example:
if isNull(out_odbc_Products.PRODUCT_SK) then
NextSurrogateKey()
else
out_odbc_Products.PRODUCT_SK
It's actually kind of funny because when you type this in then datastage draws a column link going to an invisible table:-)
Mike
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 45
- Joined: Fri Nov 07, 2008 12:22 pm