Configuring the Analysis Services Query Log

Writer: Edward Melomed

SQL Server Analysis Services uses query logs to log statistical information about queries.

SQL Server Analysis Services 2005 continues to store information about queries in a relational database. This information is used in Usage-Based Analysis.

There are only a few differences between Analysis Services in Microsoft® SQL Server™ 2000 and Analysis Services in SQL Server 2005. However, these differences are quite important. Following is a brief description of each of these important differences.

• By default, Analysis Services 2005 does not log queries into the query log table. To log queries, you need to adjust Analysis Services properties.

• Analysis Services 2005 does not support Microsoft Office Access databases as a hosting technology for the query log table. You must point Analysis Services to the SQL Server database if you want a to create a query log table and log queries.

• SQL Server does not have to reside on the same computer as Analysis Services.

• Analysis Services no longer uses the registry. All Analysis Services server properties that control query log behavior are accessible through SQL Management Studio or by directly modifying the Analysis Services configuration file.

• The format of the query log table format has changed. If you depend on the query log table, you must adjust your application so that it can use the new format.

On This Page

Configuring Analysis Services to Capture Statistics About Running Queries
The Dataset Column in the Query Log Table
Query Log Cleanup
Troubleshooting the Query Log

Configuring Analysis Services to Capture Statistics About Running Queries

There are four properties exposed by Analysis Services. In this example, we use SQL Management Studio to adjust Analysis Services server properties.

To access these properties, start SQL Management Studio and connect to the server that is running Analysis Services. Right-click the Analysis Server node and select Properties.

You will see the Analysis Server Properties dialog box as shown in Figure 1.

Figure 1

The next sections describe the properties that are used to adjust the query log table.

CreateQueryLogTable

If the CreateQueryLogTable property is set to true, Analysis Services attempts to create a table in the relational database. The table will have the name that is specified in the QueryLogTableName server property.

If a table exists already, Analysis Services tries to use that table.

The format of the table must match the specifications listed in Table 1.

Table 1

If CreateQueryLogTable is set to false and a table exists, Analysis Services tries to use the existing table.

QueryLogConnectionString

Click the query log connection string to display the standard connection dialog box, which you can use to point to the SQL Server database.

Note: Although the dialog box does not prevent you from pointing to another relational database, the only supported database type is SQL Server.

Clearing the QueryLogConnectionString property causes Analysis Services to stop logging query statistics.

QueryLogSampling

The QueryLogSampling property defines the frequency with which Analysis Services logs query statistics into the query log table.

The Dataset Column in the Query Log Table

This example uses the data in the Dataset column in the query log table. We use a migrated version of the FoodMart 2000 database for the example.We submit the following Multidimensional Expressions (MDX) query to the server that is running Analysis Services:

SELECT measures.members ON 0 FROM sales

We then see the following entry in the query log table:

“0000000,00100,000000,00,00,000000000,00,00,00,00”

This is a comma-separated string of zeros and ones, where every part of the string represents a dimension.

In this example, the first seven zeros “0000000” represent the Store dimension. Every digit in the part of the string that represents the Store dimension dimension represents a specific dimension attribute. The number 1 appears in the string in the position of whichever attribute is participating in the query.

So, we submit the following query

SELECT [Store].[Store Country].members ON 0 FROM sales

As a result, the following string is logged in the query log table.

0000001,00100,000000,00,00,000000000,00,00,00,00

The [Store Country] attribute is involved in the query and the number 1 appears in its position in the dataset.

By sending different queries, you can watch the way Analysis Services records information about these queries.

The Dataset column will later be used by the Usage-Based Optimization Wizard to design better aggregations for a particular partition. The Dataset column represents information about potential aggregations which, if created, would help answer a particular query.

The order of dimensions in the dataset string will be the same as the order in which you retrieve dimension collections by using Analysis Management Objects (AMO).

If you start SQL Profiler and capture an Analysis Services server trace while querying, you will see a QuerySubcube event with very familiar information in the text column. That information is exactly the same as the dataset string that is found in the query log.

To receive information about the dataset in the verbose format, capture the QueryCubeVerbose event. Every participating attribute will be represented, not by 0 or 1, but by its name.

Query Log Cleanup

Analysis Services creates and inserts into the query log table—it also cleans up the query log table. It does this so that the query log table does not grow indefinitely.

Analysis Services deletes records from the query log table every time a structural change occurs to a measure group or a dimension that causes a change in either the number of dimensions in the measure group or the number of attributes in the dimension.

Troubleshooting the Query Log

The query log subsystem is initialized on Analysis Services startup. Analysis Services reports initialization problems in the Microsoft Windows® NT event log. Look in the event log for events and errors associated with the Analysis Services query log.

Download

Configuring the Analysis Services Query Log
160 KB
Microsoft Word file