Home > Articles > Data > SQL Server

  • Print
  • + Share This
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.

  • + Share This
  • 🔖 Save To Your Account