Module 05: Implementing a Data Extraction Solution

In other cases, dimension members must be found by matching a unique combination of multiple columns.

Updating Dimension Members

When refreshing dimension tables, you must consider whether changes to individual dimension attributes will

have a material effect on historical reporting and analysis. Dimension attributes can be categorized as one of

three kinds :

• Fixed – the attribute value cannot be changed. For example, you might enforce a rule that prevents

changes to a product name after it has been loaded into the dimension table.

2

• Changing – the attributes value can change without affecting historical reporting and analytics. For

example, a customer’s telephone number might change, but it is unlikely that any historical business

reporting or analytics will aggregate measures by telephone number. The change can therefore be

made without the need to retain the previous telephone number.

• Historical – the attribute value can change, but the previous value must be retained for historical

reporting and analysis. For example, a customer might move from Edinburgh to New York, but

reports and analysis must associate all sales that occurred to that customer before the move with

Edinburgh, and all sales after the move with New York.

Updating Fact Records STUDENT

When refreshing the data warehouse, you must consider whether you will allow updates to fact records.

Often, your data warehouse design will only contain complete fact records, so no incomplete records will be

loaded. However in some cases, you might want to include an incomplete fact record in the data warehouse

that will be updated during a later refresh cycle.

For example, you might choose to include a fact record for a sales order where the sale has been completed,

but the item has not yet been delivered. If the record includes a column for the delivery date, you might

initially store a null value in this column, and then update the record during a later refresh after the order has

been delivered.

While some data warehousing professionals allow updates to the existing record in the fact table, other

practitioners prefer to support changes by deleting the existing fact record and inserting a new one. In most

cases, the delete operation is actually a logical deletion that is achieved by setting a bit value on a column

that indicates whether the record is active or not, rather than actually deleting the record from the table.

Common ETL Data Flow Architectures

Fundamentally, ETL is concerned with data flow from source systems to the data warehouse. The data flow

process can be performed directly from source to target, or in stages. Factors that affect the choice of data

flow architecture include:

•

•

•

•

•

The number of data sources.

The volume of data to be transferred.

The complexity of validation and transformation operations to be applied to the data.

How frequently data is generated in source systems, and how long it is retained.

Suitable times to extract source data while minimizing the impact on performance for users.

3

Single-stage ETL

In a very small business intelligence (BI) solution with few data sources and simple requirements, it may be

possible to copy data from data sources to the data warehouse in a single data flow. Basic data validations

(such as checking for NULL fields or specific value ranges) and transformations (such as concatenating

multiple fields into a single field, or looking up a value from a key) can either be performed during extraction

(for example, in the Transact-SQL statement used to retrieve data from a source database) or in-flight (for

example, by using transformation components in an SSIS data flow task).

Two-stage ETL

In many cases, a single-stage ETL solution is not suitable because of the complexity or volume of data being

transferred. Additionally, if multiple data sources are used, it is common to synchronize loads into the data

warehouse to ensure consistency and integrity across fact and dimension data from different sources, and to

minimize the performance impact of the load operations on data warehouse activity. If the data is not ready to

extract from all systems at the same time, or if some sources are only available at specific times when others

are not available, a common approach is to stage the data in an interim location before loading into the data

warehouse.

Typically, the structure of the data in the staging area is similar to the source tables, which minimizes the

extraction query complexity and duration in the source systems. When all source data is staged, it can then

be conformed to the data warehouse schema during the load operation, either as it is extracted from the

staging tables or during the data flow to the data warehouse.

Staging the data also provides a recovery point for data load failures and enables you to retain extracted data

for audit and verification purposes.

Three-stage ETL

A two-stage data flow architecture can reduce the extraction overhead and source systems, enabling a

coordinated load of data from multiple sources. However, performing validation and transformations during

the data flow into the data warehouse can affect load performance, and cause the load to negatively affect

data warehouse activity. When large volumes of data must be loaded into the data warehouse, it is important

to minimize load times by preparing the data as much as possible before performing the operation.

For BI solutions that involve loading large volumes of data, a three-stage ETL process is recommended. In

this data flow architecture, the data is initially extracted to tables that closely match the source system

4

schemas—often referred to as a “landing zone.” From here, the data is validated and transformed as it is

loaded into staging tables that more closely resemble the target data warehouse tables. Finally, the

conformed and validated data can be loaded into the data warehouse tables.

Planning Extraction Windows

To help you determine when to perform the data extraction process, consider the following questions :

How frequently is new data generated in the source systems, and for how long is it

retained?

Some business applications generate only a few transactions per day, and store the details permanently.

Others generate transient feeds of data that must be captured in real time. The volume of changes and

storage interval of the source data will determine the frequency of extraction required to support the business

requirements.

What latency between changes in source systems and reporting is tolerable?

Another factor in planning data extraction timings is the requirement for the data warehouse to be kept up to

date with changes in the source systems. If real-time (or near real-time) reporting must be supported, data

must be extracted and loaded into the data warehouse as soon as possible after each change. Alternatively,

if all reporting and analysis is historical, you may be able to leave a significant period of time (for example, a

month) between data warehouse loads. However, note that you do not need to match data extractions one-

to-one with data loads. If less overhead is created in the data source by a nightly extraction of the day’s

changes than a monthly extraction, you might choose to stage the data nightly, and then load it into the data

warehouse in one operation at the end of the month.

How long does data extraction take?

Perform a test extraction and note the time taken to extract a specific number of rows. Then, based on how

many new and modified rows are created in a particular period, estimate the time an extraction would take if

performed hourly, daily, weekly, or at any other interval that makes sense, based on your answers to the first

