Status Updates From By Kimball, Ralph The Data ...

p. 509
20 • ETL System Design + Dev Process & Tasks (cont.)
↳ ETL Process Overview (cont.)
... ↳ Develop One-Time Historic Load Processing
... ... ↳ Step 6: Perform the Fact Table Historic Load (cont.)
... ... ... ↳ Fact Table Transformations

« A lot of time (is spent) improving the dimension table, facts usually require modest transformation. »

System values like -1 shall be replaced by NULL. All FKs be NOT NULL.

p. 508
20 • ETL System Design + Dev Process & Tasks (cont.)
↳ ETL Process Overview (cont.)
... ↳ Develop One-Time Historic Load Processing
... ... ↳ Step 6: Perform the Fact Table Historic Load
... ... ... ↳ Historic Fact Table Extracts
... ... ... ↳ Audit Statistics

It is not always possible to tie the data back to source systems; in these cases, it is crucial that reasons for differences be documented clearly.

p. 507
20 • ETL System Design + Dev Process & Tasks (cont.)
↳ ETL Process Overview (cont.)
... ↳ Develop One-Time Historic Load Processing
... ... ↳ Step 5: Populate Dim Tables w Historic Data (cont.)
... ... ... ↳ Dim Table Loading
... ... ... ↳ Load Type 2 Dimension Table History
... ... ... ↳ Populate Date and Other Static Dimensions

p. 506
20 • ETL System Design + Dev Process & Tasks (cont.)
↳ ETL Process Overview (cont.)
... ↳ Develop One-Time Historic Load Processing
... ... ↳ Step 5: Populate Dim Tables w Historic Data
... ... ... ↳ Populate Type 1 Dim Tables
... ... ... ↳ Dim Transformations
➤ Simple Data Transformations
➤ Combine from Separate Sources
➤ Decode Production Codes
➤ Validate M2M one-to-one
➤ Dimension Surrogate Key Assignment

p. 502
20 • ETL System Design and Development Process and Tasks (cont.)
↳ ETL Process Overview (cont.)
... ↳ Develop the ETL Plan (cont.)
... ... ↳ Step 4: Drill Down by Target Table (cont.)
... ... ... ↳ Develop the ETL Specification Document

➤ Develop a Sandbox Source System

p. 501
20 • ETL System Design and Development Process and Tasks (cont.)
↳ ETL Process Overview (cont.)
... ↳ Develop the ETL Plan (cont.)
... ... ↳ Step 4: Drill Down by Target Table
... ... ... ↳ Ensure Clean Hierarchies*
... ... ... ↳ Develop Detailed Table Schematics

« *If the source is an Excel spreadsheet on a business user's desktop—you must either clean it up or acknowledge that it is not a hierarchy. »

p. 500
20 • ETL System Design and Development Process and Tasks (cont.)
↳ ETL Process Overview (cont.)
... ↳ Develop the ETL Plan (cont.)
... ... ↳ Step 3: Develop Default Strategies
... ... ... ↳ Extract
... ... ... ↳ Keep extracted & staged d
... ... ... ↳ Police dim, fact DQ
... ... ... ↳ Manage changes to attributes
... ... ... ↳ Ensure availability
... ... ... ↳ Design auditing
... ... ... ↳ Organize the staging

p. 499
20 • ETL System Design and Development Process and Tasks (cont.)
↳ ETL Process Overview (cont.)
... ↳ Develop the ETL Plan (cont.)
... ... ↳ Step 2: Choose an ETL Tool

« Don't expect to recoup the investment in an ETL tool (fast). (Sometimes it feels it'd be) implemented faster by coding. The big advantages come with future phases, and particularly with future modifications to existing systems. »

p. 498
20 • ETL System Design and Development Process and Tasks (cont.)
↳ ETL Process Overview (cont.)
... ↳ Develop the ETL Plan
... ... ↳ Step 1: Draw the High-Level Plan

« (it) deliberately highlights contemporary questions and unresolved issues; (...) frequently updated and released. You might sometimes keep two versions (work & communication). »

p. 497
20 • ETL System Design and Development Process and Tasks
↳ ETL Process Overview

« Developing the extract, transformation, and load (ETL) system is the hidden part of the iceberg for most DW/BI projects.

So many challenges are buried in the data sources and systems that developing the ETL application invariably takes more time than expected. »

p. 496
19 • ETL Subsystems and Techniques (cont.)
↳ The 34 Subsystems of ETL (cont.)
... ↳ Managing the ETL Environment (cont.)
... ... ⇒ Reliability
... ... ⇒ Availability
... ... ⇒ Manageability
... ... ↳ Subsystem 34: Metadata Repository Manager

p. 495
19 • ETL Subsystems and Techniques (cont.)
↳ The 34 Subsystems of ETL (cont.)
... ↳ Managing the ETL Environment (cont.)
... ... ⇒ Reliability
... ... ⇒ Availability
... ... ⇒ Manageability
... ... ↳ Subsystem 33: Compliance Manager

Combines subsystems:
• Lineage
• Dependency
• Version control
• Backup & restore
• Security
• Audit dimension

B&R:
« Hash-code versions, have them archived by 3rd party »

p. 492
19 • ETL Subsystems and Techniques (cont.)
↳ The 34 Subsystems of ETL (cont.)
... ↳ Managing the ETL Environment (cont.)
... ... ⇒ Reliability
... ... ⇒ Availability
... ... ⇒ Manageability
... ... ↳ Subsystem 31: Parallelizing/Pipelining System
... ... ↳ Subsystem 32: Security System

« The backup media should have as much security surrounding them as the online systems. »

p. 491
19 • ETL Subsystems and Techniques (cont.)
↳ The 34 Subsystems of ETL (cont.)
... ↳ Managing the ETL Environment (cont.)
... ... ⇒ Reliability
... ... ⇒ Availability
... ... ⇒ Manageability
... ... ↳ Subsystem 30: Problem Escalation System

p. 490
19 • ETL Subsystems and Techniques (cont.)
↳ The 34 Subsystems of ETL (cont.)
... ↳ Managing the ETL Environment (cont.)
... ... ⇒ Reliability
... ... ⇒ Availability
... ... ⇒ Manageability
... ... ↳ Subsystem 28: Sorting System
... ... ↳ Subsystem 29: Lineage and Dependency Analyzer

p. 489
19 • ETL Subsystems and Techniques (cont.)
↳ The 34 Subsystems of ETL (cont.)
... ↳ Managing the ETL Environment (cont.)
... ... ⇒ Reliability
... ... ⇒ Availability
... ... ⇒ Manageability
... ... ↳ Subsystem 25: Version Control System
... ... ↳ Subsystem 26: Version Migration System
... ... ↳ Subsystem 27: Workflow Monitor

p. 488
19 • ETL Subsystems and Techniques (cont.)
↳ The 34 Subsystems of ETL (cont.)
... ↳ Managing the ETL Environment (cont.)
... ... ⇒ Reliability
... ... ⇒ Availability
... ... ⇒ Manageability
... ... ↳ Subsystem 24: Recovery and Restart System

p. 486
19 • ETL Subsystems and Techniques (cont.)
↳ The 34 Subsystems of ETL (cont.)
... ↳ Managing the ETL Environment
... ... ⇒ Reliability
... ... ⇒ Availability
... ... ⇒ Manageability
... ... ↳ Subsystem 23: Backup System
... ... ... ⇒ Backup
... ... ... ... ↳ High performance • Simple admin • Automated, lights-out ops
... ... ... ⇒ Archive & Retrieval

p. 483
19 • ETL Subsystems and Techniques (cont.)
↳ The 34 Subsystems of ETL (cont.)
... ↳ Managing the ETL Environment
... ... ⇒ Reliability
... ... ⇒ Availability
... ... ⇒ Manageability
... ... ↳ Subsystem 22: Job Scheduler
... ... ... ⇒ Job definition
... ... ... ⇒ Job scheduling
... ... ... ⇒ Metadata capture
... ... ... ⇒ Logging
... ... ... ⇒ Notification

p. 482
19 • ETL Subsystems and Techniques (cont.)
↳ The 34 Subsystems of ETL (cont.)
... ↳ Delivering: Prepare for Presentation (cont.)
... ... ↳ Subsystem 20: OLAP Cube Builder
... ... ↳ Subsystem 21: Data Propagation Manager