Enabling Query logging in SSAS 2005

,

Introduction:

SQL Server Analysis Services (SSAS) 2005

has brought a plethora of new features to the OLAP developer and administrator.

Many new features are available and existing features from AS2000 have been

altered as well. In this article I would like to present to the reader a

walk through of how to implement one of the more important features of SSAS 2005,

which was present in AS2000 but is implemented a bit differently in the new 2005

platform: How to enable query logging, used later for designing usage-based

aggregations.

In Analysis Services 2000...

In AS2000 the process for collecting user queries information was as follows: right-clicking the AS server on the Enterprise Manager and choosing “properties”. Choosing the “Logging” tab, and in the “Log settings” we need to check the “Log queries sent to server” checkbox and decide on the sampling frequency, which can range from 1 to 10,000, which means 1 in every 1-10,000 queries will be written to the log. The data collected is stored in an Access database: msmdqlog.mdb. This data is later used

when you decide to design aggregations on a cube, based on usage. This was the

pretty straightforward approach in AS2000.

In SSAS 2005...

In SSAS2005 setting the logging

information requires a bit more tweaking. Firstly you need to access the SSAS

properties. Open SQL Server Management Studio (SSMS) and connect to your SSAS

server. In the Object Browser window, right click the server and choose

“Properties”. You will notice the strikingly different looks of the server

properties screen, as opposed to the AS2000 server properties dialog box. You

really have many more properties to play with here and you do need to be

careful. Most of the properties are set to make SSAS operate at the best

performance. However, some properties will have to be changed in order to enable

logging of queries execution on the server.

Expand the “name” column so you will be able to see the full description of each property and scroll down to the “Log\QueryLog” section. Let’s explain what each property actually means. I will go through each property in a logical order, which is not the order the properties appear on the screen.

  1. Query Log Connection String: this property instructs the server where

    data will be stored at. It can be a SQL Server database, an Access database,

    or an Oracle database. In fact it can be any data storage provider SQL can

    work with. This is a major enhancement from AS2000 where you were restricted

    to using the Access database (unless you modified the relevant registry

    entries to point to a SQL Server instead), with all its limitations as the

    data store for logging activity. In order to set this connection string,

    simply click on the ellipsis button in the “value” column and choose your data

    source using the normal interface for configuring a data source in SQL Server

    2005. Obviously, if you want the data to be stored on a SQL Server, you will

    need to create the database before setting this property. In this example, the

    connection string will point to a SQL2005 SQL Server and an “OLAPLog”

    database.

  2. Next, we need to instruct the server to create the log table in the database

    we have just dedicated for logging. In order to so, we need to modify the

    value of the property “Create Query Log Table” from “False” to “True”. Note

    that if you will leave this property set to “False” the server will not log

    anything. You have to set this option to “True” for logging to happen.

  3. We can also decide how to name the table that will hold the logging

    data. By changing the value of the property “Query Log Table Name” we can

    decide what will be the logging table name. The default name is

    “OLAPQueryLog”.

  4. Similar to the AS2000 settings, we can decide on the sampling frequency of queries, by modifying the value of the “Query Log Sampling” property. The default value here is also similar to the AS2000 default settings and equals to 10. This means that 1 out of 10 queries will be

    logged.

  5. Should you decide to store the logging data on a file,

    located on the disk, there are two properties which you will need to get

    familiar with: “Query Log File Name” enables you to designate the file you

    want to store the data to, and “Query Log File Size” determines the size of

    the query log file and is an advanced property which Microsoft recommends not

    to change.

Conclusion

If you have followed through these guidelines

on how to set the SSAS properties, the server will start logging query execution

and the data will be collected in the database/file you have specified. After

collecting enough data, you can now start designing aggregations based on the

logging data you have collected. This will be the subject of a coming article.

Rate

Share

Share

Rate