Optimizing Microsoft SQL Server Analysis Services: Optimization Tools: Basic Usage

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, resembling that shown in Figure 6.

Figure 6: The Query Frequency Table Report in the Viewer

NOTE: Frequency information will vary, obviously, based upon local usage of the cube you are examining.

We see that the report we requested has appeared. The Query Frequency Table report displays query frequency information, sorted from most to least frequent. 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 many development and productions scenarios, if only to provide an indication of a need for further, more focused analysis of consumer usage. (We explore more specialized analysis alternatives in future articles).

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.

Using Filters to Refine 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 Figure 7.

Figure 7: Our Selection: The Query by Hour Graph

1. Select the Query by Hour Graph report.
    
2. Click Next.

The Select the Criteria to Filter This Report dialog appears.