Data Warehousing and Business Intelligence

16 June 2014

SSAS Stops Logging

Filed under: Analysis Services — Vincent Rainardi @ 8:17 pm

When the SSAS server is restarted, it stops populating the query log table. To make it starting logging the query again, we need to make a “no change” change on the Query Log connection string. As Mike Diehl suggested here, we could change the Application Name property.

This can be scripted into an XMLA, and executed as part of server reboot procedure. I haven’t found a way to automatically execute an XMLA every time SSAS server started, yet, but we can put this on the cube processing. After the cube processing, warm the cache, then check the maximum value of StartTime column on the OlapQueryLog table. If it is too far behind (e.g. more than 1 hour) than issue Alter xmla statement to change the App Name on the connection string. To make the App Name unique/different from the current one (so it will change), we can use the timestamp, converted to string.

This Process Cube – Warm Cache – Check Query Log – Update Conn String can be done in SSIS. How? Process Cube: using SSAS Processing task, Warm Cache: using SSAS Execute DLL task (XMLA Execute Command), Check Query Log: Execute SQL task, Update Conn String: uisng SSAS Execute DLL task (XMLA Alter instance properties).

As Mike Diehl suggested on his page above, the account used in the Connection String needs more permissions than db_datawriter, but probably less than db_owner. If we only set db_datawriter, Windows event log will produce something like: SSAS can not write into OlapQueryLog table because of insufficient permission, but it managed to create the table. (regardless whether in the SSAS Server Properties you specifies create table = false or true). Giving dbo permission certainly enables SSAS to write into OlapQueryLog.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: