Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 23, 2004

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

By William Pearson

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.

1.  Start the MDX Sample Application.

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

Illustration 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 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 (Compressed View)

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM