MDX Essentials: The CROSSJOIN() Function: Breaking Bottlenecks

February 7, 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 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.