Optimizing Microsoft SQL Server Analysis Services: MDX Optimization Techniques: Caching and Additional Techniques

By : William E. Pearson, III Apr 18, 2004

Printer friendly

Introduction and Scope

In this article, we will continue our examination of MDX optimization techniques, begun in the article prior to the last, MDX Optimization Techniques: Introduction and the Role of Processing, and continued in the last article, MDX Optimization Techniques: Further Control of Processing Location and Expression Arrangement. In both articles, we focused upon control of location as a primary type of tuning interaction for MDX query optimization. We performed practice exercises to reinforce the concepts exposed, and then extended our considerations of additional tuning interaction types to include the optimization of set operations and syntax arrangement considerations. Within our exploration of the optimization of set operations, we undertook practice examples that illustrated some ways (additional tuning interaction types) whereby we can rearrange queries to enhance performance, often significantly.

In this article, we will expose methods of caching to load a commonly used slice of a cube into memory, for faster retrieval in prospective operations. Our discussion of caching as another tuning interaction type will include various aspects of cache creation, and uses of caching within MDX. In addition, we will touch upon other performance enhancement options, including external functions and cube design modifications and augmentation.

Optimizing Performance Through Caching

When queries are not well underpinned by aggregates, processing suffers. Extra effort has to be expended to compensate for the missing accumulations. We can often enhance performance by creating and caching the appropriate aggregates in memory. Caching is a feature that MDX provides to improve performance; caching affords us the capability of loading a commonly used slice of a cube into memory, “caching” it for faster retrieval by our queries.

Analysis Services and the PivotTable Service automatically cache query definitions, data, and meta data on the server and client sides, respectively. Caching increases performance in those cases where queries are repeatedly requesting the same data or meta data, reducing network traffic or execution time. The ability to create caches for data that we specify in MDX gives us another means of fine-tuning query performance; through this capability we realize a great degree of control over the caching of data for which we expect there to be a recurring need.
 

In terms of creation scope, caches are analogous to named sets: we can create a cache for the lifetime of a single query, or limit its life to a session. To create a cache to be used at the session level, the CREATE CACHE statement can be used. The CREATE CACHE statement can be used to create caches at the query level, but the WITH statement, with which we are now somewhat familiar, can perform this task just as easily, and is more frequently used for this purpose.
 

Let’s take a look at the use of the WITH statement to create a cache in an MDX query. First, we’ll call the MDX Sample Application, with which we have become familiar in previous articles, as a platform from which to perform our practice exercises.
 

1. Start the MDX Sample Application.
 

We are initially greeted by the Connect dialog, shown in Figure 1.
 

Figure 1: The Connect Dialog for the MDX Sample Application
 

The illustration above depicts the name of my server, MOTHER1, and properly indicates that we will be connecting via the MSOLAP provider (the default).
 

2. Click OK.
 

The MDX Sample Application window appears.
 

3. Clear the top area (the Query pane) of any remnants of queries that might appear.
 

4. Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.
 

5. Select the Warehouse cube in the Cube drop-down list box.
 

The MDX Sample Application window should resemble that depicted in Figure 2, complete with the information from the Warehouse cube displaying in the Metadata tree (left section of the Metadata pane).
 

Figure 2: The MDX Sample Application Window (Compressed View)