Query Performance Tuning - Expert Cube Development with SSAS Multidimensional Models - page 219

To be able to do Usage-Based Optimization, you must first set up Analysis Services
to log these requests for data. This involves specifying a connection string to a
relational database in the server properties of your Analysis Services instance and
allowing Analysis Services to create a log table in that database. The white paper
"Coniguring the Analysis Services Query Log" contains more details on how to do
this (it's written for Analysis Services 2005 but is still relevant for Analysis Services
2012), and can be downloaded from http://tinyurl.com/ssasquerylog .
The query log is a misleading name, because as you'll see if you look inside it doesn't
actually contain the text of MDX queries run against the cube. When a user runs
an MDX query, Analysis Services decomposes it into a set of requests for data at a
particular granularity and it's these requests that are logged; we'll look at how to
interpret this information in the next section. A single query can result in no requests
for data, or it can result in as many as hundreds or thousands of requests, especially
if it returns a lot of data and a lot of MDX calculations are involved. When setting
up the log you also have to specify the percentage of all data requests that Analysis
Services actually logs with the QueryLogSampling property - in some cases if it
logged every single request you would end up with a very large amount of data
very quickly, but on the other hand, if you set this value too low you may end up
not seeing certain important long-running requests. We recommend that you start
by setting up this property to 100 but that you monitor the size of the log closely and
reduce the value if you find that the number of requests logged is too high.
Once the log has been set up, let your users start querying the cube. Explain to
them what you're doing and that some queries may not perform well at this stage.
Given access to a new cube it will take them a little while to understand what data
is present and what data they're interested in; if they're new to Analysis Services it's
also likely they'll need some time to get used to whatever client tool they're using.
Therefore, you'll need to have logging enabled for at least a month or two before you
can be sure that your query log contains enough useful information. Remember that
if you change the structure of the cube while you're logging, the existing contents of
the log will no longer be usable.