ETL%20Processing

1
ETL Processing

• Mechanics of ETL

2
The Flow
Source File
Extract
Transform
Transformed File
Extract
Generate Key
Presentation Mart
Surrogate Keys
3
The Flow
Customer
Extract
Transform
Transformed File
CopyCustomer
Generate Key
Presentation Mart
NewTable
4
Extract Customer data into a working table

• SELECT INTO CustomerCopy
• FROM Customer

5
Add Surrogate Keys

• Create a table with primary key and surrogate key
  attributes, transaction primary key as primary
  key and autoincrement for a new surrogate key

6
Create a Surrogate Key Table

• CREATE TABLE CustomerSurrogates
• (CustomerNumber TEXT(3)
• CONSTRAINT pk PRIMARY KEY,
• Surrogate AUTOINCREMENT,
• CustomerKey Integer)

7
Insert primary keys into the surrogate key table
and generate surrogate keys.

• INSERT INTO CustomerSurrogates (CustomerNumber)
• SELECT CustomerNumber FROM CustomerCopy
• WHERE CustomerNumber
• NOT IN (SELECT CustomerNumber FROM
  CustomerSurrogates)

8
Copy the autonumber field into an integer field.

• UPDATE CustomerSurrogates
• SET CustomerKey Surrogate
• WHERE CustomerKey IS Null

9
Add a CustomerKey column to the CustomerCopy table

• ALTER TABLE CustomerCopy ADD COLUMN CustomerKey
  NUMBER

10
Update the CustomerCopy table to add surrogate
keys.

• UPDATE CustomerCopy, CustomerSurrogates
• SET CustomerCopy.CustomerKey
  CustomerSurrogates.CustomerKey
• WHERE CustomerCopy.CustomerNumber
  CustomerSurrogates.CustomerNumber

11
Drop unnecessary columns

• ALTER TABLE CustomerCopy
• DROP balance