Do we tune for load performance or query performance? That is always the question in our data warehouse environment, which leads us to the standard practice of creating an atomic level of history data that is not presented to our business users in our data warehousing environments.

This layer would be used for history, and can be designed and tuned for load performance. One of the big problems I see customers do in the atomic level is create too much normalization of the data. So I like to start with third normal form modeling for the atomic layer of the data warehouse and then back off based on how the data is presented from the source systems or staging areas. Load performance is key to minimizing the saturation of your ETL environment.

The presentation layer of the data warehouse is the data that should be presented to the business user for reporting and analysis. For some reports we may only want the current view of the data, while other analysis may require an abundance of history. ETL from the atomic level of the data warehouse to the presentation layer should be fast and easy to maintain. In subject areas such as product the dimensions can have a tremendous amount of denormalization and complexity. Tuning the presentation layer requires us to understand indexing and how a query would use (or not use) an index. Tuning for a reporting tool is complex and definitely an art. It requires writing queries for the business user who uses these indexes. Some companies allow business users to write their own reports, but should consider some index education for them.

With the separation of the layers in the data warehouse (atomic vs. presentation) we should be able to better tune for load or query.