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

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

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

The results dataset appears as shown in Illustration 3.

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.

-- MXAS12-2:  CREATE CACHE Query
CREATE SESSION CACHE FOR Warehouse AS 
'(
  Descendants (
      [Time].[1998],
     [Time].[Month]
),
[Warehouse].[Country].Members
)'

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.

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