Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Toward More Sophisticated Analysis

Monday Oct 20th 2003 by William Pearson

Explore more sophisticated Usage Analysis reporting options for your cubes. Author Bill explores the Query Log, including its modification and use as a direct reporting source.

About the Series ...

This is the sixteenth 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 First Cube.

Note: Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples.


We learned in our last lesson, MSAS Administration and Optimization: Simple Cube Usage Analysis, that Microsoft SQL Server 2000 Analysis Services ("MSAS") provides the Usage Analysis Wizard to assist us in the maintenance and optimization of our cubes. We noted that the Usage Analysis Wizard allows us to rapidly produce simple, on-screen reports that provide information surrounding a cube's query patterns, and that 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.

As I stated in Lesson 15, however, I often receive requests from clients and readers, asking how they can approach the creation of more sophisticated reporting 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. Yet 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.

Each of these more sophisticated analysis and reporting needs can be met in numerous ways. In this lesson, we will we will examine the source of cube performance statistics, the Query Log, discussing its location and physical structure, how it is populated, and other characteristics. Next, we will discuss ways that we can customize the degree and magnitude of statistical capture in the Query Log to enhance its value with regard to meeting more precisely our local analysis and reporting needs; we will practice the process of making the necessary changes in settings to illustrate how this is done. Finally, we will discuss options for generating more in-depth, custom reports than the wizard provides, exposing ways that we can directly obtain detailed information surrounding cube processing events in a manner that allows more sophisticated selection, filtering and display, as well as more customized reporting of these important metrics.

At the Heart of Usage Analysis for the Analysis Services Cube: The Query Log

Along with an installation of MSSQL Server 2000 Analysis Services comes the installation of two independent MS Access databases, msmdqlog.mdb, and msmdrep.mdb. By default, the location in which these databases are installed is [Installation Drive]:\Program Files\Microsoft Analysis Services\Bin. The msmdrep.mdb database houses the repository, and will not be the focus of this lesson. We will be concentrating upon the msmdqlog.mdb database, the home of the Query Log where the source information for our usage analysis and reporting is stored.

Structure and Operation of the Query Log

A study of msmdqlog.mdb reveals that it consists of a single table, named, aptly enough, QueryLog. Illustration 1 depicts the table within the database, design view, so that we can see it's layout for purposes of discussion.

Illustration 1: The Query Log Table, Design View, within Msmdqlog.mdb

NOTE: Making a copy of msmdqlog.mdb before undertaking the steps of this lesson, including entering the database simply to view it, is highly recommended to avoid issues with an operating MSAS environment, damaging a production log, etc.

The Usage-Based Optimization Wizard and Usage Analysis Wizard (see Lesson 15 for a discussion) rely on the Query Log, as we learned in our last session. As we can see, the log is composed of several relatively straightforward fields. The fields, together with their respective descriptions, are summarized in Table 1.




The name of the database used in the query


The name of the cube used in the query


The name of the user that ran the query


A numeric string indicating the level from each dimension used to satisfy the query


A string indicating the data slice for the query


The time the query began


The length of time (in seconds) of the query execution


The number of different multidimensional OLAP (MOLAP) partitions that were used to satisfy the query


The number of different relational OLAP (ROLAP) partitions that were used to satisfy the query


The sampling rate at the time the query was executed

Table 1: The Fields of the Query Log

In lockstep with a review of the fields from a description perspective, we can view sample data in Illustration 2, which depicts the same table in data view.

Click for larger image

Illustration 2: The Query Log Table, Data View, within Msmdqlog.mdb

Although each of the fields has a great deal of potential, with regard to analysis and reporting utility (the first three could be seen as dimensions), the fourth, Dataset, can be highly useful with regard to the information that it reveals about cube usage. The cryptic records within this column represent the associated levels accessed for each dimensional hierarchy within the query. An example of the Dataset field ("121411") appears in the sample row depicted in Illustration 3.

Illustration 3: Example of the Dataset Field

