Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

5.1. The Load Process

The development of load process is a critical component of every implementation. It holds the most risk and accounts for the majority of person-hours allocated to the project. The load is constructed using a diverse set of tools collectively known as ETL tools. Regardless of the specific tools used, the major processes of any warehouse load can be defined, and each process can be functionally decomposed into smaller processes.

5.1.1. The Importance of the Load

During a data warehouse implementation, the lion's share of project resources is focused on the development of the load process. Logically, this should make sense: The dimensional model has been designed to simplify the reporting process. As you saw in Chapter 1, most data warehouse queries follow a simple pattern. The load process does the difficult work that makes these queries possible, transforming a transaction-oriented schema design into a dimensional design. By performing this work once, when the schema is loaded, it will not be necessary each time an analytic query is performed.

The load process is also the focus of a disproportionate share of project risk. The complexity of transforming the data from a transactional design to an analytic design is one factor. Additionally, it is during the load process that data from multiple systems will be brought together. The load process must deal with the varying definition of similar entities across different systems and produce a standard picture for ...