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.
Overview
In this article, we will return to a function we introduced
in an earlier article, CROSSJOIN(), whose mission it is to combine two
sets of dimension members into one. We will discuss how CROSSJOIN()
works in general, and then discuss how its performance can become an issue in
medium to large-sized data sets. We will then undertake a multiple-step
practice example intended to assist us in gaining an understanding of the
issues, before exposing approaches to minimizing the performance overhead that
we can encounter in using CROSSJOIN() to reach our reporting and
analysis needs.
To accomplish this objective, we will undertake the following
steps in this article:
-
Create a copy of the Warehouse
sample cube for use in our practice exercise;
-
Prepare the cube further by
processing;
-
Discuss the
operation of the CROSSJOIN() function, and factors that may make its use
suboptimal in our queries;
-
Enhance a
suboptimal query employing a CROSSJOIN() function by substituting NONEMPTYCROSSJOIN();
-
Provide
further enhancement by leveraging the set count parameter
in the NONEMPTYCROSSJOIN()
function;
-
Explain the results we obtain from the steps we take to
accomplish the solution.
IE 7
Firefox 2.0
Comment and Contribute
