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

In this article, we will continue our examination of an area of considerable interest to MSAS developers and practitioners, and an area where optimization skills are vital to the efficient operation of both client and server. In our last article, MDX Optimization Techniques: Introduction and the Role of Processing, we began an exploration of optimizing MDX query performance. Beginning with that article, continuing with this article, and throughout MDX-focused articles that I will publish on an intermittent basis, we will discuss several tuning interaction points through which we can enhance the performance of MDX. In each of these, we will explore examples of the various general points in turn, in a hands-on manner, so as to provide a practical level of awareness that we can apply in our daily work with MDX.

In the previous article, we focused on two of the main ways to enforce control of processing location, the Large Level Threshold property and the Execution Location parameter, and performed hands-on practice with the settings involved with each. In this lesson, we will pick up where we left off, and:

• Consider further the types of intervention we can use to optimize MDX queries;
   

• Examine the importance of optimizing set operations in MDX;
   

• Explore syntax arrangement considerations in the optimization of our MDX queries.

Further Location Considerations

In our last article, we focused on two of the main ways to control processing location, the Large Level Threshold property and the Execution Location parameter, as a part of our discussion of the first tuning interaction type, control of location of query execution. While these two options provide perhaps the most straightforward ways to control where a query is evaluated and executed, there are additional, less direct ways to force processing in a desired location. We will consider some of these approaches to conclude our discussion of the location control interaction type.

Other Methods of Influencing Execution Location

At the individual query level, no means is readily available for a client application to direct where a query executes. We can, however, mandate that large-level operations execute at the client through the use of indirect means. The specification of a named set for use within the query will force processing at the client level. We can, therefore, create a named set (using either of the CREATE SET or WITH SET clauses), containing members of a large level, at the client, and then use the same named set within a query to force client-based execution.

Calculated members and calculated cells provide additional options for indirect control of the processing location. The manner of creation of a calculated member is important in determining its location-fixing effects. Using the CREATE MEMBER or WITH MEMBER clauses within a query, to define a calculated member at the server, will produce a calculated member that can be processed at the server or client equally successfully. By contrast, using CREATE MEMBER to produce a calculated member within a session will result in forced client-based execution of the query that houses it.

Calculated cells may also force client-based processing. Again, the manner in which the calculated cells are defined is important in determining their location-fixing effects. A calculated cell that is created with the CREATE CELL CALCULATION clause, at either the client or the server, can be processed at the server. By contrast, the use of the WITH CELL CALCULATION clause at the client will result in a query whose processing will be client-based.

The existence of two conditions can force a query to process on the server: a reference to a filter operation within the query, and (consistent with our discussion regarding large levels in MDX Optimization Techniques: Introduction and the Role of Processing) a large dimension level. Let’s take a look at the mechanics behind this in a little more detail.

First, we will return to the MDX Sample Application, having seen in our last session how it provides an excellent platform from which to learn about MDX and, as is often useful in a development scenario, about the data and the metadata in our cubes. Many of the MDX operations that might be performed from a client application can be simulated here or elsewhere, as we will demonstrate in many articles throughout this series. The Sample Application affords us another excellent perspective from which to view the interplay of the OLAP data source and MDX.

NOTE:  It is important to keep in mind that client applications will differ in many ways. Individual settings, design characteristics, capabilities, and other considerations will likely mean differences in operation and performance using the techniques we describe in our lessons, just as they will do in the context of any other reference document.