Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Simple Cube Usage Analysis

Monday Sep 22nd 2003 by William Pearson

Perform simple cube usage analysis. In this article, author Bill Pearson introduces the Usage Analysis Wizard for generating "quick and dirty" cube processing statistics, then looks ahead to other, more robust analysis options.

About the Series ...

This is the fifteenth article of the series, Introduction to MSSQL Server 2000 Analysis Services. As I stated in the first article, Creating Our First Cube, the primary focus of this series is an introduction to the practical creation and manipulation of multidimensional OLAP cubes. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services ("MSAS"), with each installment progressively adding features and techniques designed to meet specific real-world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.

Note: Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples.


Microsoft SQL Server 2000 Analysis Services offers us many tools to assist in the maintenance and optimization of our cubes. Two of these tools highlight the usage-based optimization features of Analysis Services, and the ease with which we can enhance performance of our cubes based upon their usage by information consumers. My experience has been that, regardless of the design effort invested in any given business intelligence application, specifically 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.

The two tools to which I refer are the Usage Analysis Wizard and the Usage-Based Optimization Wizard. The Usage Analysis Wizard allows us to rapidly produce simple, on-screen reports that provide information surrounding a cube's query patterns. This information can be useful in helping us to decide whether to consider making changes to cube design to optimize it (say, prior to taking it from development to production). The cube activity metrics generated by the wizard have a host of other potential uses, as well, such as the provision of a "quick and dirty" means of trending cube processing performance over time after the cube has entered a production status.

The Usage-Based Optimization Wizard embellishes the effectiveness of the Storage Design Wizard, and equips us to go significantly farther than the generation of simple reports. The 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 information is collected from which meaningful statistics can be derived. The Usage-Based Optimization Wizard lies beyond the scope of this lesson, but we will introduce it in a later article, where we can devote the time it deserves to cover its various facets.

In this lesson, we will first put the Usage Analysis Wizard to work in its simplest form, examining the initial reporting options that it offers us. We will then explore the ways we can modify the on-screen reports at run time, selecting from a fixed set of filters to limit the results they present. Next, we will modify one of the standard reports to examine how we can tailor members of the set to meet our needs more closely. Finally, we will discuss the virtually inevitable requirement for going beyond the on-screen reporting set, and producing more sophisticated analysis reports, looking forward to the subject of our next lesson.

Monitoring Cube Performance with the Usage Analysis Wizard

The Usage Analysis Wizard provides us a quick means of examining key cube activities by producing our choice of six pre-defined, on-screen reports. As we will see in later sections, the data upon which the reports draw resides in the query log that is generated / populated automatically, as a part of cube processing, based upon setpoints that we can adjust. We can learn about the reports that we can generate easily through a hands-on look at the wizard in action.

Launching and Using the Wizard

We can call the Usage Analysis Wizard directly from inside Analysis Manager to generate a report with a few clicks of the mouse.

1.      Start Analysis Manager.

2.      Expand the Analysis Servers folder by clicking the "+" sign to its immediate left.

Our server(s) appear.

3.      Expand the desired server (mine appears as MOTHER in the illustrations).

Our database(s) appear, in much the same manner as shown in Illustration 1.

Illustration 1: A Sample Set of Databases Displayed within Analysis Manager

4.      Expand the FoodMart2000 database.

5.      Expand the Cubes folder.

The sample cubes appear, as shown in Illustration 2.

Illustration 2: The Sample Cubes in the FoodMart2000 Database

6.      Right-mouse on the Warehouse sample cube.

7.      Select Usage Analysis... from the context menu that appears, as shown in Illustration 3.

Illustration 3: Select Usage Analysis ... from the Context Menu

The Welcome to the Usage Analysis Wizard dialog appears, as depicted in Illustration 4.

Illustration 4: The Welcome to the Usage Analysis Dialog

We see, within this dialog, the six pre-defined reports from which we can choose. Notice that clicking on any report title populates the Description field below with a short summary of the purpose of the report. The six reports and their descriptions are summarized in Table 1.

Report Selection


Query Run-Time Table

The run time of queries, ordered from the longest to the shortest run time.

Query Frequency Table

The frequency of queries, ordered from the most to the least frequent.

Active User Table

Users and the number of queries they have enacted, ordered from the most to least queries sent per user.

Query Response Graph

A distribution of the response time for all queries.

Query By Hour Graph

The total number of queries processed, grouped together by hour.

Query By Date Graph

The total number of queries sent, grouped together by date.

Table 1: The Six Preformatted, On-Screen Reports

We will select the Query Frequency Table report for our initial walkthrough of the wizard.

8.      Select the Query Frequency Table report.

9.      Click Next.

The Select the Criteria to Filter This Report dialog appears, as shown in Illustration 5.

Illustration 5: The Select the Criteria to Filter This Report Dialog

For now, let's leave all settings at default, to keep focused on the general operation of the wizard; we will return to this dialog in a later section. This means that we are about to execute an unfiltered report.

10.  Click Next.

The viewer appears, displaying the Query Frequency Table report, as shown in Illustration 6.

Illustration 6: The Query Frequency Table Report in the Viewer

We see that the report we requested has appeared. The Query Frequency Table report displays query frequency information, sorted from most frequent to least. Although this report, like the other pre-defined, on-screen reports, is quite simple, a feel for the dimensions / combinations of the dimensions that are being queried might prove useful in several development and productions scenarios, if only to provide an indication of a need for further, more focused analysis. (We explore more specialized analysis alternatives in our next lesson).

