Using SQL Server Integration Services to Load Data - Microsoft SQL Server 2008 Analysis Services - page 403

CHAPTER 22

IN THIS CHAPTER

. Using SSIS

Using SQL Server
Integration Services to
Load Data

. Using Direct-Load ETL

D ata warehousing applications require complex proce-
dures for data transformation and analysis. The typical life
cycle of data in such applications comprises four stages:

1. An online transaction processing (OLTP) system
collects the data.
2. The data is loaded from the OLTP system into the
data warehouse through the process of extraction,
transformation, and loading (ETL).
3. The data is loaded from the data warehouse into a
multidimensional model that enables fast access for
analysis of the data.
4. The data is available to the end user through reports
built on top of the model or, for ad-hoc analysis,
through various analytical applications (Excel,
ProClarity, and so on).

Figure 22.1 shows the typical ETL process of moving data
from the OLTP system through the data warehouse to the
multidimensional model. It also shows another way of
directly loading data into Analysis Services: the ETL direct-
load process in which SQL Server Integration Services (SSIS)
pipes data from nonrelational data sources directly into the
multidimensional model. The ETL direct-load process is
helpful in the following situations:

. You have to load data from nonrelational data

sources, such as web logs, XML documents, flat files,
and so on.

. The data warehouse schema is complex and not well

suited to map to a multidimensional model.