Designing and Developing the ETL System

Step 1 — Draw the High Level Plan
Step 2 — Choose an ETL Tool
1. Self-documentation that comes from using a graphical tool.
2. ETL tools offer advanced transformation logic.
3. Improved system performance at a lower level of expertise.
Step 3 — Develop Default Strategies
Step 4 — Drill Down by Target Table
1. Ensure Clean Hierarchies
2. Develop Detailed Table Schematics
3. Develop the ETL Specification Document
4. Develop a Sandbox Source System
5. Develop a Sandbox Source System
Step 5 — Populate Dimension Tables with Historic Data
1. Populate Type 1 Dimension Tables
  1. Dimension Transformations
  2. Dimension Table Loading
2. Populate Type 2 Dimension Tables History
  1. Populate Date and Other Static Dimensions
Step 6 — Perform the Fact Table Historic Load
1. Historic Fact Table Extracts
2. Fact Table Transformations
3. Fact Table Loading
4. Test, Test, and Test Again
5. Develop Incremental ETL Processing
Step 7 — Dimension Table Incremental Processing
1. Identify New and Changed Dimension Rows
2. Process Changes to Dimension Attributes
Step 8 — Fact Table Incremental Processing
1. Fact Table Extract and Data Quality Checkpoint
2. Fact Table Transformations and Surrogate Key Pipeline
3. Late Arriving Facts and the Surrogate Key Pipeline
4. Incremental Fact Table Load
5. Speed Up the Load Cycle
Step 9 — Aggregate Table and OLAP Loads
Step 10 — ETL System Operation and Automation
1. Schedule Jobs
2. Handle Predictable Exceptions and Errors Automatically
3. Handle Unpredictable Errors Gracefully
4. Maintain Database Objects
5. Develop and Test ETL Automation

Siguiente

Designing and Developing the ETL System

Descripción

Resumen del Recurso

Recursos multimedia adjuntos

Similar

	Creado por Thean Olivier hace más de 7 años