Introduction and Scope
In this article, we will introduce an area of considerable interest among MSAS developers and practitioners, and an area where optimization skills are vital to the efficient operation of both client and server. In a subset of upcoming articles that I will publish on a recurring basis, intermingled with other MSAS optimization topics, we will explore methods for optimizing the performance we obtain from our MDX queries. Beginning in this, our introductory article on MDX optimization techniques, and throughout the MDX-focused articles, we will discuss several tuning interaction points through which we can enhance performance of MDX, and 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 this lesson, we will:
-
Introduce the concept of tuning interaction points from which we can optimize MDX queries;
Discuss, from a high level, how Analysis Services processes queries as a preparation for our focus on optimization techniques in this, and upcoming, articles;
Examine the Large Level Threshold property, and discuss its pervasive importance;
Begin to explore scenarios of MDX optimization by controlling the location in which a query processes.
Our examination of optimization by controlling execution location will continue into our next article, where we will elaborate further on the topic, as well as into future articles, where we will address the extensive leverage we can gain by creatively using the principles involved.
The Tuning Interaction Points
As most of us know, from having been at least exposed to MDX, the language allows us to define and manipulate multidimensional objects and data. As with a 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 we do not specify them), provide the syntactic structure we need to extract specific portions of data from a cube for analysis.
Many avenues exist within this rich toolset to help us accomplish our business objectives; and, as we might reasonably expect, some avenues are more efficient in real-world operation than others, predominantly in terms of memory and CPU utilization. In this article, we will discuss some of the tuning interaction points at which we might perform optimization of a query, discussing the places 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 article, as well as in sister articles of the MDX Optimization Techniques subset, will be largely upon items that can be considered within the queries themselves to contribute to overall efficiency.
One of the introductory tuning interaction points we might investigate as a place for the internal optimization of the MDX queries we construct is that of potential control of MDX processing (or execution) location. We will discuss this interaction point in general after getting an overall understanding of query processing itself. Such an understanding provides a basis for understanding the points within a query with which we can interact to make the query more efficient.
Beginning with the interaction point of processing location in this article, we will touch upon other such points in upcoming articles, and look at examples of actions we might take within the context of each, to illustrate possible performance enhancements we might bring about.
Understanding Query Processing
Before we begin 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, and upon which I base the tuning interaction point groupings I have mentioned. 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 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, and the control of which of these is chosen for a given operation can be quite useful in optimizing performance, simply because it enables us to direct the operation to the best physical resource for its undertaking.