Featured Database Articles

Usage-Based Optimization in Analysis Services 2005 - Page 6

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.

1.
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.

2.
Expand the Cubes
folder that appears within the ANSYS043 Adventure Works DW tree.

3.
Expand the Adventure
Works cube that appears within the Cubes folder.

4.
Expand the Measure
Groups folder that appears within the Adventure Works cube.

The various Measure
Groups for the Adventure Works cube appear.

5.
Expand the Internet
Sales Measure Group that heads up the Measure Groups list for
the Adventure Works cube.

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.

Illustration
30: The Partitions for the Internet Sales Measure Group ...

NOTE:
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.

7.
Right-click
the Partitions folder.

8.
Select Usage
Based Optimization ... from the context menu that appears, as depicted in Illustration
31.

Illustration
31: Calling the Usage-Based Optimization Wizard ...

The Usage-Based
Optimization Wizard appears, beginning with the Welcome page, as
shown in Illustration 32.

Illustration
32: Usage-Based Optimization Wizard  Welcome Page

9.
Click Next.

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.

10.
Select the All
checkbox (on the column header, to the left of the Partition Name
label), as depicted in Illustration 33.

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.

Query Criteria Option

Optimization Selection is Based Upon:

Queries within date ranges

Date
range for queries. Date range type can be selected from:

Between  All queries between a desired Beginning date and Ending
date, inclusive

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

Query frequency

The
most commonly executed queries based upon percentage represented of all
queries.

12.
Leave the
number selections blank, putting no filters in place, as shown in Illustration
34.

Illustration
34: Specify Criteria Page  No Filters Specified ...

13.
Click Next.

The Review the Queries
that will be Optimized page appears next, as partially depicted in Illustration
35.

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
session).

14.
Leaving all
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.