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.
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
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
Tags: ETL Metadata