RE: Data warehouse data refresh/update

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Phil (Phil_at_discussions.microsoft.com)
Date: 06/28/04 

Date: Mon, 28 Jun 2004 06:26:01 -0700

Look in SQL Books Online for CHECKSUM_AGG to identify changes in a table. The difficulty then is in trying to identify what has changed. Another route is to try and identify fields within the production data that will identify when it was last changed. Typically these tend to be datetime, timestamp or rowversion data types.

If you keep production keys in your fact table as additional attributes then this will give you another option for identifying new data.

"Ben" wrote:

> Hello,
>
> Im a member of a team planning a data warehousing project. We have multiple data sources which are aggregated in a staging area. This is then denormalised and imported into the datawarehouse database.
>
> I am looking at ideas for incremental data refresh, rather than a drop and re-import of all data. This would allow us to have historic data.
>
> Does anyone have any tips that might be helpful for detecting changes in the source data for import? We have had some bad experiences with triggers on our source database in the past, so would rather not use these. I have considered replication and log shipping, but these just give a replica of the source data and does not flag the updated/new data.
>
> Any help would be greatly appreciated.
>
> Thanks.
>
> Ben.