Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

15.5. Query Optimization Techniques

As you learned earlier in this chapter, MDX query execution time can be impacted by several factors such as cube design, Analysis Services caching, and hardware. One of the important factors in getting the best MDX query execution time is the efficiency of your MDX. Using the right MDX query optimization technique is not simple and involves a deeper understanding of your cube and MDX. In this section you learn some of the important techniques that can help you optimize your MDX queries.

15.5.1. Using NON EMPTY on Axes

Most cubes are quite sparse. By sparse we mean that many of the cells in the cube space do not have a value associated with them. For example, in the Adventure Works DW 2008 sample Analysis Services database, if every coordinate of the Internet Sales measure group has data and assuming only the key attribute in each dimension, the total number of cells with data would be (Date) 1189 * Date (Ship Date) 1189 * Date (Delivery Date) 1189 * Customer (18485) * Promotion (17) * Product (398) * Sales Territory (12) * Sales Reason (11) * Source Currency (106) * Destination Currency (15) * Internet Sales Order Details (60,399), which is 2.66*10²⁷ cells. This result increases when additional attributes are added from each dimension. Although most of the cells do not have any business meaning associated with them — for example, if delivery date is ahead of order date — they belong to cube space and can be queried by the users. Querying such ...