Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

Chapter 15. Analyzing and Optimizing Query Performance

The power of Analysis Services lies in its ability to provide fast query response time for decision makers who need to analyze data, draw conclusions, and make appropriate changes in their business. The OLAP Report defines OLAP as Fast Analysis of Shared Multidimensional Information (http://www.olapreport.com/fasmi.htm). The word "fast" in this context means that the system is able to deliver results to users in less than 5 seconds (with a few highly complex queries taking more than 20 seconds). We also expect that most business decision makers will use client tools that graphically represent the data from Analysis Services for easy interpretation and understanding. As an end user, you expect to see the data quickly in order to analyze and make decisions. Some common operations that OLAP client tools offer are drill down, drill up, and compare data year over year. Users do not have time to wait for hours to get a response. Hence, queries sent to Analysis Services need to return data within seconds, at most in minutes. Query performance is pivotal to a successful Business Intelligence project deployment. A system that has very good performance will bring great business value to your company. However, you should be aware there can be queries to Analysis Services that can take more than a few minutes. Typically such queries are issued via overnight reporting systems.

Analysis Services supports three storage modes: MOLAP, ROLAP, ...