ETL Overview Extract, Transform, Load (ETL)

General ETL issues

ETL/DW refreshment process Building dimensions Building fact tables Extract Transformations/cleansing Load

MS Integration Services

Original slides were written by Torben Bach Pedersen

Aalborg University 2007 - DWML course

The ETL Process

The most underestimated process in DW development The most time-consuming process in DW development
80% of development time is spent on ETL!

Refreshment Workflow

Extract
Extract relevant data

Transform
Transform data to DW format Build keys, etc. Cleansing of data

Integration phase

Load
Load data into DW Build aggregates, etc.
Preparation phase
Aalborg University 2007 - DWML course 3 Aalborg University 2007 - DWML course 4

ETL In The Architecture

ETL side
Data sources - Extract - Transform - Load
Data Staging Area

Data Staging Area (DSA)

Transit storage for data in the ETL process
Transformations/cleansing done here

Query side
Metadata

-Warehouse Browsing -Access and Security Data marts with -Query Management aggregate-only data - Standard Reporting Conformed -Activity Monitor Data Warehouse dimensions Bus and facts
Data marts with atomic data

Presentation servers

Query Services

Reporting Tools Desktop Data Access Tools

No user queries Sequential operations on large data volumes

Performed by central ETL logic No need for locking, logging, etc. RDBMS or flat files? (DBMS have become better at this)

Data mining

Operational system

Finished dimensions copied from DSA to relevant marts Allows centralized backup/recovery
Often too time consuming to initial load all data marts by failure Backup/recovery facilities needed Better to do this centrally in DSA than in all data marts

Data

Service Element

Aalborg University 2007 - DWML course

Aalborg University 2007 - DWML course

ETL Construction Process

Plan
1) 2) 3)

Building Dimensions
Static dimension table
DW key assignment: production keys to DW keys using table Combination of data sources: find common key? Check one-one and one-many relationships using sorting

Make high-level diagram of source-destination flow Test, choose and implement ETL tool Outline complex transformations, key generation and job sequence for every destination table Construct and test building static dimension Construct and test change mechanisms for one dimension Construct and test remaining dimension builds Construct and test initial fact table build Construct and test incremental update Construct and test aggregate build (you do this later) Design, construct, and test ETL automation
7

Handling dimension changes

Described in last lecture Find the newest DW key for a given production key Table for mapping production keys to DW keys must be updated

Construction of dimensions
4) 5) 6)

Load of dimensions
Small dimensions: replace Large dimensions: load only changes

Construction of fact tables and automation

7) 8) 9) 10)

Aalborg University 2007 - DWML course

Aalborg University 2007 - DWML course

Building Fact Tables

Two types of load Initial load
ETL for all data up till now Done when DW is started the first time Very heavy - large data volumes

Types of Data Sources

Non-cooperative sources
Snapshot sources provides only full copy of source, e.g., files Specific sources each is different, e.g., legacy systems Logged sources writes change log, e.g., DB log Queryable sources provides query interface, e.g., RDBMS

Incremental update
Move only changes since last load Done periodically (e.g., month or week) after DW start Less heavy - smaller data volumes

Cooperative sources
Replicated sources publish/subscribe mechanism Call back sources calls external code (ETL) when changes occur Internal action sources only internal actions when changes occur
N

Dimensions must be updated before facts

The relevant dimension rows for new facts must be in place Special key considerations if initial load must be performed again

DB triggers is an example

Extract strategy depends on the source types

Aalborg University 2007 - DWML course

Aalborg University 2007 - DWML course

10

Extract
Goal: fast extract of relevant data
Extract from source systems can take long time

Computing Deltas
Delta = changes since last load Store sorted total extracts in DSA
Delta can easily be computed from current+last extract + Always possible + Handles deletions - High extraction time

Types of extracts:
Extract applications (SQL): co-existence with other applications DB unload tools: faster than SQL-based extracts

Extract applications the only solution in some scenarios Too time consuming to ETL all data at each load
Extraction can take days/weeks Drain on the operational systems Drain on DW systems => Extract/ETL only changes since last load (delta)

Put update timestamp on all rows (in sources)

Updated by DB trigger Extract only where timestamp > time for last extract + Reduces extract time - Cannot (alone) handle deletions - Source system must be changed, operational overhead

Aalborg University 2007 - DWML course

11

Aalborg University 2007 - DWML course

12

Changed Data Capture

Messages
Applications insert messages in a queue at updates + Works for all types of updates and systems - Operational applications must be changed+operational overhead

