Usage-Based Optimization Wizard in Action
We can call the Usage-Based
Optimization Wizard for a specific partition within a cube, within the
context of the Measure Group for which the partition has been created,
as we shall see. We can work with the Usage-Based Optimization Wizard from
within the SQL Server Management Studio, or from the Business
Intelligence Development Studio. Because we are already in the Management
Studio, and have worked here with both the Analysis Server (and
cube) and the Database Engine (for the underlying relational data source
which also now contains the Query Log, as well), we will call the Usage-Based
Optimization Wizard from within the SQL Server Management Studio.
The Management Studio is best used as an administrative
environment (and I would consider usage-based optimization as an administrative
function, in most cases), whereas the BI Development Studio might be
best leveraged as a development environment, but the lines often blur,
and, because we can access the Usage-Based Optimization Wizard (and many
other capabilities) from both environments, local policies and business needs
might dictate that one or the other is more appropriate.
Let's practice performing
usage-based optimization on a sample Measure Group within the Adventure
Works cube, within our clone Analysis Services database, ANSYS043
Adventure Works DW, by taking the following steps.
From the Object
Explorer in SQL Server Management Studio, expand the Analysis
Services database ANSYS043 Adventure Works DW, by clicking the "+"
sign to its immediate left, as necessary.
Expand the Cubes
folder that appears within the ANSYS043 Adventure Works DW tree.
Expand the Adventure
Works cube that appears within the Cubes folder.
Expand the Measure
Groups folder that appears within the Adventure Works cube.
The various Measure
Groups for the Adventure Works cube appear.
Expand the Internet
Sales Measure Group that heads up the Measure Groups list for
the Adventure Works cube.
Expand the Partitions
folder that appears underneath Internet Sales.
The partitions that make
up the Internet Sales Measure Group (one for each year of Internet
Sales data contained in the cube) appear, as shown in Illustration 30.
30: The Partitions for the Internet Sales Measure Group ...
For more detailed information regarding the nature and use of partitions, and
related topics, see other articles in my Introduction
to MSSQL Server Analysis Services series.
the Partitions folder.
Based Optimization ... from the context menu that appears, as depicted in Illustration
31: Calling the Usage-Based Optimization Wizard ...
Optimization Wizard appears, beginning with the Welcome page, as
shown in Illustration 32.
32: Usage-Based Optimization Wizard Welcome Page
The Select Partitions
to Modify page appears. It is here that we can direct which partitions we
wish to modify. (The fact that we can handle multiple partitions in one dialog
here might come as a relief to those who, finding that the Usage-Based
Optimization Wizard could be called by right-clicking individual partitions,
have concluded that we are required to handle the partitions one-by-one. The
relative obscurity of the documentation on the Usage-Based Optimization
Wizard has led to some confusion here, if forums, blogs, and my own e-mail
receipts on the topic are any indication). We are reminded that we must choose
at least one partition within this page.
Select the All
checkbox (on the column header, to the left of the Partition Name
label), as depicted in Illustration 33.
33: Usage-Based Optimization Wizard Select Partitions to Modify ...
The Specify Query
Criteria page appears. This dialog affords us the opportunity to filter
among the queries taken into consideration by the Wizard in proposing
usage-based aggregation designs. We have, within this dialog, three general options
(four actual checkbox choices) for query selection criteria; we can use
one or more of these to narrow the selection of logged queries upon which we
wish to base our design for optimization. The three general criteria, together
with descriptions, are summarized in Table 1.
1: The Three General Query Selection Criteria Usage-Based Optimization Wizard
Query Criteria Option
Optimization Selection is Based Upon:
Queries within date ranges
range for queries. Date range type can be selected from:
Between All queries between a desired Beginning date and Ending
Beginning Date All queries on or after a selected Beginning date
Ending Date - All queries executed before a selected Ending date
Queries by specific users
Users and Groups of Users, as
defined by User Roles
most commonly executed queries based upon percentage represented of all
number selections blank, putting no filters in place, as shown in Illustration
34: Specify Criteria Page No Filters Specified ...
The Review the Queries
that will be Optimized page appears next, as partially depicted in Illustration
35: Review the Queries that will be Optimized Page (Partial View)
Here we see every query
in the log (since we have specified no filters), grouped by number of occurrences,
for which average durations are displayed. We have the opportunity, at
this point, to eliminate individual queries from consideration, for added
flexibility. (We will leave them all selected for purposes of our practice
selections in place, click Next.
The Specify Storage
and Caching Options page of the Wizard appears next. Here we can
adjust the existing settings for storage mode and caching options.
We will leave the settings at default (solely MOLAP), as shown in Illustration
36: Specify Storage and Caching Options Page Default Settings
The Specify Object
Count page appears next.
Count button, to direct the Wizard to calculate object counts.
Counting commences, and
we quickly see the values appear, as partially depicted in Illustration 37.
37: The Specify Object Count Page (Partial View)