Featured Database Articles

We now have a clone cube with which we can
pursue our examination of CROSSJOIN() optimization. Let's initialize
the MDX Sample Application, as a platform from which to perform our practice
exercises, taking the following steps:

1.
Start the MDX
Sample Application.

We are
initially greeted by the Connect dialog, depicted in Illustration 7.

The
illustration above depicts the name of my server, MOTHER1, and properly
indicates that we will be connecting via the MSOLAP provider (the
default).

2.
Click OK.

The MDX
Sample Application window appears.

3.
Ensure that FoodMart
2000 is selected as the database name in the DB box of the toolbar.

4.
Select the new
MDX27_CROSSJOIN_TESTING cube in the Cube drop-down
list box.

5.
Click File
--> New to open a blank Query pane.

The MDX
Sample Application window should resemble that shown in Illustration 8,
complete with the information from the MDX27_CROSSJOIN_TESTING cube displaying in the Metadata tree (left
section of the Metadata pane).

Illustration 8: The MDX Sample Application Window
(Compressed View)

We
will begin creating our query with a focus on returning results efficiently. We
request the specific requirements for the query from the information consumers.
They explain that they have requested to see Warehouse Profit, based
upon individual Store (denoted in the cube as Store Name), Product
(denoted as Product Name), and Store Type ("Store Type")
combinations, for Supermarket Store Types specifically, for
operating year 1997. They want to see the top fifteen combinations
only, in descending order, as the final presentation.

We obtain the original
query, to which we have referred above, from the MSAS instance on the computer
abandoned by the developer upon his lay off. The query appears as follows:

The consumers with whom we are interacting
tell us that the query does, indeed, give them the results they want, in the
appropriate layout. We determine that we will create an identical query in the Sample
Application, upon which we will apply enhancements to tune its
performance. We will save each step as a separate query to allow us to "fall
back," if necessary, to a previous step, as we incrementally modify the
query.

1.
Create the
following new query (identical, except for comment line, to the original):

After
running for a few seconds, the results dataset appears as depicted in Illustration
9.

Illustration 9:
The Results Dataset - Original Approach

3.
Save the query
as MDX28-1.

Based
upon what we know about the CROSSJOIN() function, we can readily see
that the query above can be optimized. First, we note that the query as
originally written creates every Warehouse Name (24), Store Name
(24), and Product Name (1,560) combination. This means, once again,
that the query generates 898,560 combinations, many of which (as we mentioned
above) are empty, a frequent occurrence in CROSSJOIN() results.
Furthermore, in addition to taking the time and resources to generate all
combinations, we are next performing our TOPCOUNT() function on all
combinations, most of which, again, are empty in the first place.

Let's
take the first step to make the query more efficient. We will substitute the NONEMPTYCROSSJOIN()
function for the CROSSJOIN(). Our objective will be to filter out the
empties. This alone will give us a significant boost in processing speed.