Getting Analysis Services Audit File?

• rockzcm

  SSC Enthusiast

  Points: 174

  More actions

  December 5, 2007 at 12:50 am

  #118996

  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.

• Dave Balsillie

  SSCertifiable

  Points: 5589

  More actions

  December 6, 2007 at 7:52 am

  #757674

  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!