Chapter 13: Populating Data Warehouse Structures

 Download CD Content

Overview

After completing this chapter, you will be able to:

• Understand the role of SSIS in maintaining data warehouse models.

• Know when and how to implement staging tables.

• Use SSIS for loading dimension tables.

• Use SSIS for loading fact tables.

Microsoft SQL Server 2005 Integration Services (SSIS) provides a rich set of built-in tasks, containers, transformations, and data adapters that support the development of business applications. Without writing a single line of code, you can create SSIS solutions that solve complex business problems, using extract, transform, and load (ETL) and business intelligence (BI) processes to manage SQL Server databases.

You learned in Chapter 12, "Data Warehouse Concepts," how a data warehouse is often the core component of a BI infrastructure for today's corporations. This book will not get into the details of building and maintaining data warehouses, but it's important to look at some of the characteristics and design considerations that are crucial to BI infrastructures and solutions. Data warehouses are traditionally implemented as relational database models of various design types.

SSIS is used to perform data cleansing, standardization, derivation, and many other transformation tasks. It is not unusual for data warehouses to have more data than the source systems that feed them. But even though the data volume might be extremely large, the database model is designed for fast access for reporting.

Data warehouses, if designed and maintained appropriately, provide the most accurate and reliable data for effective reporting and BI solution support. You will now review data warehouse database models and look at some of the ways SSIS is used to build and maintain data warehouses.