Overview: Just a quick tip re: resolving an issue with getting SSAS the query logging table populated.

Enabling the SSAS Query Log

In Management Studio, if you right-click the SSAS Server instance, you are presented with Analysis Server properties. Here is where you tell SSAS to log queries. The default table name is “OlapQueryLog” which will reside in the database you specify in the connection string.

In the screen shot below, you’ll see I specified a sampling of 1. This is because I wanted to capture all queries within this test database. I have a dashboard getting close to being rolled out. Before migrating to Production I wanted to run the Usage-Based Optimizer for the purpose of creating a few aggregations that’ll help query performance of the new dashboard (particularly a many-to-many bridge table that I know is a bit of a bottleneck).

So, I enabled the QueryLog properties as shown above. Then I went and ran my dashboard to force some records into the OlapQueryLog table.

Next, a quick select statement on the OlapQueryLog table – no records. It was empty. Huh?

SSAS Service Account Permissions

Why was my OlapQueryLog table empty? Well, in the connection string I didn’t specify certain credentials. Which means the SSAS Service Account was responsible for running it. And … that SSAS Service Account didn’t have write permissions to the database specified in the connection string.

So, since this was a quick exercise, I chose to grant the write permissions needed by the SSAS Service Account & then run my queries; after verifying I had the records I expected, I then set the QueryLogSampling property back to 0 (to turn SSAS query logging back off), and revoked the write privileges.

If you intend to keep logging enabled for more than a quick test, it would be a better practice to define a specific user ID and password that has few privileges overall, but with the write permissions it needs, and embed it in the QueryLogConnectionString property.

Finding More Information

Technet – Configuring the Analysis Services Query Log

SQL CAT - Reintroducing Usage-Based Optimization in SQL Server 2008 Analysis Services