Datasheet

This book focuses on the three most common categories of SSIS usage:

❑ Data warehouse ETL

❑ Data integration

❑ SSIS administration

Before going any further, it makes sense to consider the purpose and background of each of these types

of ETL.

Data Warehousing ETL

Some of you may be well-versed in data warehousing and related ETL concepts, but for those who are

not, here is a high-level overview of data warehousing. Data warehousing focuses on decision support, or

enabling better decision making through organized accessibility of information. As opposed to a transac-

tional system such as a point of sale (POS), Human Resources (HR), or customer relationship manage-

ment (CRM) that is designed to allow rapid transactions to capture information data, a data warehouse

is tuned for reporting and analysis. In other words, instead of focusing on the entry of information, data

warehousing is focused on the extraction and reporting of information to show trending, summary, and

data history.

Databases designed for data warehousing are created in a structure called a dimensional model, which

involves two types of tables. Dimension tables hold informational data or attributes that describe entities.

Fact tables capture metrics or numeric data that describe quantities, levels, sales, or other statistics. A data

warehouse may involve many dimension tables and fact tables. Figure 1-2 shows the relationships

between several dimension tables and one fact table in a structure often called a star schema.

The focus of this book is not on the design of the dimension tables and fact tables, but rather on getting

data into these structures from other repositories. Processing ETL for data warehousing involves extracting

data from source systems or files, performing transformation logic on the data to correlate, cleanse, and

consolidate, and then loading a data warehouse environment for reporting and analysis (see Figure 1-3).

Chapter 1: Getting Started

Be Careful About Tool Selection

In some client environments, an ETL tool may be chosen without consideration for the

availability of industry skills, support, or even the learning curve. Even though the tool

could perform “magic,” it usually doesn’t come with a pocket magician, just the magic

of emptying your corporate wallet. In many cases, thousands of dollars have been

spent to purchase an ETL tool that takes too long to master, implement, and support.

Beyond the standard functionality questions you should ask about a tool, be sure to

also consider the following:

❑ Your internal skill sets

❑ The trend of industry use of the tool

❑ How easy it is to learn

❑ The ease of supporting the tool

04_134115 ch01.qxp 4/24/07 6:40 PM Page 4