Professional SQL Server® 2008 Integration Services

Chapter 10. Loading a Data Warehouse

Among the various applications of SQL Server Integration Services (SSIS), the most common is loading a data warehouse. SSIS provides the ETL features and functionality to efficiently handle many of the tasks required when dealing with transactional source data that will be extracted and loaded into a data mart, data warehouse, or even an operational data store (ODS), including the capabilities to then process data from the relational data warehouse into the SQL Server Analysis Services (SSAS) cubes.

Soup to nuts, SSIS provides the core foundation of data processing from your source, to staging, to your data mart, and onto your cubes (and beyond!). Figure 10-1 highlights a common architecture of a business intelligence (BI) solution.

Figure 10-1. Figure 10-1

The presentation layer on the right side of Figure 10-1 shows the main purpose of the BI solution, which is to provide business users (from the top to the bottom of an organization) meaningful data that they can take actionable steps from. Underlying the presentation data are the back-end structures and processes that make it possible for data to become meaningful and visible to the right people.

ETL is a large part of this back-end process because its responsibility is to move and restructure the data between the data tiers of the BI architecture. This involves many steps, as you will see, from ...