While we won't go into a detailed explanation in this lesson, I expect to publish an article in the near future that outlines the interpretation of the digits in the Dataset field. We will trace an example Dataset field's component digits to their corresponding components in the respective cube structure, along with more information regarding report writing based upon the Query Log in general. Our purpose here is more to expose general options for using the Query Log directly to generate customized usage analysis reports.

Additional fields provide rather obvious utility in analyzing cube usage, together with performance in general. The Slice field presents information, in combination with Dataset, which helps us to report precisely on the exact points at which queries interact with the cube. These combinations can provide excellent access and "audit" data. To some extent, they can confirm the validity of cube design if, say, a developer wants to verify which requests, collected during the business requirements phase of cube design, are actually valid, and which, by contrast, might be considered for removal from the cube structure based upon disuse, should the time arrive that we wish to optimize cube size and performance by jettisoning little-used data.

StartTime and Duration provide the ingredients for evolved performance trending, and act as useful statistics upon which to base numerous types of administrative reports, including information that will help us to plan for heavy reporting demands and other cyclical considerations. MOLAPPartitions and ROLAPPartitions, which provide counts on the different multidimensional OLAP or relational OLAP partitions, respectively, that were used to retrieve the specified query results, can also offer great advanced reporting options, particularly in the analysis / monitoring of partitioning scheme efficiency, and the related planning for adjustments and so forth.

Finally, SamplingRate displays the setting in effect for automatic logging of queries performed by MSAS. This appears in Illustration 2 at its default of 10. The setting can be changed, however, as we shall see in the next section.

Customizing Query Log Capture

Both of the usage-based analysis and optimization processes provided in MSAS (via the Usage Analysis Wizard and the Usage-Based Optimization Wizard, as we learned in our last lesson) perform their functions based upon statistics captured in the Query Log. Within the MSAS Server Properties dialog box, which contains options that control user interfaces, server environment, processing, logging, and add-ins, we can make the query logging process more frequent than the default of one-in-every-ten queries. For that matter, from the Properties settings, we can:

  • Stop and start logging (we can leave it stopped entirely, if we desire);
  • Clear the log;
  • Change the logging interval (Valid range is an integer between 1 and 10,000).

Although somewhat peripheral to our immediate considerations, it might be useful to mention that we can also manage the automatic logging of processing messages, directing the server to enable the logging of the messages to a log file for which we can designate a file path and name.

While setting the logging interval too low might degrade performance in a busy production environment, the logging interval setting for a development environment would typically be a lower number than 10; this would obviously allow us to capture more voluminous statistics to support intensive analysis of cube performance and use prior to optimizing it for final delivery to information consumers.

Let's practice the steps involved in setting this property as if we were in a development scenario.

1.  Start Analysis Manager.

2.  Expand the Analysis Servers folder by clicking the "+" sign to its immediate left.

Our server(s) appear.

3.  Right-click the desired server (mine appears as MOTHER in the illustrations).

4.  Select Connect ... from the context menu, as depicted in Illustration 4, as necessary, to connect to the server.

Illustration 4: Select Connect ... from the Context Menu

A Connecting to the Analysis Server message briefly appears, and then the server icon displays the small, green arrow to its lower right, indicating that a connection has been established, as shown in Illustration 5.

Illustration 5: The Icon Indicates Active Connection

5.  Right-click the server again.

6.  Click Properties from the modified context menu that appears, as shown in Illustration 6.

Illustration 6: Selecting Server Properties on the Context Menu

The Properties dialog appears, defaulted to the General tab.

NOTE: Making a backup of the msmdqlog.mdb before undertaking any of the modifications noted below is highly recommended to avoid issues with an operating MSAS environment, damaging a production log, etc.

7.  Click the Logging tab.

Logging frequency is set in the Write to Log Once per [integer] Queries box.

8.  Change the logging frequency to "1."

Note that the Clear Log button is here, as well. We can choose to clear the log of all entries with this option.

9.  Click OK to apply the frequency change.

A message box appears reminding us that we must restart Analysis Services for the logging frequency change to take effect.

10.  Click OK to close the message box.

11.  Select Console --> Exit from the console main menu.

Analysis Services closes.

