December 5, 2007 at 12:50 am
Hi
What I really want to do is to keep a record of analysis services audit file, for example, in a particular time period, I want to know who has browsed the cube, how long does it take for a particular MDX to execute.
I know I can use SQL Server Profiler to monitor the analysis services and I can get the expected result that I want, but I cannot scheduler a job for SQL Server Profiler to run at a particular time and export the result to a SQL database or excel file.
Is there any way that I can schedule a job and export the result to a file?
Also, I am thinking of using full sql log to capture this information? Is it possible?
Thanks.
December 6, 2007 at 7:52 am
Analysis Services has a built in function to log queries (OLAPQueryLog). You turn it on via the properties of the server. It collects the database, the cube, user, query mask, start time & duration. If your MSAS is lightly used, set the QueryLogSampling to 1. Be prepared to run some regular cleaup updates on this log (i.e. deleting entries for test cubes). Some notes:
- The query mask is both useful and frustrating. To understand the query mask, you must understand how attributes are masked. Download a copy of BIDS Helper to help you get started.
- In my experience the logging facility does have a tendancy to stop, when certain problem occur. To restart it, I just restart MSAS. But monitor it for a while to make sure you're not having problems.
- The log can / is used by the Usage Based Optimization, to tune your aggregations. However, as you change attribute information (i.e. add / drop attributes) you essentually invalidate the query mask (changed the bit flags). So it's most useful if you've let it run for awhile.
- While doing cube development work, you can "train" the MSAS Usage Based Optimization by running a prepared set of queries that you want to tune (clear the log first).
Note: this facilitry does not track the partitions accessed, which in my own opinion woudl be a great enhancement. Then, the optimizer (and the designer) could make some intellgent decisions on aggregation designs for different partitions. Oh well, enhancement request!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply