In any data warehouse, tables can be grouped under the following categories:
- Fact tables
- Slowly changing dimension tables
- Dimension tables not falling under slowly changing dimensions
- Summary tables
- Data Dictionary / Metadata tables
To build a solid ETL Plan for any warehouse, each of the stated categories needs different attention from the ETL programmer.
The ETL workflow should be as follows:
Step 1: Dimension Tables falling under non-SCD category
Step 2: Dimension Tables falling under SCD category
Step 3: Track and store historical data for SCD depending on the tracking level; the main objective of this step is to load and determine the current active data for SCD
Step 4: Fact Tables
Step 5: Data Dictionary / Metadata Tables
Step 6: Build of summary tables
Step 6: Implementation of data quality rules
For more information, check out SearchCRM's Best Web Links on Data Warehousing.