To see the results of our change in logging frequency, we can now restart Analysis Services (performing steps 1 through 4 above will do the trick), and then run a test query or two via the MDX Sample Application. If we then look into msmdqlog.mdb, we can see the new entries, containing the integer "1" in the SamplingRate field, for the respective queries.

To give a common example of how lowering the Sampling Rate might be useful, let's consider a typical approach to optimizing performance based upon usage. We often begin the process by creating partitions with zero aggregations. We next adjust query logging to log every query for a period of time, to assist us in capturing typical usage patterns. We then use the Usage-Based Optimization Wizard to design aggregations appropriate to the usage. In short, we adjust the Sampling Rate to increase the size of the sample upon which we run the Optimization Wizard, presumably to more closely bring actual usage patterns into consideration within the process.

Now that we have an understanding of the workings of the Query Log, we will discuss options for producing customized reports to meet our business needs.

Direct Reporting for Sophisticated Utilization Analysis

As most of us are certainly aware, we can rely upon the fact that the options for reporting choices for cube utilization analysis are plentiful. Whether we retain the practice of maintaining the log in the original MS Access database format, or whether we move the statistical data it contains, in whole or part, from the Access environment a larger RDBMS (like MSSQL Server), house it in an administrative reporting warehouse / mart, or some other such arrangement. We will find that many common packages can be used in identical fashion to report from these stores via ODBC and other channels. I have even created cubes (both for Cognos PowerCubes and MSAS cubes)for larger clients from the statistical information regarding cube usage, together with statistics that come from other diagnostic sources, generating robust and useful mechanisms for tracking cube usage and performance from many perspectives. (I expect to publish articles that detail more of the specifics of some of these administrative database and reporting options, in later articles in this series and elsewhere).

Because the concepts are essentially the same, we will explore a basic approach to reporting from a copy of the msmdqlog.mdb data source. Our "medium" for reporting will be a combination of MS Access and a PivotTable view, but I have created similar reporting processes in the past using a wide range of business intelligence tools, including Cognos, Business Objects, Crystal Reports, ProClarity, MicroStrategy, Microsoft Office, and, most recently, Microsoft Reporting Services, among other less common tools.

Our focus surrounds the entrainment of the data from the log, for use in a report in any of a multiple choice of vendors, and we will not spend any time with formatting considerations, aggregating data in the report, etc., as most of us will know the steps required to create robust and attractive reports within our own choices of solutions. Other of my articles elsewhere deal with the specifics of working with various reporting options, and I will try to make the approach here more generic, focusing on the "hows" of connection versus the details of report writing.

Create a Query in MS Access

We will first create a query in MS Access upon which we can base a reporting mechanism; this process could be duplicated as stored procedure in an enterprise-level RDBMS, or we could create the query at the reporting solution level and run the underlying SQL against the tables of the database directly, among other approaches. To keep it simple, let's set up an illustrative query in MS Access, mentally extrapolating the process to other solutions where useful, to act as the source of data for a sample report.

1.  Go to the Start button on the PC, and then navigate to the Microsoft Access icon.

2.  Click the icon to start MS Access.

MS Access opens, and may display the initial dialog. If so, close it.

3.  Select File -> Open from the top menu, and navigate to the copy of the msmdqlog.mdb database that we discussed making earlier in the lesson (the file might also be accessed from the Open a File menu atop the task pane, if it has not been disabled previously, at the right side of the main window in MS Access 2002.)

4.  Select msmdqlog.mdb.

5.  Click Open.

We arrive at the Database Window.

6.  Click Queries, under Objects in the Database window.

7.  Click New on the Database window toolbar, just above the Objects pane.

The New Query dialog appears, as shown in Illustration 7.

Illustration 7: The New Query Dialog

8.  Ensuring that the Design View option is selected, click OK.

The Select Query dialog appears by default, with the Show Table dialog appearing in front, (containing the sole QueryLog table) as shown in Illustration 8.

Illustration 8: The Select Query Dialog (Compressed View), with Show Table Dialog Foremost

