Business Intelligence Architect Bill Pearson introduces simple
Usage-Based Optimization in MSSQL Server 2005 Analysis Services. In this
article, we practice employing the Usage-Based Optimization Wizard to fine tune
our aggregation design based upon cube usage statistics.
About the Series ...
article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to provide hands-on application of
the fundamentals of MS SQL Server Analysis Services, with each installment
progressively presenting features and techniques designed to meet specific real
- world needs. For more information on the series, please see my initial
article, Creating Our First
Note: To follow along with the steps we undertake, the following components,
samples and tools are recommended, and should be installed according to the
respective documentation that accompanies MSSQL Server 2005:
Server 2005 Database Engine
Server 2005 Analysis Services
Intelligence Development Studio
Server 2005 sample databases
The Analysis Services
Tutorial sample project and other samples that are available with the
installation of the above.
successfully replicate the steps of the article, you also need to have:
within one of the following:
Read permissions within any SQL
Server 2005 sample databases we access within our practice session, if
Note: Current Service Pack updates are assumed for the operating system, MSSQL
Server 2005 ("MSSQL Server"), MSSQL Server 2005 Analysis
Services ("Analysis Services"), MSSQL Server 2005 Reporting
Services ("Reporting Services") and the related Books
Online and Samples. Images are from a Windows 2003
Server environment, but the steps performed in the articles, together with
the views that result, will be quite similar within any environment that
supports MSSQL Server 2005 and its component applications.
In this lesson, we revisit usage-based optimization, a
subject that we undertook in my article MSAS
Administration and Optimization: Simple Cube Usage Analysis, in September of 2003, and MSAS
Administration and Optimization: Toward More Sophisticated Analysis in October of 2003. In the
earlier articles, we discovered that, among several tools that Microsoft SQL Server 2000 Analysis
Services offered 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, leveraged the usage-based optimization features
of Analysis Services. They made it easy to perform basic performance
enhancement of our cubes based upon their usage by information consumers. As I
mentioned then, my experience is 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
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.
lesson, 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 have previously considered. 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
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.
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;
querying the cube;
with which a query was executed;
for the query;
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;
cube further by processing and manipulating data / creating Query Log
practice exercise, using the Usage-Based Optimization Wizard, to set aggregations
for our practice cube;
of the possible settings that are available to us, as we proceed through the
guided steps of the Wizard;
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
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.
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.
Let's get some hands-on practice with Usage-Based Optimization. To prepare, we will create a new Analysis Services database, based upon the existing Adventure Works DW sample database, to insulate the original sample database from modifications we will make. We will accomplish the creation of the "clone" database from within the SQL Server Management Studio, and then enable query logging to prepare for our work with the Usage-Based Optimization Wizard.
Create a Clone Analysis Services Database in SQL Server Management Studio
We will begin our preparation within SQL Server Management Studio, where we will create a clone of the sample Adventure Works DW database, which can be installed by anyone installing MSSQL Server 2005 Analysis Services.
1. Click the Start button.
2. Select Microsoft SQL Server 2005 within the Program group of the menu.
3. Click SQL Server Management Studio, as shown in Illustration 1.
The Connect to Server dialog appears.
4. Select Analysis Services in the Server type selector.
5. Type / select the server name / instance, if appropriate) into the Server name selector.
6. Supply authentication information, as required in your own environment.
The Connect to Server dialog appears, with the appropriate input for our local environments, similar to that depicted in Illustration 2.
Illustration 2: The Connect to Server Dialog, with Representative Settings
7. Click the Connect button to connect with the specified Analysis Services server.
The SQL Server Management Studio opens.
8. Within the Object Explorer (the leftmost pane of the Studio, by default), expand the server in which we are working, if necessary, by clicking the "+" sign to its immediate left.
9. Expand the Databases folder that appears underneath the expanded server.
10. Right-click the Adventure Works DW database.
11. Select Back Up... from the context menu that appears, as shown in Illustration 3.
Illustration 3: Right-click the Adventure Works DW Database Select Back Up ...
The Backup Database Adventure Works DW dialog appears.
12. Replace the default name that appears in the Backup file box with the following:
ANSYS043 Adventure Works DW.abf
13. Uncheck the Apply compression setting in the Options section.
14. Uncheck the Encrypt backup file setting that immediately follows.
The Backup Database Adventure Works DW dialog appears, as depicted in Illustration 4.
Illustration 4: The Backup Database Adventure Works DW Dialog Appears
15. Click OK to begin the backup.
The Backup Database Adventure Works DW dialog grays, as the Executing symbol in the Progress pane (lower left corner of the dialog) becomes active. The process may run several minutes depending upon the resources available on the local PC. Once completed, the dialog closes, returning us to the Management Studio.
We will next restore the same backup under a different name, to create a copy of the existing sample database a copy wherein we can make modifications without impairing the existing sample, which we may wish to use to complete tutorials included with MSSQL Server 2005 or elsewhere.
16. Within the Object Explorer, right-click the Databases folder underneath the Adventure Works DW database.
17. Select Restore... from the context menu that appears, as shown in Illustration 5.
Illustration 5: Right-click the Databases Folder Select Restore ...
The Restore Database dialog appears.
18. Click the Browse button to the right of the box (second from the top) labeled From backup file.
The Locate Database Files dialog appears.
19. Navigate to the following backup file (where we located it in our backup steps above):
ANSYS043 Adventure Works DW.abf
20. Click the file within the Select the file window, to place the file name into the File name box, as depicted in Illustration 6.
Illustration 6: Locate Database Files Dialog with Our Input ...
21. Click OK to accept the file path / name, and to close the Locate Database Files dialog.
We return to the Restore Database dialog, where we see the file we have selected appear in the From backup file box.
22. Type the following into the Restore database box immediately above the From backup file box:
ANSYS043 Adventure Works DW
The Restore Database dialog, with our input, appears as shown in Illustration 7.
Illustration 7: The Completed Restore Database Dialog
23. Click OK to initiate the restoration.
The Restore Database dialog grays, as the Executing symbol in the Progress pane, once again, becomes active. The process runs, and, once completed, the dialog closes, returning us to the Management Studio. Here we see the new ANSYS043 Adventure Works DW database appear in the Object Browser, as depicted in Illustration 8.
Illustration 8: The New Database Appears ...
NOTE: If the new database does not appear immediately, right-click the Databases folder and select Refresh from the context menu that appears, as shown in Illustration 9.
Illustration 9: Refreshing as Required ...
Having created the ANSYS043 Adventure Works DW database, we can now proceed with the practice portion of our session, and get some hands-on experience with the Usage-Based Optimization Wizard in Analysis Services 2005. In the next session, we will take an additional preparatory step: enabling the logging of query statistics, upon which the Wizard bases its optimization strategy.
Enable Query Logging to Gather Statistics
Usage-based optimization is based upon usage. We
therefore must capture usage statistics to have a basis for
optimization. We do this by enabling query logging within the Analysis
Server, as we shall see in the steps that follow.
the Analysis Server with which we are working.
from the context menu that appears, as depicted in Illustration 10.
Illustration 10: Select
Properties from the Context Menu ...
The Analysis Services
Properties dialog appears. A busy place, indeed, many settings of a default
and operational nature are maintained here. (My advice is to learn all that we
can about each entry within this table-like dialog, so as to know of its
existence when the time comes to address the utility that it offers). First,
we need to enable the logging of query statistics.
Scroll down to
the Log \ QueryLog \ CreateQueryLogTable entry in the table within the
Change the Value
setting for the entry, which is defaulted to false, to true,
via the selector provided, as shown in Illustration 11.
We next need to direct Analysis
Services as to where we wish to host the Query Log. Analysis
Services 2005 offers us a great deal of flexibility (an improvement over Analysis
Services 2000's provision of an MS Access database for this purpose,
which could be migrated to MSSQL Server). We will direct that the Query
Log database be created within the sample AdventureWorksDW
relational database, which underlies our new clone Analysis Services
database, ANSYS043 Adventure Works DW. This is a logical place to put
the Query Log for this session, but we are free to put it into any OLE-DB
/ .NET compatible data source.
In the Log
\ QueryLog \ QueryLogConnectionString row, immediately below the CreateQueryLogTable
row (where we assigned the value of true above), click the Value
box, and then click the box that appears to its immediate right (marked "..."),
as shown in Illustration 12.
Illustration 12: Beginning
Connection Setup for the Query Log Data Source
Connection Manager appears.
Leave the Provider
setting at its default of Native OLE DB \ SQL Native Client.
Select / type
the appropriate Server or Server / Instance combination into the Server
Mine is MOTHER1\M1MSSQL2K5,
as we see in illustrations throughout recent articles of the series.
In the Log on
to the server section of the Connection Manager dialog, make the
authentication selections appropriate to your environment.
I am using Windows
Authentication, and therefore select the respective radio button.
In the Connect
to a database section in the lower half of the dialog, in the selector box
labeled Select or enter a database name, select the AdventureWorksDW relational
The Connection Manager
dialog appears, with our input, similar to that depicted in Illustration 13.
Illustration 13: Connection
Manager Dialog, with our Input
Click the Test
Connection button to ascertain connectivity to the database.
settings, confirmation of connectivity appears in a message box, as shown in Illustration
Illustration 14: "Test
to close the message box.
to accept settings and to close the Connection Manager dialog.
We are returned to the Analysis
Services Properties dialog, where we see that the Value box of the Log
\ QueryLog \ QueryLogConnectionString row now contains a connection
string, courtesy of the Connection Manager. We have enabled the collection
of query statistics and established the location of their collection.
Next, we will make a couple of additional adjustments before putting the Usage-Based
Optimization Wizard, which depends upon the Query Log to perform its
work, into action.
In the Log
\ QueryLog \ QueryLogSampling row, which appears three rows below the Log
\ QueryLog \ QueryLogConnectionString row, change the Value from the
default of 10 to 1.
Here we are merely
increasing the sampling rate from "capture statistics from every
tenth query" to "capture statistics for each query,"
a step similar to those we took in previous articles with the Usage-based
Optimization Wizard as it existed in Analysis Services 2000.
(Although the setting was managed a bit differently, in the Write to log
once per [number] box within Analysis Services 2000, our intent
then, as it is now, was to simply allow the log to capture enough data to make the
procedural steps of our practice exercise meaningful.)
In the Log
\ QueryLog \ QueryLogTableName row, which appears in the row immediately
below the Log \ QueryLog \ QueryLogSampling row, modify the Value
from its default of OLAPQueryLog to the following:
Our settings, within the
rows of the Analysis Services Properties dialog we have visited, appear
as depicted in Illustration 15.
Illustration 15: Modified
Settings within the Analysis Services Properties Dialog
to enact the settings we have made above.
indicator appears in the bottom left corner of the dialog momentarily, and then
dialog closes. We are returned to the SQL Server Management Studio.
We can confirm the
creation of the new MSASQueryLog table within the AdventureWorksDW relational
database easily from the SQL Server Management Studio, where we can
access the relational world in combination with the OLAP world, as we shall see
in the next steps.
Connect button atop the Object Explorer.
Engine from the menu that appears, shown circled in Illustration 16.
Illustration 16: Connecting
to the SQL Server 2005 Database Engine
to Server dialog appears, just as we saw earlier when connecting to Analysis
correct Server or Server / Instance combination into the Server
appropriate authentication mode into the Authentication box that
immediately follows (with related details in the Password box, as
The Connect to Server
dialog appears similar to that depicted in Illustration 17.
Illustration 17: The
Connect to Server Dialog Appears
We return to the SQL
Server Management Studio, where we see the instance of the Database
Engine appear within the Object Explorer, underneath the Analysis
Server with which we have been working, as shown in Illustration 18.
Illustration 18: The
Database Engine Instance Appears within Object Explorer
Expand the Database
Engine Server by clicking the "+" sign to its immediate left, if
Expand the Databases
folder underneath the expanded server.
Expand the AdventureWorksDW
Expand the Tables
folder within the AdventureWorksDW database.
The tables within the
folder appear. Among them, we see the new MSASQueryLog table that we
created in our earlier steps, as depicted in Illustration 19.
Illustration 19: The
MSASQueryLog Table Appears in the AdventureWorksDW Database
The table is in place and
query logging is enabled. We are positioned to conclude our preparations, by
generating some sample queries, and to begin getting some hands-on practice
with the Usage-Based Optimization Wizard.
the Database Clone and Generate Statistics to be Captured by the Query Log
We need some log entries
upon which to base the work of the Usage-Based Optimization Wizard in
our practice example, so we will populate the table with some rows in a "quick
and dirty" manner by doing quick manipulations of
the data in the cube. First, we will process the new Analysis Services
database copy we have created.
the new Analysis Services database, ANSYS043 Adventure Works DW, which
we created earlier. (The database can be found within the Databases
folder of the Analysis Server instance, within Object Explorer.)
from the context menu that appears, as shown in Illustration 20.
The Process Database
dialog appears for Analysis Services database ANSYS043 Adventure Works
DW, as depicted in Illustration 21.
settings on the dialog at default, click OK to begin processing.
The Process Progress viewer
appears, logging the events of database processing as they occur. Once all
database objects are processed, we receive a Process Succeeded message
in the Status bar at the bottom of the viewer, as shown in Illustration
Illustration 22: Successful
Processing Completion is Indicated ...
Click the Close
button to dismiss the Process Progress viewer.
We are returned to the SQL
Server Management Studio, where next we will perform a few actions to
generate statistics in the Query Log table.
Expand the Analysis
Services database ANSYS043 Adventure Works DW, by clicking the "+"
sign to its immediate left within Object Explorer.
Expand the Cubes
folder that appears within the ANSYS043 Adventure Works DW tree.
the Adventure Works cube that appears within the Cubes folder.
from the context menu that appears, as depicted in Illustration 23.
Illustration 23: Select
Browse from the Context Menu ...
Works [Browse] tab appears within SQL Server Management Studio.
Here we will do a few browses to generate sample query statistics within the Query
within the Metadata pane by clicking the "+" sign to its
Expand the Internet
Sales measure folder that appears underneath the expanded Measures level.
related to Internet Sales appear.
Drag measure Internet
Sales Amount into the Data pane, dropping it into the area marked Drop
Totals or Detail Fields Here, as shown in Illustration 24.
Illustration 24: Drag
and Drop the Measure into the Data Pane ...
Drag measure Internet
Order Quantity from the Metadata pane to the Data pane,
dropping it to the right of the Internet Sales Amount.
Expand the Date
dimension within the Metadata pane.
Expand the Calendar
folder that appears underneath the expanded Date dimension.
Expand the Date.Calendar
hierarchy that appears within the expanded Calendar folder.
Drag member Calendar
Year into the Data pane, dropping it onto the area marked Drop
Column Fields Here, as depicted in Illustration 25.
Illustration 25: Drag
and Drop Calendar Year into the Column Fields of the Data Pane ...
Expand the Product
dimension within the Metadata pane.
Expand the Product
Categories hierarchy that appears underneath the expanded Product dimension.
Drag the Category
level, appearing underneath the Product Categories hierarchy, into
the Data pane, dropping it onto the area marked Drop Row Fields Here,
as shown in Illustration 26.
Illustration 26: Drag
and Drop Product Categories into the Row Fields of the Data Pane ...
The Data pane appears,
after our insertions, as partially depicted in Illustration 27.
Illustration 27: Our
Initial Browse in the Data Pane (Partial View)
Drill down on each
of the Product Categories by clicking the "+" sign to its
immediate left, as shown in Illustration 28.
Illustration 28: Drilling Down on
Product Categories ...
Drill down on
the Calendar Year CY 2003, to display the bi-annual levels H1 CY 2003
and H2 CY 2003.
Drill down on
level H2 CY 2003 one level further, to expose the underlying quarterly
levels Q3 CY 2003 and Q4 CY 2003, as depicted in Illustration
Illustration 29: Drilling
Down on Select Date Dimension Levels ...
In a manner
similar to the steps above, perform several browses involving different dimensions
/ dimension hierarchies and measures.
Close the Adventure
Works [Browse] tab, when finished.
Having provided for the
collection of a representative set of query statistics, we can now call the Usage-Based
Optimization Wizard into action and get some exposure to its operation.
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)
The Set Aggregation Options page appears next. This page is familiar to those of us who have used the Aggregation Design Wizard and varies only little from the equivalent page of the previous version of Analysis Services.
18. Under Design aggregation until, click the checkbox labeled Performance gain reaches, to select this aggregation option.
19. Input 75 % into the selector box to the immediate right of the Performance gain reaches label.
The Set Aggregation Options page appears as shown in Illustration 38.
Aggregation design begins, and continues until the desired optimization objective is reached. In the present case, we receive a message in the status bar at the bottom of the Set Aggregation Options page, indicating a count of the aggregations designed, along with the optimization level percentage reached and the estimated storage size of the cube, similar to that depicted in Illustration 39.
Keep in mind that aggregations are only designed by the Wizard; the affected partitions must be processed to physically create the aggregations that the Wizard has designed.
The Completing the Wizard page appears, providing an opportunity to review the partitions that are about to be optimized, once processing begins. We can elect to begin processing as the final step of the Usage-Based Optimization Wizard, or to defer processing until a later time. We will elect the former option at this point.
22. Click the checkbox labeled Process partitions immediately, in the bottom left corner of the Completing the Wizard page.
The Completing the Wizard page appears, as shown in Illustration 40.
Illustration 40: The Completing the Wizard Page
The Process Objects dialog opens. Here we can perform various batch settings and review, for a final time (and even remove some, or all of) the objects scheduled to be processed. The Process Objects dialog appears as depicted in Illustration 41.
Illustration 41: The Process Objects Dialog
24. Click OK to begin processing the partitions.
The Process Progress viewer appears, logging the events of the processing of the partitions as they occur. Once all scheduled partitions are processed, we receive a Process Succeeded message in the Status bar at the bottom of the viewer, as shown in Illustration 42.
Illustration 42: Successful Processing Completion is Indicated ...
25. Click the Close button to dismiss the Process Progress viewer.
We are returned to the SQL Server Management Studio. Usage-based optimization is completed.
26. Discard the clone Analysis Services database, ANSYS043 Adventure Works DW, as desired. (Right-click and Delete.)
27. Reset the Log \ QueryLog \ CreateQueryLogTable entry, within the Analysis Server Properties dialog for your local server, to "false," if desired, to disable query logging.
28. Select File ---> Exit, when ready to close SQL Server Management Studio.
We have completed our practice steps with an Analysis Services 2005 performance tuning tool, the Usage-Based Optimization Wizard. The Wizard is similar to the tool of this name within Analysis Services 2000, with performance and utility enhancements in evidence. The Usage-Based Optimization Wizard allows us to optimize the aggregations for our MSAS cubes partitions based upon queries that have previously been executed against the cube. Our ability to discriminate between common, frequently run queries and more ad hoc, less representative queries, when deciding the population to use as a basis for tuning the cube's performance with future queries, allows us to customize the action of the tool a bit beyond what we might expect of more typical wizardry. One of numerous characteristics of the process that remains unchanged since Analysis Services 2000 is the role of the administrator within this flexible, albeit guided tuning event: making the correct selections here can certainly inject the element of artistry into what might otherwise appear to be an objective evolution.
In this lesson, we revisited the Usage-Based Optimization Wizard, which remains a formidable tool in our Analysis Services 2005 administration toolset. We noted that the Usage-Based Optimization Wizard embellishes the effectiveness of the Usage Analysis (going significantly farther than the generation of simple reports) and Storage Design (allowing for up-to-date, usage-based optimization) Wizards. The Usage-Based Optimization Wizard offers 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 passes, and as information is collected, from which meaningful statistics can be derived.
In this article, we examined the operation of the Usage-Based Optimization Wizard within a context of aggregation design. Our practice exercise included preparation steps, within which we created a copy of a sample Analysis Services database for use in our practice exercise, enabled the Analysis Server Query Log to capture query statistics, processed the clone database, and then manipulated data within a cube therein to create Query log entries. We then performed a procedure whereby we set aggregations for our designated practice cube with the Usage-Based Optimization Wizard. Throughout the guided steps of the Wizard, we examined each of the possible settings that it makes available to us, and commented upon general optimization concepts as we proceeded through the practice example.
» See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.