Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Simple Cube Usage Analysis - Page 2
September 22, 2003
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.
4. Expand the FoodMart2000 database.
5. Expand the Cubes folder.
The sample cubes appear, as shown in Illustration 2.
6. Right-mouse on the Warehouse sample cube.
7. Select Usage Analysis... from the context menu that appears, as shown in Illustration 3.
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.
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.
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.
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.