MDX Essentials: The CROSSJOIN() Function: Breaking Bottlenecks - Page 5

Procedure

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:

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).

The MDX Sample Application window appears.

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).

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:

SELECT 
    {[Measures].[Warehouse Profit]} ON COLUMNS,
   TOPCOUNT( {CROSSJOIN([Warehouse].[Warehouse Name].Members,
CROSSJOIN([Store].[Store Name].Members, 
       [Product].[Product Name].Members))}, 15, ([Store Type].
           [All Store Type].[Supermarket], [Measures].[Warehouse Profit]))
               ON ROWS
FROM  
   [MDX27_CROSSJOIN_TESTING]
WHERE
    ([Time].[1997]) 

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.

--MDX28-1:  Original Query (Suboptimal)
SELECT 
    {[Measures].[Warehouse Profit]} ON COLUMNS,
   TOPCOUNT( {CROSSJOIN([Warehouse].[Warehouse Name].Members,
CROSSJOIN([Store].[Store Name].Members, 
       [Product].[Product Name].Members))}, 15, ([Store Type].
           [All Store Type].[Supermarket], [Measures].[Warehouse Profit]))
               ON ROWS
FROM  
   [MDX27_CROSSJOIN_TESTING]
WHERE
    ([Time].[1997]) 

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

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.

--MDX28-2:  Introduce NONEMPTYCROSSJOIN() as initial improvement
SELECT 
    {[Measures].[Warehouse Profit]} ON COLUMNS,
   TOPCOUNT( {NONEMPTYCROSSJOIN([Warehouse].[Warehouse Name].Members,
        [Store].[Store Name].Members, 
                [Product].[Product Name].Members)} , 15, 
                   ( [Store Type].[All Store Type].[Supermarket], 
                       [Measures].[Warehouse Profit])) ON ROWS
FROM  
   [MDX27_CROSSJOIN_TESTING]
WHERE
   ([Time].[1997])