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

December 22, 2003

The Types of Intervention

As most of us know, from having worked with MDX in the preceding articles of this series and elsewhere, MDX syntax allows us to define and manipulate multidimensional objects and data. As with an SQL query, we have found that an MDX query requires a data request (the SELECT clause), a point from which to begin (the FROM clause), and a filter (the WHERE clause). These and other keywords, which may or may not actually be visible in our queries, (many assume the values of defaults when left unspecified), provide the tools used to extract specific portions of data from a cube for analysis. MDX also supplies a robust set of functions for the manipulation of data, data definition language (DDL) syntax for managing data structures, and support for extending itself with user-defined functions. MDX commands abound for creating (and deleting) cubes, dimensions, measures, and their subordinate objects.

Many approaches exist in the rich MDX toolset to help us accomplish our business objectives; and, as we might reasonably expect, some approaches are more efficient in operation than others are, predominantly in terms of memory and CPU utilization. In this article, we will discuss some of the types of intervention that we might perform to achieve optimization of a query, discussing the points at which adjustments and / or enhancements might give us the performance boosts we desire. While we will touch upon enhancements that can be made outside our queries to contribute to performance, our focus in this "triptych" of articles will be largely upon items that can be considered within the queries themselves to contribute to overall efficiency.

The types of intervention that we might investigate as options for the optimization of the MDX queries we construct consist of the following general groups:

• Control of the location of processing;
• Optimization of set operations and syntax arrangement;
• Optimal uses of caching;
• Substitution of external functions;
• Leveraging database structure.

We will examine each of these types of intervention in the following tutorials, and look at examples of actions we might take within the context of most, to illustrate possible performance enhancements we might bring about.

Understanding How Queries are Processed

Before we delve into our examination of MDX query optimization techniques, a general understanding of how Analysis Services processes our queries will be useful. It is from the perspective of each of the process points that we can seek to optimize a given query. From a high level, these steps occur in the following general order:

• An MDX statement or query is issued by a client application.
• The statement or query is parsed by the PivotTable Service, which rearranges it into an expression tree to facilitate ease of use in subsequent steps.
• The metadata identification is resolved, and the determination made to process the query on the server or on the client.
• Final preparations for processing occur, including possible further optimization procedures.
• Rows and columns are identified (the axes are resolved) and, in some cases (isolation mode, predominantly), cell values are pre-fetched and cached.
• The combined results are returned to the application from which the query originated.

Integral to our lesson will be the location at which the calculations are performed. The location at which the operations occur is highly relevant to performance optimization, as most of us can readily see. The location under consideration can be either the Analysis Server or the client PivotTable service.

The Location of Query Processing

Control of the location of processing, as we shall see, can be managed from the Large Level Threshold property setting. 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. We will examine these settings in more depth as we proceed, but before we undertake to control the location of processing, we should understand the benefits and costs associated with the client / server processing options from which we can select. Some of the more important of these are summarized in Table 1.

Let's take a look at the first of these two prominent settings, Large Level Threshold property, with which we will begin a hands-on examination of managing processing location.