Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 22, 2003

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

By William Pearson

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.

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM