Optimizing Microsoft SQL Server Analysis Services: MDX Optimization Techniques: Optimizing the CROSSJOIN() Function

10. Execute the query using the Run Query button.

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

Figure 10: The Results Dataset – Substitution of NONEMPTYCROSSJOIN()

11. Save the query as SSP014-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.

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

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

13. Save the query as SSP014-3, to protect SSP014-2.

14. Place the cursor between .Members, in the sixth line of the query – counting the comment line – and the “)” character, as depicted in Figure 11 (partial view).

Figure 11: Placing the Cursor … (Partial View of the Query)

15. Press the ENTER key four times.

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

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

16. Insert a comma (“,”) after .Members (at the point where we placed the cursor in Step 9).

17. 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 Figure 13.

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


Leave a comment

Your email address will not be published.