The Microsoft® Data Warehouse Toolkit: With SQL Server™ 2005 and the Microsoft® Business Intelligence Toolset

5.2. High-Level Planning

Now that you're familiar with the features and architecture of Integration Services, this chapter returns to the process of planning the ETL system. The end goal of this chapter is to describe how to write a detailed ETL specification. Before you dig into the details, it's best to start at a high level, by developing the first draft high-level map from the information that was gathered during the business process dimensional modeling effort.

After you pull your very high-level thoughts together into a high-level map, it's time to do detailed data profiling to learn about the characteristics of your data and what the transformation rules will need to be. These profiles will feed into the detailed source to target maps.

You will need to develop some system-wide approaches and rules, for example how to extract data and maintain conformed dimensions. For each fact table you'll need to decide how much history to load initially, and how much historical data to keep live in the DW/BI system.

A summary of the high-level planning process includes the following items:

• Develop a high-level map.

• If helpful, build a sandbox source system.

• Perform detailed data profiling and complete the source-to-target mapping.

• Determine how often you'll load each table.

• Determine how much historical data you'll load for each table.

• Develop a strategy for partitioning the relational and Analysis Services fact tables.

• Design a strategy for extracting data from each source system.

• If necessary, ...