Introduction
In this article we examine usage-based optimization in Analysis Services. Among several tools that Microsoft SQL Server 2000 Analysis Services offers us to assist in the maintenance and optimization of our cubes, two of these tools, the Usage Analysis Wizard and the Usage-Based Optimization Wizard, leverage the usage-based optimization features of Analysis Services. These tools make it easy to perform basic performance enhancement of our cubes, based upon the usage of those cubes by information consumers. My experience has been that, regardless of the design effort invested in any given business intelligence application, particularly within the context of anticipating the patterns of use of that application by the intended consumers, nothing can quite equal history as a guide to future human activity.
In Analysis Services 2000, the Usage Analysis Wizard allowed us to rapidly produce simple, on-screen reports that provided information surrounding a cube's query patterns - information that could be useful in helping us to decide whether to consider making structural changes to optimize cube design. The Usage-Based Optimization Wizard, the descendant of which is the subject of this article, embellished the effectiveness of the Storage Design Wizard, and went significantly farther than the generation of simple reports. It offered us the capability to base aggregation design upon a given cube’s usage statistics, in combination with other factors, and to make subsequent adjustments to our existing aggregation design and storage mode as time passed, and information collected from which meaningful statistics could be derived.
In this hands-on article, we will consider the
Analysis Services 2005 Usage-Based Optimization Wizard, which combines some of the features we have seen in the related
Analysis Services 2000 wizards. We will discuss preparation for its use, as well as the steps involved in making the
Usage-Based Optimization Wizard an effective tool in our
Analysis Services administration toolset.
The Usage-Based Optimization Wizard
Overview and Discussion
The Usage-Based Optimization Wizard provides us a quick means of creating aggregations to improve cube processing performance. While the Storage Design Wizard serves us well when we initially design storage for our cubes, and allows us to specify parameters to achieve a tradeoff between storage requirements and query response time that is appropriate to our business environments, the Storage Design Wizard is designed to assume that “all queries are equal,” with regard to the resource requirements they place upon the system, and with regard to the likelihood of their selection by information consumers. Once the cube is designed, deployed and processed, and once it becomes a data source for a potentially diverse range of consumers, it often transpires that some queries are executed more than others, and that various parts of the cube structure are utilized more heavily than others.
The Usage-Based Optimization Wizard enables us fine tune the aggregations within our cubes so that recurring queries obtain quicker response times than infrequent or “one-off” queries. The Wizard allows us to do this through its analysis of queries that have been submitted by various applications to Analysis Services. The Usage-Based Optimization Wizard allows us to go as far as to even select the specific queries for which we wish to optimize, if that is desirable. Just as we find to be the case with the Storage Design Wizard, the data aggregations designed by the Usage-Based Optimization Wizard are created when the respective partition, its measure group, or its cube is processed.
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 frequency with which 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. We will examine the operation of the Usage-Based Optimization Wizard within a context of aggregation design, as we accomplish the following:
-
Create a copy of a sample Analysis Services database for use in our practice exercise;
-
Enable the Analysis Server Query Log to capture query statistics;
-
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.
Considerations and Comments
For purposes of the practice exercises within this series, we will be working with samples that are provided with MSSQL Server 2005 Analysis Services. These samples include, predominantly, the Adventure Works DW Analysis Services database (with member objects). The Adventure Works DW database and companion samples are not installed by default in MSSQL Server 2005. The samples can be installed during Setup, or at any time after MSSQL Server has been installed. The topics “Running Setup to Install AdventureWorks Sample Databases and Samples” in SQL Server Setup Help or “Installing AdventureWorks Sample Databases and Samples” in the Books Online (both of which are included on the installation CD(s), and are available from www.Microsoft.com and other sources, as well), provide guidance on samples installation.
Important information regarding the rights / privileges required to accomplish samples installation, as well as to access the samples once installed, is included in the references I have noted.