Caching and Optimization
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.
Start the MDX
initially greeted by the Connect dialog, shown in Illustration 1.
The Connect Dialog for the MDX Sample Application
illustration above depicts the name of my server, MOTHER1, and properly
indicates that we will be connecting via the MSOLAP provider (the
Sample Application window appears.
Clear the top
area (the Query pane) of any remnants of queries that might appear.
Ensure that FoodMart
2000 is selected as the database name in the DB box of the toolbar.
Select the Warehouse
cube in the Cube drop-down list box.
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).
Illustration 2: The MDX Sample Application Window