This is the first in a series of posts see: Metadata Driven ETL Process with Change Data Capture
Build a robust and flexible Metadata Driven Incremental ETL Process that can easily meet the following seemly impossible requirements.
Examples:
Several weeks ago Jr. ETL Developer made a bad ETL logic change on a SCD2. The Dimensional data needs to be recreated but the source system does not keep historical changes for the required attributes
Business wants to change a dimension SCD1 to SCD2 and past historical attribute changes need to be included. The source system does not maintain historical changes
SCD2 and SCD1 Dimensions need to be completely re-architected without losing any historical data and creating past historical attribute changes where needed.
Jr. DBA truncated SCD2 Dimension and backups are corrupted. Dimension needs to be re-created with accurate past historical attribute changes.
Just Downloaded the zip and follow the directions in the script:
Note: the SSIS packages connection managers point to the local instance of AdventureWorks
additional Links:
SSIS Dimension Merge SCD Component
PragmaticWorks: BIXpress (performance and row count FrameWork) . This is similar to my framework but it is much easier to implement and the reports are more pretty. It also comes with standard best practices SSIS Design Patterns that can be used as starting place for developing our own standards around SSIS
SQL Server San Antonio user group
Tags: ETL Metadata
September 26, 2011 at 4:22 pm |
[...] This post is a continuation of my previous post entitled ‘Metadata Driven ETL Process’. If you have not already done so, download Metadata ETL Demo.zip and walk through step-by-step the [...]
September 29, 2011 at 7:59 pm |
[...] Metadata Driven ETL Process [...]
January 4, 2012 at 1:41 am |
[...] Metadata Driven ETL Process is something unique that has saved me a lot of headaches! Hopefully it can help you as well. [...]