MDX Essentials: Enhancing CROSSJOIN() with Calculated Members
April 4, 2005
About the Series ...
This article is a member of the series, MDX Essentials. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding features designed to meet specific real-world needs.
For more information about the series in general, as well as the software and systems requirements for getting the most out of the lessons included, please see my first article, MDX at First Glance: Introduction to MDX Essentials.
Note: Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples.
In an earlier article, The CROSSJOIN() Function: Breaking Bottlenecks, we examined the use of CROSSJOIN(), and factors that can render this otherwise powerful function suboptimal within our queries. We discussed a business need as defined by a hypothetical group of information consumers, in which we were asked to tune an MDX query for more optimal performance. Our focus centered upon enhancing query performance when using CROSSJOIN() in medium- to large-sized data sets. After discussing how CROSSJOIN() works in general, and pointing out the way in which its operations can result in crippling performance overhead, we exposed approaches to mitigating that overhead within practice exercises designed to reinforce the concepts.
We learned that using NONEMPTYCROSSJOIN() is, by far, the most effective avenue to minimizing the bottlenecks that plague standard CROSSJOINS() within challenging cube scenarios. We examined two approaches to using NONEMPTYCROSSJOIN() in achieving our ends, finding refinements in the second approach, where we employed the optional set count parameter in the function, to provide more efficiency than the first (which, even in its "vanilla" context, had demonstrated its power to enhance performance dramatically). We noted, however, a prevailing concern amid all this success: NONEMPTYCROSSJOIN() filters out calculated members, and so it is not useful in a scenario where calculated members are to be returned.
In this article, we will examine the enhancement of queries using CROSSJOIN() where calculated members are, indeed, to be returned. We will begin with a simple, but intensive, CROSSJOIN() scenario, reviewing how CROSSJOIN() performance can become an issue where larger sized data sets are involved. We will then undertake a multiple-step practice example, which will initially help us to gain an understanding of the issues encountered with calculated members. We will attempt the approach to minimizing performance overhead that we used in The CROSSJOIN() Function: Breaking Bottlenecks, where we met with a simpler scenario that did not involve calculated members. We will then provide an approach that provides palpable relief of the performance issues, while returning a calculated member that we require to achieve our reporting and analysis objectives.
To accomplish our examination of CROSSJOIN() enhancement when calculated members are a factor, we will undertake the following steps in this article: