We have a legacy database whose data needs to be included in our yet-to-be-built sql 2005 data warehouse. Some of the tables don’t have any natural candidates for a primary key. (Of course, we also need to add other data to the mix.)
Suppose we load the empty warehouse initially. In following loads we don’t want to include those records that haven’t changed from the first load (“duplicates”) but we also don’t want to delete the contents of the entire warehouse because of the load time. Any ideas/best practices how to handle “incremental updates” to a warehouse would be appreciated.
TIA,
Bill
Making "incremental" updates to a data warerhouse
Hector
Here's a few references:
http://blogs.conchango.com/jamiethomson/archive/2005/06/06/1543.aspx (includes a demo)
http://sqljunkies.com/WebLog/tpagel/archive/2005/07/24/16195.aspx
Probably the best available reference is BOL.
-Jamie
slphosts
Can you explain how "slowly changing dimensions" can be used to create this type of process (I'm new to this stuff and I can't make the connection on my own.)
TIA,
Bill
kaky
You can use the Slowly Changing Dimension Wizard, an advanced transformation component, to create this type of process quite easily.
_nicola
<<tables don’t have any natural candidates for a primary key>>
The above situation is likely a fundamental design flaw for which there is no easy fix. What is the usefullness and business meaning of an entity for which there is no natural key It is likely nonsense.
However, from your definition of the problem, it sounds more like the key would be the combination of all columns in the row. How else would you define what is a "new" row vs. a "changed" row Isn't that what you are describing If so, there is your key.
Ken