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
Note: Service Pack 3 updates are assumed for MSSQL
Server 2000, MSSQL Server 2000 Analysis Services, and the related Books
Online and Samples.
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
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.
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
2. Expand the Analysis
Servers folder by clicking the "+" sign to its immediate
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: A Sample Set of Databases Displayed within Analysis Manager
4. Expand the FoodMart2000
5. Expand the Cubes folder.
The sample cubes appear, as shown in Illustration 2.
2: The Sample Cubes in the FoodMart2000 Database
6. Right-mouse on the Warehouse
7. Select Usage
Analysis... from the context menu that appears, as shown in Illustration 3.
3: Select Usage Analysis ... from the Context Menu
The Welcome to the Usage Analysis Wizard dialog appears, as depicted
in Illustration 4.
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.
time of queries, ordered from the longest to the shortest run time.
frequency of queries, ordered from the most to the least frequent.
and the number of queries they have enacted, ordered from the most to least
queries sent per user.
distribution of the response time for all queries.
By Hour Graph
total number of queries processed, grouped together by hour.
By Date Graph
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.
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.
The viewer appears, displaying the Query Frequency Table
report, as shown in Illustration 6.
Illustration 6: The Query Frequency Table
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).
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
11. Click Finish
after reviewing the report.
The report closes, and we are returned to the Analysis Manager
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.
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
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.
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.