Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 7, 2005

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

By William Pearson

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

5.  Execute the query using the Run Query button.

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

Click for larger image

Illustration 10: The Results Dataset - Substitution of NONEMPTYCROSSJOIN()

6.  Save the query as MDX28-2.

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.

7.  Change the comment line in the query to read as follows:

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

8.  Save the query as MDX28-3, to protect MDX28-2.

9.  Place the cursor between .Members, in the sixth line of the query - counting the comment line - and the ")" character, as depicted in Illustration 11 (partial view).

Illustration 11: Placing the Cursor ... (Partial View of the Query)

10.  Press the ENTER key four times.

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

Illustration 12: Query with Inserted Space (Partial View of the Query)

11.  Insert a comma (",") after .Members (at the point where we placed the cursor in Step 9).

12.  Insert the following between the line containing .Members, and the following line (containing " )}, 15, ").

{[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.

Illustration 13: Query with Inserted Syntax (Partial View of the Query)

13.  Remove the entire line originally containing the following:

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

The line to be removed appears circled in Illustration 14.

Illustration 14: Line to be Removed ... (Partial View of the Query)

14.  Remove one of the two parentheses (")") to the right of the following:

[Measures].[Warehouse Profit]

which appears just before the ON ROWS keywords, as shown in Illustration 15.

Illustration 15: Remove the Extra Parenthesis (Partial View of the Query)

15.  Remove the parenthesis (")") at the left of the following line:

) } , 15,

which appears just below the line containing

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

which we added above. The location of the character to remove is shown in Illustration 16.

Illustration 16: Remove the Left Parenthesis (Partial View of the Query)

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

Illustration 17: Complete Query with Modifications

16.  Execute the query, using the Run Query button, once again.

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

Illustration 18: The Results Dataset - After Final Modifications

17.  Resave the query as MDX28-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 have tidied it up slightly, as well, but the syntax is identical).

-- MDX28-3: Enhance NONEMPTYCROSSJOIN() through use of set-count parameter
    {[Measures].[Warehouse Profit]} ON COLUMNS,
        ([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

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.

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

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

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

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM