Optimizing Microsoft SQL Server Analysis Services: MDX Optimization Techniques: Caching and Additional Techniques

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 through the respective calls to those functions, provided that 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 function is not used in fixing the axes within the query, then the query can be largely evaluated 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. (I do this with the Time dimension(s), for example, quite often.) 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
 

In this article, we extended our toolset by adding yet another tuning interaction 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.
 

Throughout our Optimizing MSSQL Server Analysis Services series, we will add new MDX Optimization Techniques segments from time to time, on an ad-hoc basis. Optimization is obviously an area of great interest to developers and consumers alike; 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!

 

Copyright 2004 by the author.