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 to 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.
» 
Comment and Contribute
