While trying to laborate with the scd stage, I ran into the following.
The scd-stage sends update and inserts to the upsert link. My problem is that every record gets a new dimension id. This is ok for new records, but not for updates. I thought that updates would keep the old dimid. The problem here is that the link to the fact tables will be lost.
All my attributes are type 1.
I suppose that the NextSurrogateKey() generates a new dimid for each row. How do I make updates keep their old dimid?
If you want detailed help, you will need to provide some details about the properties that you've specified in the stage.
Focus on the column or columns that you've designated with the Business Key purpose code. A lookup failure using the business key always results in a new row regardless of the Type 1 or Type 2 purpose code setttings.
Is all of the metadata for the designated business keys a complete match between source and target?
I have specified two columns as business keys. The lookup succeeds because I can see that my type 1 columns are updated.
The problem is that my surrogate key also gets updated with a new key.
I could exclude the dim id/surrogate key for update in the update part of the upsert. But then the dimid in my fact table will be updated with a new dimid pointing to nothing.
The fact that your type 1 columns are updated is no indication of a lookup success. Type 1 columns that have changed are always "updated" (i.e. dimension tables can have a mixture of type 1 and type 2 attributes). What are your two business key columns? Are they a unique business identifier? Are they an unchanging business identifier? There is a 1 to 1 relationship between business key and surrogate key in a purely type 1 dimension table.
