Seven steps to optimizing data warehouse performance

Abstract

Most operational systems store data in a normalized model in which certain rules eliminate redundancy and simplify data relationships. While beneficial for the online transaction processing workload, this model can inhibit those same OLTP databases from running analytical queries effectively. Because the analytical systems did not need to support the OLTP workload, many developers began preplanning for the answer sets. Preplanning, however, created problems in four areas: creating summary tables of preaggregated data, placing indexes in the system to eliminate scanning large data volumes, putting data into one table instead of having tables that join together, and storing the data in sorted order. All these activities require prior knowledge of the analysis and reports being requested. Unfortunately, most data warehouse implementations ignore the longer-term goals of analysis and flexibility in the rush to provide initial value. Taking time to consider the project's real purpose, then building a correct foundation for it, can assure a better future for the data warehouse. To meet user demands for more timely and flexible analysis, companies can use a step-by-step approach to move from maintaining detailed information to using summary-level data.