Introduction to the Analysis Services 2005 Query Log

Structure and Operation of the Query Log

As we have seen in other articles, and have emphasized in the sections above, the Query Log lies at the heart of Usage Analysis for the Analysis Services Database and some of its child objects, including cubes. As we have also previously noted, the Query Log captures details about the queries that have been enacted upon the server by client applications. We have prepared the Query Log for examination in this section, having selected the creation and maintenance of the MSSQL Server table incarnation versus the file option, which we mentioned was possible in passing earlier.

A study of the MSSQL Server table, which we named ANSYS049_MSASQueryLog, reveals some differences over its counterpart in the previous version of MSSQL Server Analysis Services. Unlike its SQL Server Analysis Services 2000 predecessor (by default an MS Access database named msmdqlog.mdb), we can name the Query Log table whatever we direct in the Properties settings for the Analysis Server under consideration, and begin with an MSSQL Server table (rather than converting an MS Access table to one) as we saw above.

A look at the ANSYS049_MSASQueryLog table reveals the nature of the data within the Query Log. Let's look at the layout of the table from SQL Server Management Studio, before examining its contents on a more specific basis.

The ANSYS049_MSASQueryLog table schema appears on a new Browser tab (adjacent to the Adventure Works [Browse] tab we opened earlier), within the SQL Server Management Studio, as depicted in Illustration 31.

As we noted in Usage-Based Optimization in Analysis Services 2005, the Usage-Based Optimization Wizard relies upon the Query Log to support its operations. 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.

In lockstep with a review of the fields from a description perspective, we can view the actual data in the table from the SQL Server Management Studio, as well.

The ANSYS049_MSASQueryLog table appears on another new Browser tab (adjacent to the tabs we have already opened), within the SQL Server Management Studio, as partially depicted in Illustration 33.

Each of the fields has significant potential, with regard to analysis and reporting utility. I have even created cubes from this table, which, coupled with performance, sizing and other such information from various sources, can support all manner of administrative and similar analysis. The fourth column, Dataset, can be highly useful with regard to the specific information that it reveals about cube usage. The somewhat cryptic records within this column represent the associated levels accessed for each dimensional hierarchy within the query. An example of the Dataset field appears (enclosed in a red rectangle), within a subset of a sample row, as shown in Illustration 34.

While we won't go into a detailed explanation in this lesson, I expect to publish a prospective article 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 fields present information which, particularly in combination with Dataset, 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 (via relational reports, OLAP reports, KPIs assembled from either or both, and more), and act as useful statistics upon which to base (or filter) numerous types of administrative reports, including information that will help us to plan for heavy reporting demands and other cyclical considerations.

Customizing Query Log Capture

As we have seen, the usage-based analysis and optimization processes provided via the Usage-Based Optimization Wizard perform their functions based upon statistics captured in the Query Log. From within the Analysis 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, as we saw earlier. Moreover, from the Properties settings, we can:

• Stop and start logging (we can leave it stopped entirely, if we desire);
• Clear, idle or restart the log;
• Create a new "prospective" log, while retaining its predecessor for archiving, etc.;
• Change the logging interval.

We have also mentioned that we can further manage the automatic logging of processing messages by 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 might 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. To cite an example of the utility of lowering the Sampling Rate that most of us might find intuitive, a fairly straightforward approach to optimizing performance based on usage is to create partitions with zero aggregations, adjust query logging to log every query for a period of time to capture typical usage patterns, and then use the Usage-Based Optimization Wizard to design aggregations appropriate to the usage. Once this is done, we can likely afford to raise the Sampling Rate frequency again, to lower the overhead inherent in its use.

Now that we have a basic 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 many of us probably can see, we can rely upon the fact that the options for reporting choices for cube utilization analysis are plentiful, whether we maintain the Query Log in an MSSQL relational table or a file, as we have discussed, or otherwise move / transform the data, and 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 Analysis Services and other popular OLAP 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, as well as my MSSQL Server Reporting Services series, here at Database Journal).

Regardless of the reporting application, the concepts are essentially the same in reporting from the Query Log. The general steps include the following:

• Establishment of a connection to MSSQL Server, and the database containing the Query Log table (or a connection to the respective location of the data if it has been housed elsewhere);
• Creation of a query / dataset against the data source;
• Creation of a report or other layout file within which to accumulate / present the selected data;
• Publication / deployment of the report / other layout in a manner whereby it becomes accessible to the intended audience(s).

As I have stated, our "medium" for reporting can be selected from a wide array of applications. I have created similar reporting processes in the past using a wide range of business intelligence tools, including MSSQL Server Reporting Services, Cognos, Business Objects, Crystal Reports, ProClarity, MicroStrategy, and Microsoft Office, among other less common tools.

Because reporting typically entails more than the mere entrainment of the data from the Query Log, for use in a report or reports within any of a multiple choice of applications, we will not be able to examine report writing and formatting considerations, aggregating the data in the report or elsewhere, etc., in this article. Many 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. 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 later 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 article, we introduced the general need for more sophisticated analysis based upon usage analysis statistics that we can collect surrounding our Analysis Services cubes. Our primary focus was a closer examination of the source of Analysis Services Database (and predominantly cube) performance statistics, the Query Log, discussing its location and physical structure, how it is populated, and other characteristics.

We first created a copy of a sample Analysis Services database for use in our practice exercise, and then enabled the Analysis Server Query Log to capture query statistics. We then processed our cube, before manipulating data to create Query Log entries, to complete preparation for our overview of the Query Log.

We next moved into the focus of our session, examining the Query Log contents, discussing the various statistics captured. Finally, we discussed reporting possibilities, commenting on both relational and OLAP options. As a part of our exploration of Query Log statistics capture, we touched upon various means of customizing Query Log capture to meet local needs.