22. Save the query as SSP014-3.
NOTE: Because I am aware that making modifications in stages as we did above might prove tedious for some, the full syntax appears below, for cut and paste transfer, if desired. If you cannot obtain the results shown above, you might find it easier to work with the version below (I’ve tidied it up slightly, as well, but the syntax is identical).
— SSP014-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
[SSP014_OPT_CROSSJOIN]
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, SSP014-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, SSP014-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 Figure 19.
Figure 19: Results with Simple Transfer of “Filter” Set to NONEMPTYCROSSJOIN()
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.
23. Exit the MDX Sample Application when ready.
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.
Copyright 2005 by the author.
]]>