dcsimg

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

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


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.

MDX Essentials Series
The LEVEL_NUMBER Member Property
The LEVEL_UNIQUE_NAME Intrinsic Member Property
Intrinsic Member Properties: The HIERARCHY_UNIQUE_NAME Property
Intrinsic Member Properties: The DIMENSION_UNIQUE_NAME Property
Further Combination of BottomCount() with Other MDX Functions
Combine BottomCount() with Other MDX Functions to Add Sophistication
Basic Set Functions: The BottomCount() Function, Part I
Intrinsic Member Properties: The MEMBER_VALUE Property
Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property
Intrinsic Member Properties: The MEMBER_NAME Property
Intrinsic Member Properties: The MEMBER_KEY Property
Intrinsic Member Properties: The MEMBER_CAPTION Property
Set Functions: The StripCalculatedMembers() Function
Set Functions: The AddCalculatedMembers() Function
MDX Numeric Functions: The Min() Function
MDX Numeric Functions: The Max() Function
Set Functions: The .AllMembers Function
MDX Essentials: Set Functions: The MeasureGroupMeasures() Function
String Functions: The .Properties Function, Part II
String Functions: The .Properties Function
Logical Functions: IsGeneration(): Conditional Logic within Filter Expressions
MDX Scripting Statements: Introducing the Simple CASE Statement
Logical Functions: IsGeneration(): Conditional Logic within Calculations
Logical Functions: IsAncestor(): Conditional Logic within Filter Expressions
MDX Clauses and Keywords: Use HAVING to Filter an Axis
Logical Functions: IsAncestor(): Conditional Logic within Calculations
Logical Functions: IsSibling(): Conditional Logic within Filter Expressions
Logical Functions: IsSibling(): Conditional Logic within Calculations
MDX Operators: The IsLeaf() Operator: Conditional Logic within Filter Expressions
MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations
MDX Numeric Functions: The .Ordinal Function
Other MDX Entities: Perspectives
MDX Operators: The IS Operator
MDX Set Functions: The Distinct() Function
MDX Set Functions: The ToggleDrillState() Function
Set Functions: The DrillUpLevel() Function
Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions
MDX Set Functions: DrillDownLevel()
MDX Set Functions: The DRILLUPMEMBER() Function
MDX Essentials: Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions
MDX Essentials : Set Functions: The DRILLDOWNMEMBER() Function
MDX Essentials: Drilling Through with MDX: The DRILLTHROUGH Statement
MDX Essentials: String Functions: The .UniqueName Function
MDX Essentials: String Functions: The .Name Function
MDX Essentials: String / Numeric Functions: The CoalesceEmpty() Function
MDX Essentials: Basic Set Functions: The TopCount() Function, Part II
MDX Essentials: Basic Set Functions: The TopCount() Function, Part I
MDX Essentials: Enhancing CROSSJOIN() with Calculated Members
MDX Essentials: Set and String Functions: The GENERATE() Function
MDX Essentials: The CROSSJOIN() Function: Breaking Bottlenecks
MDX Essentials: String / Numeric Functions: More on the IIF() Function
MDX Essentials: String / Numeric Functions: Introducing the IIF() Function
MDX Essentials: Logical Functions: The IsEmpty() Function
MDX Essentials: Basic Set Functions: The EXTRACT() Function
MDX Essentials: Numeric Functions: Introduction to the AVG() Function
MDX Essentials: Basic Member Functions: The .Item() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Subset() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Tail() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Head() Function
MDX Essentials: Basic Set Functions: The CrossJoin() Function
MDX Essentials: Basic Numeric Functions: The Count() Function
MDX Essentials: Basic Set Functions: The Filter() Function
MDX Essentials: Basic Set Functions: The EXCEPT() Function
MDX Essentials: Basic Set Functions: The Intersect() Function
MDX Essentials: Basic Set Functions: The Union() Function
MDX Essentials: Basic Set Functions: The Order() Function
MDX Essentials - MDX Time Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions
MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions
MDX Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions
MDX Essentials: MDX Member Functions: "Relative" Member Functions
MDX Member Functions: The Cousin () Function
MDX Essentials: Member Functions: More "Family" Functions
MDX Member Functions: The "Family" Functions
MDX Essentials: MDX Members: Introducing Members and Member
MDX Essentials : MDX Operators: The Basics
MDX Essentials: Structure of the MDX Data Model
MDX at First Glance: Introduction to SQL Server MDX Essentials








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers