MDX in Analysis Services: Optimizing MDX: Caching and Other Considerations - Page 2

Caching and Optimization

When queries are not well underpinned by aggregates, 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 for 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 will call our old friend, the MDX Sample Application, as a platform from which to perform our practice exercises.

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

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

The MDX Sample Application window appears.

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