While the on-screen report cannot be saved, it offers us the capability to Delete Records. Selecting this option will, in fact, delete the logged queries we have analyzed in our report from the query log, meaning that they will not be available for future analysis, unless steps are taken to first preserve them elsewhere. (Keep this in mind before becoming trigger-happy with the Delete Records button.)

11.  Click Finish after reviewing the report.

The report closes, and we are returned to the Analysis Manager console.

Refining the On-Screen Reports

As we noted in our initial walkthrough of the Usage Analysis Wizard, we are offered an opportunity to filter any report we choose using various offered criteria. Let's examine these criteria and perform a filtering procedure on an illustrative report selection.

1.      Right-mouse on the Sales sample cube.

2.      Select Usage Analysis... from the context menu that appears, as we saw in our walkthrough above.

The Welcome to the Usage Analysis Wizard dialog appears.

This time, we will select the Query by Hour Graph, as shown in Illustration 7.

Illustration 7: Our Selection: The Query by Hour Graph

3.      Select the Query by Hour Graph report.

4.      Click Next.

The Select the Criteria to Filter This Report dialog appears.

As we have discovered, we are offered an opportunity to set criteria for filtering the results that are returned for query usage analysis in the report we have specified in the previous dialog. By setting filter criteria, we are able to place restrictions on the data that is returned, via the Usage Analysis Wizard, from the query log, which is the source of the cube data we are analyzing (we will discuss the log, and mention other options for its use, in our next session together). Available filter criteria, and a brief description of what each entails, are included in Table 2.

Filter Criteria


Queries for the dates

Select this criteria item to apply date filters. Select Between (the default, if this criteria item is activated), Before or After, then supply the date(s) as appropriate. Date selection is assisted by the presence of the calendar control, which is activated by clicking the ellipses (...) button.

Queries by these users

Select this item to add users / groups of users to a list, upon which the filter is based.

NOTE: User Roles must be defined before the Add button will deliver any but the Everyone selection. Filtering by users is ineffective until User Roles are defined.

Queries that ran more than

Select this item to filter by a number of times a query has been executed. Only queries that have been executed more than the selected frequency threshold are returned in the report.

Queries that took longer than

Select this item to report only queries whose run times exceeded the time threshold (number of minutes / seconds) input.

Table 2: Filter Criteria Available in the Usage Analysis Wizard

As we noted earlier, this step is optional. The report we selected in the previous dialog will be returned with no filters if we simply leave the criteria boxes in the dialog unchecked / uncompleted, and proceed by clicking Next.

5.      Select the Queries That Ran More Than option by clicking the checkbox to its left.

6.      Select 6 in the "times" selector box.

We have indicated that we wish to limit the Query by Hour Graph report to include only those queries that have been executed more than six times, as depicted in Illustration 8.

Illustration 8: The Select the Criteria to Filter This Report Dialog with Our Selection

7.      Click Next.

The viewer appears, displaying the Query by Hour Graph report, as shown in Illustration 9.

Illustration 9: The Filtered Query by Hour Graph Report in the Viewer

The Query by Hour Graph report displays, after filtering out queries that have been executed 6 times or less, the total number of queries processed. The queries are grouped and displayed by the respective hours of day at which they were processed. Even though the example display in Illustration 9 displays minimal data, we can still get a feel for how the results are presented. (Keep in mind that your results may differ dramatically, depending on the history of the cube upon which you run the Usage Analysis Wizard.)

8.      Click Finish to close the wizard, and to return to the Analysis Manager console.

In Search of More Sophisticated Approaches to Utilization Analysis

I often receive e-mails and calls from clients, as well as from members of my general audience, asking how they can approach the creation of a more sophisticated report to assist in their usage analysis pursuits. This is sometimes based upon a need to create a report similar to the pre-defined, on-screen reports, but in a way that allows for printing, publishing to the web, or otherwise delivering report results to information consumers. Moreover, some users simply want to be able to design different reports that they can tailor themselves, to meet specific needs not addressed by the Usage Analysis Wizard's relatively simple offerings. Others want a combination of these capabilities, and / or simply do not like the rather basic user interface that the wizard presents, as it is relatively awkward, does not scale and so forth.

All of these needs can be met in numerous ways, including the use of multiple external reporting options. In our next lesson, we will take a look at approaches to the need for more sophisticated analysis, exposing the data source itself, and how we might access it in addressing these needs.

Summary ...

In this lesson, Simple Cube Usage Analysis, we introduced the topic of usage analysis for our multidimensional cubes, and discussed its importance as a contributor to performance enhancement. We contrasted the Usage Analysis Wizard with the Usage-Based Optimization Wizard, and then focused on the Usage Analysis Wizard as a "quick and dirty" means of viewing cube processing activity statistics in both development and production. Next, we modified an illustrative standard report, examining options for how we can tailor our selections to meet our needs more closely.

Our hands-on examination of the Usage Analysis Wizard in its simplest form, together with a discussion of the somewhat limited on-screen report set it offers, undoubtedly leads the experienced developer and operator to foresee the inevitable need to extend our reporting capabilities for various business reasons. In our next lesson, we will examine the source of cube performance statistics; we will expose ways that we can directly obtain more detailed information surrounding cube processing events in a manner that allows more sophisticated selection and filtering. Furthermore, we will discuss options for more customized reporting of these metrics, as well as a wider range of delivery choices.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Mobile Site | Full Site