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:
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:
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.
Table 1: Advantages and Disadvantages of Processing Location Options
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.