Bill Anton recently had a really great post about the OLAP query log and how to turn it on and translate it into English (available here). I’ve been meaning to blog about this for awhile but have been putting it off, and reading his post motivated me to go actually do it, instead of just staring at the note I have sketched down (thanks Bill!) One thing to keep in mind is that the query log does consume I/O while in use, so be careful about putting too much overhead on your server.
So let’s say you do all this great reading, and want to turn on your OLAP Query Log. You go to properties->general, and change the query log create table to true, specify your connection string, give your table a name, and then go check your database. But….nothing happens. Even after you refresh. Oh, Analysis Services, why must you vex me so? Fortunately, all of the information Analysis Services is doing with trying to create this table is logged. We just need to know where.
Deep in the depths of Analysis Services there is a file (well, lots of them actually, but we’re looking for something specific here :)). In the SQL Analysis services install directory, everything that the msmdsrv config file tries to do has a corresponding log entry, located in a log file at $InstallDrive\Microsoft SQL Server\MSAS12.MSSQLSERVER\OLAP\Log (note the 12 is the version number, for 2014. yours might be 11 for 2012, 10.5 for 2008R2, 10 for 2008, or 9 for 2005). In it is a simple text file called msmdsrv that shows all of the activity. It contains information like the start and stop times of the service, flight recorder start/stop info, some I/O information, and general configuration errors. If you scroll down to the bottom (assuming you just tried to create the OLAP Query log table and it doesn’t work, you should see an entry with the detailed error information:
Starting at the bottom, it says “An error occurred while starting the query log. Now we know at least what happened! An error. Ok, what error? Up two lines is our culprit: “Login failed for user ______________”. There we have it. I’d changed the service account on my vm to a dedicated SSAS MD service account (best practice and all), and hadn’t given it login and create table permissions in the source server. I’m going to go create a user in the SQL database engine for my new service account, and then go back into the properties. Turn the create table to false, click Ok. Go back in and change back to true, and click Ok. Now Analysis Services was able to create the log table without any issues:
Fortunately, a relatively easy fix, once you find the log file. Now my mind is beginning to wonder what else we can do with this log file… (hmm. definitely unstructured. maybe either filestream to get it into SQL or…. A HADOOP CLUSTER!!!!)