Building Caches in MDX
Another feature Multidimensional Expressions (MDX) provides to improve performance is the ability to load a commonly used slice of a cube into memory, caching it for faster retrieval.
Microsoft® SQL Server™ 2000 Analysis Services and PivotTable® Service automatically cache query definitions, data, and meta data on the server and client sides, respectively. This 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 specific data in MDX gives you complete control over the caching of data to be used repeatedly, allowing fine-tuning of query performance.
In terms of creation scope, caches are similar to named sets in that a cache may be created for the lifetime of a single query or 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 can perform this task just as easily.
For example, the following MDX query uses the WITH statement to cache:
WITH CACHE AS '(Store.[Store Name].Members)'
SELECT
{[Store].[Store Name].Members} ON COLUMNS,
{[Measures].[Unit Sales]} ON ROWS
FROM Sales
While the WITH statement can be used to create a cache for a single query, the CREATE CACHE statement can be used to create caches at the session level, as well. The CREATE CACHE statement requires PivotTable Service in order to employ a session level cache.
For more information about the CREATE CACHE statement, see CREATE CACHE Statement.