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
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.
- 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”
- 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.
- 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
- 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
- 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
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.