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 3

By William Pearson

Let's look first at creating a cache with a query scope. To do so, we will take the following steps:

1.  Create the following new query:

-- MXAS12-1:  WITH CACHE Query
 '([Product].[Product Department].Members)'
   {[Measures].[Warehouse Sales]} ON COLUMNS,
   {[Product].[Product Department].Members} 
FROM Warehouse

2.  Execute the query using the Run Query button.

The results dataset appears as shown in Illustration 3.

Illustration 3: WITH CACHE Query

3.  Save the query as MXAS12-1.

Keep in mind that the "life" of the WITH CACHE statement is only as long as the query in which it resides. Use of the WITH CACHE statement can sometimes result in more rapid completion of the overall query, because the full set of cells that we have specified in the statement arrives at the client before the multidimensional data set is returned. In scenarios where the server is accessed via a high speed LAN by the application generating the query, the small performance enhancement may be negligible. By contrast, scenarios where access is over WAN links or modem connections, query results may be retarded in first making an appearance, but will likely require less time to retrieve overall.

Now, let's take a look at the use of the CREATE statement to create a cache in an MDX query. We will create a cache with session scope, as we have already created a cache with query scope above.

4.  Create the following new query:

-- MXAS12-2:  CREATE CACHE Query
  Descendants (

5.  Execute the query using the Run Query button.

6.  Save the query as MXAS12-2.

The query creates a cache with session scope; we notice that no measures are specified this time. This is because all the cube's base measures are loaded into the cache at runtime. The CREATE statement above does not take noticeably more time to execute than would the core query; immediate execution of the query will occur subsequent to cache creation, however, because the query would process in its completeness from RAM, where the cache is housed.

Before we decide to use caching, we need give thought to whether the query we are attempting to improve through caching will actually benefit from means other than redesign of the query itself, and whether the caching process can realistically provide improved performance in general. Obviously, a one-time query is not likely to benefit from caching. In addition, numerous situations will exist where, although a large population of cells are specified in a query, only a few cells are actually accessed. To use caching, for example, in a scenario where only specific tuples out of a large CrossJoin are actually used, may mean more processing time to cache the population of cells than will be saved in performance gains for the few cells actually used. The cache statements are best left to scenarios where their effects are likely to increase performance and where the query actually has a need for tuning from the outset.

7.  Close the Sample Application.

Other Performance Enhancement Options

External functions can often offer processing optimizations over their calculated equivalents, particularly when the calculated members are complex. External functions can be used in a query via the presence of the associated calls to those functions, if the associated function library is installed in the appropriate place(s) on the client or server. Queries making use of these functions can be resolved on either the client or the server if the function resides on either tier, and, as long as the external query is not used in making the determination of the axes within the query, then the query can be largely resolved on the server, even when the function does not reside on the server. Keep in mind, however, that the presence of the function solely on the client means forced client-based processing regardless of other factors.

Finally, cube design modification and / or augmentation can provide significant efficiency in cube processing, even though this might often rest outside the control of the query designer. Such improvements as placing member properties into measures (especially numerical data) allow for free and efficient use of these fields at multiple levels. And the additional processing that can be encountered by making this measure available as a calculated member can be mitigated significantly by placing the measure in a "custom" cube (one-dimensional cubes work fine, if adequate to meet the need), which can then be combined with other cubes - cubes that need the new measure - in virtual cubes. This concept can be extended into many other areas, and provides an excellent way to leverage the existing cube structure by "adding on" needed components.

Summary and Conclusion ...

In this, the concluding lesson of our Optimizing MDX mini-series, we extended our toolset by adding yet another intervention type for optimizing our MDX queries. We exposed two methods of caching to load a commonly used slice of a cube into memory, making for faster retrieval in prospective operations. We discussed the creation scope for caches, within the context of both the CREATE CACHE and the WITH CACHE statements, and discussed appropriate uses for each. Finally, we touched upon other performance enhancement options, including external functions and cube design modifications and augmentation.

While this brings our Optimizing MDX mini-series to a close for the present, we will likely add new segments to the set from time to time, on an ad hoc basis. Optimization is obviously an area of great interest to developers and consumers alike, and it is a rare week indeed when yet another nuance for more efficient MDX does not present itself at client sites and / or in my lab. Stay tuned for more tips in the months to come!

» See All Articles by Columnist William E. Pearson, III

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