ETL and Metadata

1
ETL and Metadata
2
The ETL Process
Presentation System
Source Systems
Staging Area
Transform
Extract
Load
3
Source Data

• Record the name location and data that exists in
  the TPS environment.
• File names and location
• Layout
• Attribute meaning

4
Extraction

• Copy specific data directly from the source
  tables into a working dataset in the staging area.

5
Transformation (Dimension Tables)

• Generate surrogate key in a primary-surrogate
  table. Make this permanent.
• Insert the surrogate key into the working
  dimension tables.
• Conduct any editing/cleaning operations you need
  (usually on the working table)
• Generate any derived attributes you need.
• Generate and retain process logs.

6
Transformation(Fact tables)

• Join all dimensions to the fact table (using
  original primary keys). Insert surrogate keys
• Generate derived facts
• Generate indicator flags

7
Target Data

• Describe the presentation data structure.
• Model
• Metadata
• Usage and constraints

8
Flow Documentation

• DFD for the ETL process
• ERD for Source, Staging and Target databases.
• Metadata
• Usage notes.