scrrbiswas (10/28/2013)
This topic itself is nice and the techniques mentioned in this article can be used in many occasions.However "change a dimension ID to see what happens" section does not cover the situation when the staging fact table already has a transaction (record) with same sale person id , product id and date.
The following update statement will create a record with a duplicate key (100, 1004, 20101102), if allowed.
UPDATE staging.SalesDetailSource
SET SalesPersonID = 100
WHERE ProductID = 1004
AND SalesPersonID = 101
AND SaleDate = 20101102;
Any explanation on how to handle this situation will be interesting.
Thanks.
Depends on if you want a Historical record for this dimension table update.
Is this just a situation where you want to update the Business Key Id in the dim table when it changes in the source?
Maybe some details on what is a Suragate Key and a Business Key in your statement above?
You are using Suragate keys in your Dimension tables right?