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.