ETL and Metadata The ETL Process Source Systems

ETL and Metadata

The ETL Process Source Systems Presentation System Staging Area Extract Transform Load

Source Data Record the name location and data that exists in the TPS environment. File names and location Layout Attribute meaning Source Business Owner IS Owner Platform Location Data Source Description

Extraction Copy specific data directly from the source tables into a working dataset in the staging area. Targ et Table Target Column Dat a Ty pe Le n Target Column Description Sour ce Syst em Source Table / File Source Col / Field Data Txform Notes

Transformation (Dimension Tables) Generate surrogate key in a primarysurrogate 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.

Transformation (Fact tables) Join all dimensions to the fact table (using original primary keys). Insert surrogate keys Generate derived facts Generate indicator flags Ch g Fla g Fact Gro up Derived Fact Name Derived Fact Description Typ e Ag g Rul e Formula Constra ints Transf or- matio ns

Target Data Describe the presentation data structure. Model Metadata Usage and constraints Table Name Column Name Dat a Typ e Len Column Description Null s? PK PK Or der FK

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