Common Transformations

Data type conversions

EBCDIC ASCII/UniCode String manipulations Date/time format conversions

DB triggers
Triggers execute actions on INSERT/UPDATE/DELETE + Operational applications need not be changed + Enables real-time update of DW - Operational overhead

Normalization/denormalization
To the desired DW format Depending on source format

Replication based on DB log

Find changes directly in DB log which is written anyway + Operational applications need not be changed + No operational overhead - Not possible in some DBMS
Aalborg University 2007 - DWML course 13

Building keys
Table matches production keys to surrogate DW keys Correct handling of history - especially for total reload

Aalborg University 2007 - DWML course

14

Data Quality
Data almost never has decent quality Data in DW must be:
Precise
N

Cleansing
BI does not work on raw data
Pre-processing necessary for BI analysis

DW data must match known numbers - or explanation needed DW has all relevant data and the users know No contradictory data: aggregates fit with detail data The same things is called the same and has the same key (customers) Data is updated frequently enough and the users know when

Handle inconsistent data formats

Spellings, codings,

Complete
N

Remove unnecessary attributes

Production keys, comments,

Consistent
N

Replace codes with text (Why?)

City name instead of ZIP code,

Unique
N

Combine data from multiple sources with common key

E.g., customer data from customer address, customer name,

Timely
N

Aalborg University 2007 - DWML course

15

Aalborg University 2007 - DWML course

16

Types Of Cleansing
Conversion and normalization
Text coding, date formats, etc. Most common type of cleansing

Cleansing
Mark facts with Data Status dimension
Normal, abnormal, outside bounds, impossible, Facts can be taken in/out of analyses

Special-purpose cleansing
Normalize spellings of names, addresses, etc. Remove duplicates, e.g., duplicate customers

Uniform treatment of NULL

Use explicit NULL value rather than special value (0,-1,) Use NULLs only for measure values (estimates instead?) Use special dimension keys for NULL dimension values
N

Domain-independent cleansing
Approximate, fuzzy joins on records from different sources

Rule-based cleansing
User-specifed rules, if-then style Automatic rules: use data mining to find patterns in data
N

Avoid problems in joins, since NULL is not equal to NULL

Mark facts with changed status

New customer, Customer about to cancel contract,

Guess missing sales person based on customer and item

Aalborg University 2007 - DWML course

17

Aalborg University 2007 - DWML course

18

Improving Data Quality

Appoint data quality administrator
Responsibility for data quality Includes manual inspections and corrections!

Load
Goal: fast loading into DW
Loading deltas is much faster than total load

SQL-based update is slow

Large overhead (optimization, locking, etc.) for every SQL call DB load tools are much faster

Source-controlled improvements
The optimal?

Construct programs that check data quality

Are totals as expected? Do results agree with alternative source? Number of NULL values?

Index on tables slows load a lot

Drop index and rebuild after load Can be done per index partition

Parallellization
Dimensions can be loaded concurrently Fact tables can be loaded concurrently Partitions can be loaded concurrently

Do not fix all problems with data quality

Allow management to see weird data in their reports? Such data may be meaningful for them? (e.g., fraud detection)

Aalborg University 2007 - DWML course

19

Aalborg University 2007 - DWML course

20

Load
Relationships in the data
Referential integrity and data consistency must be ensured (Why?) Can be done by loader

ETL Tools
ETL tools from the big vendors
Oracle Warehouse Builder IBM DB2 Warehouse Manager Microsoft Integration Services

Aggregates
Can be built and loaded at the same time as the detail data

Offers much functionality at a reasonable price

Data modeling ETL code generation Scheduling DW jobs

Load tuning
Load without log Sort load file first Make only simple transformations in loader Use loader facilities for building aggregates

The best tool does not exist

Choose based on your own needs Check first if the standard tools from the big vendors are OK

Should DW be on-line 24*7?

Use partitions or several sets of tables (like MS Analysis)

Aalborg University 2007 - DWML course

21

Aalborg University 2007 - DWML course

22

Issues
Pipes
Redirect output from one process to input of another process ls | grep 'a' | sort -r

MS Integration Services

Files versus streams/pipes

Streams/pipes: no disk overhead, fast throughput Files: easier restart, often only possibility

ETL tool or not

Code: easy start, co-existence with IT infrastructure Tool: better productivity on subsequent projects

A concrete ETL tool Example ETL flow Demo

Load frequency
ETL time dependent of data volumes Daily load is much faster than monthly Applies to all steps in the ETL process

