Data Warehouse Performance Tuning: Art or Science?

  Business Intelligence   Strategy  

You've spent months of research defining strategies for the optimal data model and physical database structure that will support high performance queries on your large data warehouse. You've gone through the design, executed to plan and are ready to finally launch. The anticipation for a successful launch is overwhelming. You unveil the warehouse to the users, wait for the first query to return, and wait...and wait...

Does that sound familiar? It's happening at data warehouses all across America. After months of planning, what happened? The truth is that you may have designed your warehouse with scientific precision, but a number of factors such as the data loading process, query patterns and unexpected data volume growth can lead to performance degradation. Performance tuning is an art that must be practiced constantly after the initial launch to achieve optimal warehouse performance.

The first key to assessing and improving your query performance is a keen understanding of the patterns of user queries. If a specific combination of criteria is used predominantly, the physical database structure should be designed to target optimal access to that criteria combination. One way to target a combination of criteria is through a composite index that will create an index based upon the access criteria. This type of index will store the values using a …

Please log in or sign up below to read the rest of the article.