Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Enabling Query logging in SSAS 2005

By Yaniv Mor,

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.

 
Total article views: 8053 | Views in the last 30 days: 7
 
Related Articles
FORUM

database properties not accessing

database properties not accessing

ARTICLE

Changing Linked Server Properties

A quick guide on changing the linked server properties and settings.

FORUM

Migration of AS2000 cubes to AS2005.

Migration of AS2000 cubes to AS2005.

FORUM

quering server properties from linked servers

I'm trying to query a given servers properties via a linked server connection

ARTICLE

Industrial-strength database documentation using Extended Properties

To finish this short series on extended properties a look at documenting sets of database objects

 
Contribute