Microsoft SQL Server 2012 Bible

Loading Data

When loading a data warehouse, it typically starts with loading the dimensions and then the facts. The obvious reason is to satisfy the fact tables' foreign key constraints. In the SQL Server world, the Extraction, Transformation and Loading (ETL) is accomplished using SQL Server Integration Services (SSIS). Chapter 52, “Building, Deploying, and Managing ETL Workflows in Integration Services,” provides a detailed explanation of how to do this. Figure 51.3 shows a simple dimension load with SSIS, and Figure 51.4 illustrates a fact load. This chapter focuses on loading the data with pure Transact-SQL.

Figure 51.3 Simple Dimension Load from SSIS.

Figure 51.4 Fact load using SSIS.

Loading Dimensions

The source data has a tremendous effect on which method will be used when loading the data into the data warehouse. If you encounter a situation in which all the fact and dimension data has relatable column, or more preferably foreign, keys, the process is straightforward. To load a dimension you would compare the rows in the dimension table to the rows that are contained in the incoming source data. As a best practice, you should copy the source data into staging tables instead of loading data directly from your source system. This should eliminate the possibility of contention ...