SQL Server Analysis Services Query Log

By: Siddharth Mehta

Overview

After user access is configured for SQL Server Analysis Services (SSAS), users can start accessing the data. It's inevitable to log the data being accessed by the users for a variety of reasons. So the next logical task for an SQL Server Analysis Services (SSAS) Administrator is to setup a query log repository. We will look at how to setup this feature as well as how to analyze the data in the query log.

Explanation

Open the server properties from SQL Server Management Studio (SSMS), and focus on the query log related properties as shown in the below screenshot.

• CreateQueryLogTable - Set this property to true if you need the query log table to be created by the system. This table will hold all the logging information.
• QueryLogConnectionString - This property specifies the connecting string to the database where the table will be hosted.

• QueryLogSampling - This property specifies the sampling frequency. A default value of 10 means that 1 of every 10 queries would be captured. This reduces the query log table from growing too large.
• QueryLogTableName - This property specifies the name of the query log table. By default it is OlapQueryLog.

Once the properties are set, let the users access the cube. After the cube is accessed by the users, query the OlapQueryLog table, and you should be able to see something like the below screenshot.

The Dataset column is used by the Usage-Based Optimization Wizard to design better aggregations for a particular partition. The Dataset column represents information about potential aggregations which, if created, would help answer a particular query.

Additional Information

• Analysis Services creates and inserts into the query log table—it also cleans up the query log table. It does this so that the query log table does not grow indefinitely. Analysis Services deletes records from the query log table every time a structural change occurs to a measure group or a dimension that causes a change in either the number of dimensions in the measure group or the number of attributes in the dimension. Consider backing up this table in case if you want to retain the query log indefinitely.