Optimizing Microsoft SQL Server Analysis Services: Optimization Tools: Usage-Based Optimization Wizard

About the Series
 
This is the third article of the series, Optimizing MSSQL Server Analysis Services. The primary focus of this series is the introduction of optimization tools and concepts for MSSQL Server Analysis Services (“MSAS”). The series is designed to provide hands-on application of the fundamentals of MSAS optimization from multiple perspectives.

As we progress through the series, we may reference previous articles and the concepts we have introduced therein. However, one of my objectives is to make each article as “standalone” as possible, meaning that we should not encounter cases where we cannot complete a given procedure without components or objects that we have created in previous articles. This should make it easier for “casual” visitors to join us with any lesson, and still successfully complete that session, given an existing understanding of concepts and principles that we have accumulated up to that point.

For important information concerning the applications you need to have in place to obtain the most benefit from the articles of this series, please see “What We Will Need to Complete the Series Tutorials” in our introductory article, Optimization Tools: The Storage Design Wizard.

Introduction and Scope

Among the optimization tools that assist us in optimizing the performance of our MSAS cubes, we have thus far discussed the Storage Design Wizard and the Usage Analysis Wizard, together with the concept of usage analysis from other perspectives. (For a discussion of these tools, see Optimization Tools: The Storage Design Wizard and Optimization Tools: Basic Usage Analysis, respectively.

First, recall that the Storage Design Wizard enables us to optimize the tradeoff between system performance and the disk space allocated to storing aggregations. Through this wizard, we take advantage of a complex MSAS algorithm to determine the optimal set of aggregations for a given scenario. We then can use the information that results to derive additional aggregations to put into place to improve cube query performance. With the Storage Design Wizard, we obtain a “system assist” with the complex management of aggregation design, so as to free us, as administrators and developers, to focus on application design considerations.

Within our exploration of the Usage Analysis Wizard, we embarked upon an introduction to the concept of usage-based optimization, where we determine the queries most often executed upon our cubes, with the primary objective being to physically adjust our cubes to perform better, based upon the details of that usage.

In this lesson, we will consider the Usage-Based Optimization Wizard that combines some of the features we have seen in the wizards we have previously considered. The Usage-Based Optimization Wizard performs an analysis of the query activity that is very similar to that of the Usage Analysis Wizard, and then it assists us in tuning performance of the cube under consideration. The Wizard then provides us a means to immediately tune our cube’s performance to provide rapid response to the queries most often executed, as we instruct it to design aggregations appropriate to those queries, while maintaining reasonable storage requirements. In this way, the Usage-Based Optimization Wizard mimics the action of the Storage Design Wizard, as we shall see.

As we explore the operation of the Usage-Based Optimization Wizard, we will see the ways it combines the functions of the wizards I have already introduced. And while full reliance upon wizardry is a state to which systems and administrators have, for the most part, yet to evolve, we will see in our hands-on practice with the Usage-Based Optimization Wizard that, regardless of the magnitude of that reliance, the Wizard stands ready to assist us in rapidly building a system with a minimum number of aggregations, which we can later performance tune according to the actual usage of the system, providing, in many cases, a shorter overall path to reaching our objectives.

In this article, we will examine the operation of the Usage-Based Optimization Wizard within a context of aggregation design. We will accomplish this as we do the following:

• Create a copy of a sample cube for use in our practice exercise.
   

• Prepare the cube further by processing and manipulating data / creating Query log entries.
   

• Perform a practice exercise, using the Usage-Based Optimization Wizard, to set aggregations for our practice cube.
   

• Examine each of the possible settings that are available to us, as we proceed through the guided steps of the Wizard.
   

• Comment upon general optimization concepts as we proceed through our practice example.

The Usage-Based Optimization Wizard as a Tool for Designing Aggregations

The Usage-Based Optimization Wizard provides us a quick means of creating aggregations to improve cube processing performance. We can instruct the Wizard, through a series of dialogs, to create aggregations based upon a flexible combination of several cube usage characteristics, including:

• a date range of cube use;
   

• the users querying the cube;
   

• the number of times a query was executed;
   

• response time for the query;
   

• storage mode of the data involved.

We will examine each of these parameters as we work through a practice session with the Usage-Based Optimization Wizard in this article.

NOTE: If you are performing the steps of our article on a production (or other) cube, instead of the sample cube we will create for the purposes of illustration of the use of the Usage-Based Optimization Wizard, the preparation steps in the first couple of sections below can be skipped.