Professional Microsoft SQL Server 2012 Analysis Services with MDX and DAX

Chapter 11

Optimizing Query Performance

What's in this chapter?

• The calculation model
• Query execution architecture
• Performance analysis and tuning tools
• Analyzing and solving query performance issues
• Query optimization techniques
• Scale out with read-only databases
• Writeback query performance

The power of Analysis Services lies in its capability 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 (www.olapreport.com/fasmi.htm). The word “fast” in this context means that the system can deliver results to users in less than 5 seconds (with a few highly complex queries taking more than 20 seconds). You can also expect that most business decision makers 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 to analyze and make decisions.

Queries sent to Analysis Services need to return data within seconds and query performance is pivotal to a successful Business Intelligence (BI) project deployment. A system that has good performance can 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.

How OLAP Enhances Performance

Analysis ...