Design of the data warehouse for staging, the lowest level of granularity (history), and the data marts has to be tuned based on how these levels are used. Let’s evaluate some characteristics about each level.

STAGING – Staging areas are used as a place to land data for propagation and integration into the data warehouse. Sometimes we just land the data and integrate on the way to the data warehouse, and sometimes we integrate within the staging area. My preference is to integrate and check the data quality on the way to the data warehouse. Tuning in this area requires us to understand the ETL integration process and index accordingly. We can also consider creating indexes JUST FOR A PROCESS and then drop them. Some staging areas have persistent data.

DATA WAREHOUSE – The data warehouse has integrated data, modeled as close to third normal form as efficiency will allow. History is prevalent in the data warehouse. Tuning in the data warehouse requires us to build and drop indexes for propagating information to the data warehouse.

DATA MARTS – I like to create low-level detail star schemas and highly summarized tables to be used with dashboards. This would require the dashboard technology to allow us to drill into another table for the detail. Tuning in the data mart requires us to index based on how the data is queried. With so many indexes in the data marts area, truncating and reloading data is difficult for most database management systems.

I create highly summarized tables when the requirements dictate fast response.