9.  Select the QueryLog table, by highlighting it, and then clicking the Add button (a double-click will also suffice), placing it into the Select Query dialog.

10.  Click the Close button on the Show Table dialog to close it.

The Select Query dialog, upper portion, displays the newly added table, appearing as shown in Illustration 9.

Illustration 9: The Select Query Dialog, with Selected Table (Compressed View)

For the QueryLog table, double-click each of the indicated fields below, to place it in the corresponding field of the matrix in the bottom half of the Select Query dialog.

  • MSOLAP_Cube
  • MSOLAP_User
  • Dataset
  • StartTime

The Select Query dialog displays the newly added table and fields, appearing as shown in Illustration 5.

Illustration 10: The Select Query Dialog, Selected Table and Fields (Compressed View)

We will perform some added steps to make the query a more useful reporting data source at this stage.

11.  Click the Criteria field for the MSOLAP_Cube column, to place the cursor into the field.

12.  Type the following expression into the Criteria field:


The purpose of this expression is to filter the query selection to contain only log entries for the Warehouse cube. We could easily make this a prompted value, asking that the cube to be reported upon be selected at run time. For a tutorial where such prompting is covered, see my article Reporting in MS Access: Grouped Transactional Report Part I, a tutorial in the DatabaseJournal MS Access for the Business Environment series.

13.  Click the Criteria field for the StartTime column, to place the cursor into the field.

14.  Type the following expression, substituting dates that are appropriate for your local dataset, into the Criteria field:

Between #10/1/2002# And #10/31/2002#

The purpose of this expression is, again, to act as a very basic filter, which, just as the MSOLAP_Cube column filter that we established above, could easily be enhanced to drive a prompt for a runtime beginning date, end date, or both, instead of the "hard coded" dates we have supplied above for the sake of simplicity. The general idea, in any case, is to provide criteria to serve as the basis for some of the cube usage statistics that we display in the report.

Let's save our work at this juncture.

15.  Select File -> Save As.

The Save As dialog appears.

16.  Type the following into the "Save Query 'Query1' to:" box:

	Warehouse Cube Activity

The Save As dialog should now appear as shown in Illustration 11.

Illustration 11: The Save As dialog, with New Query Name

The Select Query dialog should now appear as partially shown in Illustration 12.

Illustration 12: The Select Query Dialog with Alterations

Now, let's run the query and examine the result set that it returns.

17.  Select Query --> Run from the main menu.

The query executes, and then returns a data set which should appear similar to that displayed in Illustration 13 (the data that appears in your own results will obviously differ).

Illustration 13: The Data Set Returned by Our New Query

We have now created and saved a query in the surrogate MS Access database. This query will serve as the data source for the report that we will build in following sections to demonstrate the steps involved.

18.  Click File --> Close to close the Warehouse Cube Activity query.

19.  Click Yes, if prompted to save the layout of query Warehouse Cube Activity.

We are returned to the Database window, Queries view, where we see the new query appear.

Create the PivotTable View

Now that we have put a query in place to act as a data source, we are ready to set about the creation of a sample report. In our present case, we will create a straightforward PivotTable view to act as our report, keeping in mind, as we noted before, that any number of more elegant, robust, and enterprise-oriented solutions might be more appropriate in your own production environment.

Remaining within the Queries section of the Database window, let's take the following steps to begin construction of our new PivotTable view.

1.  Single-click the new Warehouse Cube Activity to select it

2.  Click Open on the Database window toolbar.

The query results set appears within the main window, as shown in Illustration 14.

Click for larger image

Illustration 14: The Database Window

3.  Select View --> PivotTable View from the main menu, as shown in Illustration 15.

Illustration 15: Select View --> PivotTable View

After a few seconds, a blank PivotTable appears, with a floating PivotTable Field List appearing in the foreground, as shown in Illustration 16. Note the appearance of the two additional fields that are based upon the StartTime field.

Illustration 16: A Blank PivotTable View Appears

