Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 7982 | Views in the last 30 days: 10
 
Related Articles
FORUM

database properties not accessing

database properties not accessing

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

FORUM
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones