SQL Server Analysis Services Query Log



By:
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.

SQL Server Analysis Services Query Log Related Properties
  • 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.
Connection Manager from SQL Server Analysis Services
  • 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.

Query the OlapQueryLog table for Cube activity

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.

Last Update: 4/7/2016






Comments For This Article




Wednesday, March 15, 2017 - 10:51:53 AM - Satish Back To Top

I have configured the QueryLOG and it's working good, able to see records.

I can see results in huge count, Iam expecting result should come excluding the MSOLAP_user <> service acocunt. (i mean Query log should capture records only for user accounts, not service account)

Can i configure like this, do we have option ?



download


get free sql tips
agree to terms


Learn more about SQL Server tools