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

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.

Our server(s) appear.

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

The sample cubes appear, as shown in Illustration 2.

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

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

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

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.

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

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