MDX in Analysis Services: Optimizing MDX: Control Location of Processing - Page 3

The Large Level Threshold Property

The Large Level Threshold property fixes the point at which a dimension level is too large to be sent to the client application as a single body. The property, by default, is set at the server at 1,000. We can use the property to help us to manage both CPU and memory utilization from the client perspective. Because the property stipulates the point at which level is "too large for the client," it also determines the level at which the number of members from a given level force server-based execution of the PivotTable Service. The property, therefore, is highly useful to us in determining query execution location, which, as we are learning, is a significant point of intervention in the optimization of our MDX queries.

If the number of members in a level is below the threshold we set in this property, it is sent to the client application in a single piece. The level is broken into smaller groups, which are sent to the client application as needed, if the number of members in the level under consideration is equal to or more than the threshold setting. Should the level contain a number of members smaller than this threshold value, the entire level will be sent to the client application. If the level under consideration contains a number of members that is greater than or equal to the value of the Large Level Threshold property, the level is incrementally sent from the server to the client application.

While the default value for the Large Level Threshold property is 1,000, the minimum value for this property is 10. (If the value is set to less than 10, the value will reset itself at 10, automatically preventing any errors in processing). The Large Level Threshold property can be modified at the server, or can be specified by the client upon establishment of a connection to the server. Let's explore the process for setting the parameter in the former case, with the following steps.

The Properties dialog appears.

We have now set the number of members a dimension level must contain to be processed as a large level. The range here can be from 1 - 10,000 (default is 1,000, as we mentioned earlier), and it is meaningful in the context (the number of level members) with which Analysis Services classifies dimension levels into large and small categories only. Keep in mind, as we have already discovered, that large levels are not sent to the client unless they are specifically requested, but small levels are sent to the client even if the entire level is not requested.

We are greeted with a dialog that announces we must restart the Analysis server for changes to take effect, as shown in Illustration 3.

The new threshold level will be enacted upon our next start of Analysis Services.

As we have mentioned, we can set the Large Level Threshold property via an MDX query we generate from a client application. When we define the large-level threshold at the client application, we do so by specifying the setpoint as a connection property. Because the property value is set upon opening the connection, this approach for setting the large-level limit does not provide a means of controlling the location of query processing on a query-by-query basis, any more than the same setpoint within Analysis Services provides for ad hoc location management. The Large Level Threshold property therefore allows for setting the property from the client, much as it is set from within the Analysis Services properties that we explored above. A connection string example is as follows:

	Large Level = 750;

Thus, we see that the control of the location of processing can be managed from the Large Level Threshold property setpoint. While the Large Level value is a critical influencer of which location is selected for query processing, we can also control the location using other means, the chief of which is the Execution Location parameter, which we will take up in our next section.

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 setpoint options for ExecutionLocation are displayed in Table 2.

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 the query does not involve a large level, the processing of the query occurs at the client. If a large level is apparent, the query processing occurs at the server.

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, Optimizing MDX: More on Location, and the Importance of Arrangement, where we will also move into arrangement considerations in MDX query optimization.

Summary and Conclusion ...

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 types of intervention, or classifications of approaches that we can take, to enhance the performance of MDX; we will revisit the types of intervention 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.

» See All Articles by Columnist William E. Pearson, III