- About the Series
- Introduction to Performance Tuning
- Understanding How Queries Are Processed
- Types of Intervention
- Large Level Threshold Property
- Location of Query Processing
- Optimization of Set Operations
- Importance of Optimal Arrangement
- Expression Arrangement
- Caching and Optimization
- Other Performance Enhancement Options
- A Parting Word
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Types of Intervention
As we all know after having transited the preceding articles of this series, MDX as a 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 (recall that 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 objectives and, as we might reasonably expect, some approaches are more efficient in operation than others—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. Although we touch upon enhancements that can be made outside our queries that might contribute to performance, we will focus 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 tutorial, and look at examples of actions we might take within the context of most to illustrate possible performance enhancements we might bring about.