Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Introduction to Performance Tuning

In the last article in our series, "Key MDX Components—Drilling Through to Details," we explored performing drillthrough with multidimensional cubes. First, we discussed scenarios in which drillthrough from summary cube data to the underlying details can be valuable to information consumers. Next, we examined some of the strengths and weaknesses of the capability in MSSQL 2000 Analysis Services.

We examined the steps required to implement drillthrough as a part of setting up a sample drillthrough in the Cube Editor. This "approach from the visual side" allowed us to focus on concepts in an introductory fashion. We then practiced the creation and use of an example MDX query that used the DRILLTHROUGH statement to retrieve rowsets from the source data underneath cube cells. We explored the syntax for the DRILLTHROUGH statement and discussed options that surrounded this functionality as a part of our hands-on exercises.

In this lesson, we explore methods for optimizing performance from our MDX queries. First, we will discuss the main types of intervention through which we can enhance performance of our MDX queries; then we will explore examples of the various types in turn in a hands-on manner, to provide a practical level of awareness that we can apply in our daily work with MDX.

In this lesson, we will do the following:

  • Discuss how Analysis Services processes queries, as a preparation for our focus on optimization techniques

  • Discuss the types of intervention we can use to optimize MDX queries

  • Discuss the Large Level Threshold property and its pervasive importance

  • Discuss the control of location of query execution

  • Explore optimization of set operations and syntax arrangement considerations

  • Explore the optimal uses of caching

  • Discuss the substitution of external functions

  • Discuss leveraging database structure for optimal performance

  • + Share This
  • 🔖 Save To Your Account