two questions.

During what time periods are source systems least heavily used?

Some data sources may be available only during specific periods, and others might be too heavily used

during business hours to support the additional overhead of an extraction process. You must work closely

with the administrators and users of the data sources to identify the ideal data extraction time periods for

each source.

5

After you consider these questions for all source systems, you can start to plan extraction windows for the

data. Note that it is common to have multiple sources with different extraction windows, so that the elapsed

time to stage all the data might be several hours or even days.

Planning Transformations

When planning an ETL solution, you must consider the transformations that need to be applied to the data to

validate it and make it conform to the target table schemas.

Where to Perform Transformations

One consideration for transformations is where they should be applied during the ETL process.

Performing transformations on extraction

If the data sources support it, you can perform transformations in the queries used to extract data. For

example, in an SQL Server data source, you can use joins, ISNULL expressions, CAST and CONVERT

expressions, and concatenation expressions in the SELECT query used to extract the data. In an enterprise

BI solution, this technique can be used during the following extractions :

• Extraction from the source system.

• Extraction from the landing zone.

• Extraction from the staging area.

Performing transformations in the data flow

You can use SSIS data flow transformations to transform data during the data flow. For example, you can

use lookups, derived column transformations, and custom scripts to validate and modify rows in a data flow.

You can also use merge and split transformations to combine or create multiple data flow paths. In an

enterprise BI solution, this technique can be used during the following data flows :

• Source to landing zone.

Landing zone to staging.

•

• Staging to data warehouse.

Performing transformations in-place

6

In some cases, it might make sense to transfer data from sources into one or more database tables, and then

perform UPDATE operations to modify the data in-place before the next phase of the ETL data flow.

For example, you might extract data from one source and stage it, and then update coded values based on

data from another source that is extracted during a later extraction window. In an enterprise BI solution, this

technique can be used in the following locations :

Landing zone tables.

•

• Staging tables.

Guidelines for choosing where to perform transformations

Although there is no single correct place in the data flow to perform transformations, consider the following

guidelines for designing your solutions :

• Minimize the extraction workload on source systems. This enables you to extract the data in the

shortest time possible with minimal adverse effect on business processes and applications using the

data source.

• Perform validations and transformations in the data flow as soon as possible. This enables you to

remove or redirect invalid rows and unnecessary columns early in the extraction process, reducing

the amount of data being transferred across the network.

• Minimize the time it takes to load the data warehouse tables. This enables you to get the new data

into production as soon as possible and perform the load with minimal adverse effect on data

warehouse users.

How to Perform Transformations

You can use Transact-SQL statements to transform or validate columns during extraction or in-place.

Alternatively, you can use SSIS data flow transformations to modify the data during the data flow. The

following table lists some typical validation and transformation scenarios, together with information about how

to use Transact-SQL or data flow transformations to implement a solution.

Scenario

Transact-SQL

Data flow transformations

Data type conversion

Use the CAST or CONVERT

Use the Data Conversion

Concatenation

function.

Concatenate fields in the

SELECT clause of the query.

Replacing NULL values

Use the ISNULL function.

transformation.

Use the Derived Column

transformation.

Use the Derived Column

transformation with an

expression containing the

ReplaceNull function.

.

O

N

L

Y

STUDENT

Looking up related values

where referential integrity is

enforced

Use an INNER JOIN.

Use the Lookup transformation.

Looking up related values

Use an OUTER JOIN, and

Use the Lookup transformation

enforced

replace null values where no

matching rows exist.

and then add a transformation U

later in the data flow to handle

SE

null values (either by replacing

them with a Derived Column

or redirecting them with a

7

I

H

B

I

Condition Split).

Alternatively, use the Redirect

rows to no match output

option and handle the nulls

before using a Merge

transformation to return the

fixed rows to the main data

flow.

T

E

D

Note: Some people who are unfamiliar with SSIS make the erroneous assumption that the data flow

processes rows sequentially, and that data flow transformations are inherently slower than set-based

transformations performed with Transact-SQL. However, the SSIS pipeline performs set-based operations on

buffered batches of rows, and is designed to provide highperformance transformation in data flows.

Documenting Data Flows

An important part of designing an ETL solution is to document the data flows you need to implement. The

diagrams and notes that document your data flows are commonly referred to as “source-to-target”

documentation, that typically starts with a simple high-level diagram for each table in the data warehouse.

The diagram shows the source tables from which the data warehouse table fields originate and the validation

and transformations that must be applied during the data flow.

As a general rule, use a consistent diagramming approach for each table, and include as much detail about

validation rules, transformations, and potential issues as you can. It is common for these high-level diagrams

to start simple and be refined as the ETL design evolves.

As your ETL design is refined, you will start to develop a clear idea of what fields will be extracted, generated,

and validated at each stage of the data flow. To help document the lineage of the data as it flows from the

source to the data warehouse tables, you can create detailed source-to-target mappings that show detailed

information for the fields at each stage.

A common way to create a source-to-target mapping is to use a spreadsheet divided into a set of columns for

each stage in the data flow. Start with the fields in the target table, and then work backward to determine the

required staging, landing zone, and source fields, along with any validation rules and transformations that

must be applied. The goal is to create a single document in which the origins of a field in the target table can

be traced back across a row to its source.

P

R

O

8

Like high-level data flow diagrams, many BI professionals have adopted different variations of source-totarget

mapping. The organization in which you are working may not have a standard format for this kind of

documentation. It’s important, therefore, to use a consistent format that is helpful during ETL design and easy

to understand for anyone who needs to troubleshoot or maintain the ETL system in the future.

9