As I suggested in the DatabaseJournal article Create a PivotTable View in Access, the blank PivotTable offers an excellent "conceptual" view of the basic makeup of a PivotTable. With a PivotTable we can create views of our data that strongly resemble, but far out-power, a cross-tab query. The PivotTable allows us to define the data values that we wish to occupy multiple rows, columns, pages (or "layers") and summaries.

We can see above that the center of the table contains numeric data ("measures"), while the rows and columns present (often hierarchical) dimensional data. We will see a simple illustration of how these areas of the conceptual "map" can be populated with our cube usage analysis data in the steps that follow.

Let's define our view to effectively present the data set returned from our new query.

4.  If the PivotTable Field List does not appear, initialize it clicking the Field List button, shown in Illustration 17, on the toolbar.

Illustration 17: The PivotTable Field List Button

5.  In the Field List, click MSOLAP_Cube to highlight it.

6.  Select Filter Area in the selector box, to the right of the Add to button (at the bottom of the Field List), as shown in Illustration 18.

Illustration 18: The PivotTable Field List with our Selection

7.  Click the Add to button.

8.  In the Field List, click MSOLAP_User to highlight it.

9.  Select Row Area in the selector box, to the right of the Add to button.

10.  Click the Add to button.

11.  In the Field List, click StartTime to highlight it.

12.  Select Detail Area in the selector box, to the right of the Add to button.

13.  Click the Add to button.

The PivotTable view (with data appropriate to your own location) should resemble that shown in Illustration 19.

Illustration 19: The PivotTable with Our Field Assignments

NOTE: If the view appears, instead, with a "roll up" perspective that includes empty fields in the place of the StartTime column shown in Illustration 19 above, click the "+" sign to the right of the first MSOLAP_User name (shown as ISLO1\Administrator in Illustration 20).

Illustration 20: Expanding the StartTime Field

The dates of cube query events appear, giving us a view of StartTime by user.

14.  Right-click the StartTime column heading in the PivotTable view.

15.  Select Properties from the context menu that appears.

The Properties dialog appears.

16.  Select the Captions tab.

17.  Type the following into the Caption box:

Query Event

18.  Click the Bold format button to make the Caption bold.

19.  Click the Centered alignment option to center the Caption in the view.

The Properties dialog, Caption tab, appears as depicted in Illustration 21.

Illustration 21: The Properties Dialog, Caption Tab, with Caption and Font Modifications

20.  Close the Properties dialog.

The PivotTable view appears similar in structure (data, again, will differ) to that shown in Illustration 22.

Illustration 22: Completed PivotTable View

And so we see that reporting upon the Query Log is a relatively simple process, once a data connection is established between the reporting tool selection and the data source.

21.  Save the PivotTable View as desired.

As we stated before, this illustration is only an attempt to show the process involved with entraining the data in the Query Log into a report. (Immediate improvements might include adding counts to the report, and thus supporting meaningful totals with numerous users, and a host of other possibilities.) In future articles, as I noted earlier, I will present more sophisticated reports, based upon data sources with more advanced usage, performance and other statistics. The demand is evident, in discussions with my clients and readers, for the capability to trend cube performance, create user access reports, and so forth and I will focus on some of those needs, together with ways to meet them. I will attempt to accomplish this over the months to come, with different reporting tools after we have exposed other components of the statistical data source, of which the Query Log is only a part.

Summary ...

In this lesson, MSAS Administration and Optimization: Toward More Sophisticated Analysis, we introduced the general need for more sophisticated analysis than that supported by the simple reports created by the Usage Analysis Wizard that we explored in our previous lesson. We examined the source of cube performance statistics, the Query Log, discussing its location and physical structure, how it is populated, and other characteristics. We then discussed ways to customize the degree and magnitude of statistical capture in the Query Log to enable it to meet more precisely our local analysis and reporting needs; we practiced the process of making the necessary changes in settings to illustrate how this is accomplished. Finally, we discussed options for generating more in-depth, custom reports than the Usage Analysis Wizard provides, exposing ways (and practicing with a simple example) that we can directly obtain detailed information surrounding cube processing events in a manner that allows more sophisticated selection, filtering and display, as well as more customized reporting of these important metrics.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Mobile Site | Full Site