Optimizing Microsoft SQL Server Analysis Services: MDX Optimization Techniques: Further Control of Processing Location and Expression Arrangement

We are initially greeted by the Connect dialog, shown in Figure 1.

Figure 1: The Connect Dialog for the MDX Sample Application

The figure above depicts the name of my server, MOTHER1, and properly indicates that we will be connecting via the MSOLAP provider (the default).

2. Click OK.

The MDX Sample Application window appears.

3. Clear the top area (the Query pane) of any remnants of queries that might appear.

4. Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

5. Select the Warehouse cube in the Cube drop-down list box.

The MDX Sample Application window should resemble that depicted in Figure 2, complete with the information from the Warehouse cube displaying in the Metadata tree (left section of the Metadata pane).

Figure 2: The MDX Sample Application Window (Compressed View)

We will create an MDX query that helps us to “qualify” a second query as containing elements that cause it to fall within the two conditions we have exposed above that force a query to process on the server.
 

6. Type the following query into the query pane of the Sample Application:

-- SSP_007-1: Qualification through Count

WITH

MEMBER[Measures].[Count] AS

'COUNT({ [Product].[Product Name].Members})'

SELECT

{[Measures].[Count]}ON COLUMNS

FROM Warehouse

Again, our intent here is to ascertain that an upcoming example expression ([Product].[Product Name].Members) will “qualify” as meeting conditions that would force it to process on the server. Therefore, in our first query, we are simply obtaining a count of the members of the given level.
 

7. Execute the query using the Run Query button.

The results dataset appears as shown in Figure 3.

Figure 3: Results Dataset, Count Query

8. Save the query in a convenient location as SSP_007-1.

We see that [Product].[Product Name].Members refers to a genuine Large Level, because the number of members in the Product Name level (1,560) of the Product dimension exceeds the Large-Level Threshold we set in our last article (750). For that matter, it exceeds even the default threshold that existed before our modifications (1000). (The number of members is also verifiable at the RDBMS level in the FoodMart2000.mdb sample that is installed with MSSQL Server 2000 Analysis Services).

NOTE: For details on setting the Large-Level Threshold, see MDX Optimization Techniques: Introduction and the Role of Processing.

Let’s use the level whose population we have just quantified in the COUNT query above to illustrate. The inclusion of a filter within our query will also be a driver for server-based processing, as we shall see.

9. Create the following new query:

-- SSP_007-2: Qualification through Count & Filter

SELECT

{[Measures].[Units Shipped]} ON COLUMNS,

TopCount ([Product].[Product Name].Members,

7, [Measures].[Units Shipped]) ON ROWS

FROM Warehouse

The use of TopCount() above provides an instance where server-based execution is likely to be appropriate, for the reasons we have already exposed. Most of the large level will be pruned away by the filter action before returning the result. As we noted earlier, existence of a filter operation within the query is another driver for server processing.
10. Execute the query using the Run Query button.

The results dataset appears as shown in Figure 4.

Figure 4: Results Set, TopCount() Query

11. Save the query as SSP_007-2.

While level-size and filter requirements are good criteria to use in most cases for determining the likelihood of forced server-based processing, there are scenarios where even meeting or exceeding the parameters of these two criteria will not force a query to execute on the server. Examples of these situations include the presence of a function or functions within the query (say a user-defined function that is registered solely on the client) that cannot execute on the server. Also, as is somewhat obvious, a query that is executed against a local cube will not process on a remote server.