Optimizing Microsoft SQL Server Analysis Services: MDX Optimization Techniques: Introduction and the Role of Processing

The Execution Location Parameter

We have learned that large levels, as defined by the threshold, are processed solely at the server level, and not sent to the client unless a specific request is made. Small levels, in contrast, are sent to the client to be processed even if the associated level is not requested in its entirety.

The Execution Location connection parameter provides another, more direct means of controlling location of processing of our MDX queries. To build a query that will be executed on the server, we need only use the OLE DB property ExecutionLocation, which specifies where the query is to be resolved.

The setting options for ExecutionLocation are displayed in Table 2.

Table 2: Cube Editor View of the Store Dimension Member Properties

The location of query processing can be forced, using the Execution Location property, to our choice of server or client. The default setting (Automatic), allows the PivotTable Service to determine where the query should be processed, based upon its prediction of which option will mean better performance.

A critical factor in the determination of the execution location, when using the default option (where the processing location is determined internally), is the Large Level Threshold property we have already discussed. Say the level is set at 1,000. The PivotTable Service forces “large level” treatment if it determines that the query will require the aggregation of 1000 or more members in a given dimension level. If a large level is apparent, the query processing occurs at the server. Conversely, if a large level is not evaluated as being apparent, the processing of the query occurs at the client.

Other means of controlling the location of processing involve named sets, calculated members, and calculated cells, and the circumstances under which they are created. We will delve into the mechanics of these options further in our next session together, MDX Optimization Techniques: More About Processing Location and Arrangement Considerations, where we will also move into arrangement considerations in MDX query optimization.

Summary

In this lesson, we began the Optimizing MDX group of articles. We established the objective, within this mini-series, of exploring methods for optimizing the performance of our MDX queries. We introduced what I like to call tuning interaction points, or classifications of approaches that we can take, to enhance the performance of MDX; we will revisit the tuning interaction points theme throughout the series.

Within the context of each intervention type that we expose, we will perform practice examples to reinforce an awareness that we can apply in our daily work with MDX. In this lesson, we initially discussed the general steps Analysis Services takes in processing queries, as a preparation for our overall focus on optimization techniques; we then “drilled down” our focus to an examination of the important Large Level Threshold property, followed by an introduction to the first intervention type, to which the property contributes its strongest influence, control of location of query execution.
 

Copyright