Aalborg University 2007 - DWML course

23

Aalborg University 2007 - DWML course

24

Integration Services (IS)

Microsofts ETL tool
Part of SQL Server 2005

Packages

Tools
Import/export wizard - simple transformations BI Development Studio advanced development

The central concept in IS Package for:

Sources, Connections Control flow Tasks, Workflows Transformations Destinations

Functionality available in several ways

Through GUI - basic functionality Programming - advanced functionality

Aalborg University 2007 - DWML course

25

Aalborg University 2007 - DWML course

26

Package Control Flow

Containers provide
Structure to packages Services to tasks

Tasks
Data Flow runs data flows Data Preparation Tasks
File System operations on files FTP up/down-load data

Control flow
Foreach loop container
N

Workflow Tasks
Execute package execute other IS packages, good for structure! Execute Process run external application/batch file

Repeat tasks by using an enumerator Repeat tasks by testing a condition Groups tasks and containers into control flows that are subsets of the package control flow

For loop container

N

SQL Servers Tasks

Bulk insert fast load of data Execute SQL execute any SQL query

Sequence container
N

Scripting Tasks
Script execute VN .NET code

Task host container

Provides services to a single task
Arrows: green (success) red (failure)

Analysis Services Tasks

Analysis Services Processing process dims, cubes, models Analysis Services Execute DDL create/drop/alter cubes, models

Maintenance Tasks DB maintenance

27 Aalborg University 2007 - DWML course 28

Aalborg University 2007 - DWML course

Event Handlers

Data Flow Elements

Sources
Makes external data available All ODBC/OLE DB data sources: RDBMS, Excel, Text files,

Executables (packages, containers) can raise events Event handlers manage the events Similar to those in languages JAVA, C#

Transformations
Update Summarize Cleanse Merge Distribute

Destinations
Write data to specific store Create in-memory data set

Input, Output, Error output

Aalborg University 2007 - DWML course

29

Aalborg University 2007 - DWML course

30

Transformations
Business intelligence transformations
Term Extraction - extract terms from text Term Lookup look up terms and find term counts

A Simple IS Case
Use BI Dev Studio/Import Wizard to copy TREO tables Save in
SQL Server File system

Row Transformations
Character Map - applies string functions to character data Derived Column populates columns using expressions

Rowset Transformations (rowset = tabular data)

Aggregate - performs aggregations Sort - sorts data Percentage Sampling - creates sample data set by setting %

Look at package structure

Available from mini-project web page

Look at package parts

DROP, CREATE, source, transformation, destination

Split and Join Transformations

Conditional Split - routes data rows to different outputs Merge - merges two sorted data sets Lookup Transformation - looks up ref values by exact match

Execute package
Error messages?

Other Transformations
Export Column - inserts data from a data flow into a file Import Column - reads data from a file and adds it to a data flow Slowly Changing Dimension - configures update of a SCD
Aalborg University 2007 - DWML course 31

Steps execute in parallel

But dependencies can be set up

Aalborg University 2007 - DWML course

32

ETL Demo

Load data into the Product dimension table

Construct the DW key for the table by using IDENTITY Copy data to the Product dimension table

Load data into the Sales fact table

Join raw sales table with other tables to get DW keys for each sales record Output of the query written into the fact table

Aalborg University 2007 - DWML course

33

Aalborg University 2007 - DWML course

34

ETL Part of Mini Project

Core:
Build an ETL flow using MS DTS that can do an initial (first-time) load of the data warehouse Include logic for generating special DW surrogate integer keys for the tables Discuss and implement basic transformations/data cleansing

A Few Hints on ETL Design

Dont implement all transformations in one step!
Build first step and check that result is as expected Add second step and execute both, check result Add third step

Extensions:
Extend the ETL flow to handle incremental loads, i.e., updates to the DW, both for dimensions and facts Extend the DW design and the ETL logic to handle slowly changing dimensions of Type 2 Implement more advanced transformations/data cleansing Perform error handling in the ETL flow

Test SQL before putting into IS Do one thing at the time

Copy source data one-one to DSA Compute deltas
N

Only if doing incremental load Versions only if handling slowly changing dimensions

Handle versions and DW keys

N

Implement complex transformations Load dimensions Load facts

35 Aalborg University 2007 - DWML course 36

Aalborg University 2007 - DWML course

Summary
General ETL issues
The ETL process Building dimensions Building fact tables Extract Transformations/cleansing Load

MS Integration Services

Aalborg University 2007 - DWML course

37