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

February 7, 2005

In this version of the query, we employ the formidable NONEMPTYCROSSJOIN() function to improve the query speed dramatically. As many of us are aware, NONEMPTYCROSSJOIN() returns the "non-empty" crossjoin of multiple sets. In accepting two or more sets for its input, nesting of the function is not required, unlike CROSSJOIN(), which can handle only two sets per function, and thus does require nesting (as we saw in the original query above). NONEMPTYCROSSJOIN() filters out calculated members in this case, as well, and so it might not be the best approach in a scenario where calculated members are to be returned (we will examine the optimization of CROSSJOIN() under such a scenario in another article).

The query executes, this time palpably faster, and the results dataset appears as shown in Illustration 10.

Next, we will take advantage of another feature of the NONEMPTYCROSSJOIN() function, the set count parameter. We will do so within a modification to our last query.

-- MDX28-3: Enhance NONEMPTYCROSSJOIN() through use of set-count parameter

The query, with our inserted space, resembles that shown in Illustration 12.

{[Store Type].[All Store Type].[Supermarket]}, 3)  

The query, with the syntax inserted into the space we created above, resembles that depicted in Illustration 13.

( [Store Type].[All Store Type].[Supermarket], 

The line to be removed appears circled in Illustration 14.

[Measures].[Warehouse Profit]

) } , 15,

{[Store Type].[All Store Type].[Supermarket]}, 3) 

The query, with modifications made in the last several steps, resembles that depicted in Illustration 17.

The query executes, once again, and the results dataset appears as shown in Illustration 18 (identical to our last results dataset).

-- MDX28-3: Enhance NONEMPTYCROSSJOIN() through use of set-count parameter
SELECT 
    {[Measures].[Warehouse Profit]} ON COLUMNS,
    TOPCOUNT( {NONEMPTYCROSSJOIN
        ([Warehouse].[Warehouse Name].Members,
           [Store].[Store Name].Members, 
               [Product].[Product Name].Members, 
                    {[Store Type].[All Store Type].[Supermarket]}, 3)   } ,
                         15, [Measures].[Warehouse Profit])
                             ON ROWS
FROM  
   [MDX27_CROSSJOIN_TESTING]
WHERE
([Time].[1997])

To summarize our latest set of enhancements, we have taken the NONEMPTYCROSSJOIN() version to which we had evolved in our last query, and then restructured it to take advantage of the set count parameter. As we noted earlier, NONEMPTYCROSSJOIN() accepts two or more sets for crossjoining purposes. NONEMPTYCROSSJOIN() also allows for an optional set count parameter, which is used to determine the sets, beginning with the left most set we supply ("Set 1"), that will be included in the results dataset that appears.

In our first use of the NONEMPTYCROSSJOIN() function in this lesson, we placed the [Store Type].[All Store Type].[Supermarket] set in the Numeric Expression position of the TOPCOUNT() function, which is laid out as shown below:

TopCount(«Set», «Count»[, «Numeric Expression»])

for the following syntax (excerpted from our second query, MDX28-2):

         ( [Store Type].[All Store Type].[Supermarket], 
             [Measures].[Warehouse Profit])) ON ROWS

In this query, we enforced our "Supermarkets only" requirement by combining [Store Type].[All Store Type].[Supermarket] with the desired measure, [Measures].[Warehouse Profit] in the Numeric Expression portion of the function. While the overall approach in the second query resulted in dramatically better performance than the original query, we went it one better in the third approach.

In our third query, MDX28-3, we transferred the "filter" set, [Store Type].[All Store Type].[Supermarket] to the group of sets included within the NONEMPTYCROSSJOIN(), to boost performance even more. This gives us the same effect from a filter standpoint; the only way it might not work for us in meeting the consumers' requirement would be in the fact that we already have a result dataset that appeals to them from a presentation perspective.

In fact, we do not want to change the presentation at this point. Simply including [Store Type].[All Store Type].[Supermarket] in the NONEMPTYCROSSJOIN() sets, and leaving all else unchanged, would result in the results dataset depicted in Illustration 19.

Fortunately, the NONEMPTYCROSSJOIN() function comes to our rescue with the set count parameter. This allows us to have the best of both worlds: enhanced performance and the presentation the information consumers have requested. By placing a "3" in the set count parameter, we are crafting the function to employ all four sets in "determining emptiness," but to return (in the results dataset) sets 1, 2, and 3 - and therefore to leave [Store Type].[All Store Type].[Supermarket] out of the picture. We are thus able to meet the business requirement.

As we mentioned earlier, NONEMPTYCROSSJOIN() may not be the best approach in scenarios where calculated members are involved. The function's inherent removal of calculated members might rule out its selection as an option. I will address alternatives for these scenarios in a later article.

Summary ...

In this article, we examined the use of the CROSSJOIN() function, and factors that can render this otherwise powerful tool 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. As in the other articles of this series, after introducing our topic, we prepared for our practice exercise by creating and processing a "clone" of a sample cube (in this case the Warehouse cube.)

We next proceeded to examine the query under consideration, and determined that it contained a CROSSJOIN() function that formed the nucleus of the performance issues noted by the information consumers. We enhanced the query in successive steps, discussing the reasons for our modifications as we applied each. We substituted NONEMPTYCROSSJOIN() for the original CROSSJOIN() to make significant performance gains, and then provided further enhancement by leveraging the set count parameter in the NONEMPTYCROSSJOIN() function. Throughout our practice exercise we explained the results we obtained from the steps we took to accomplish the solution.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.