Customizing Query Log Capture
the usage-based analysis and optimization processes provided in MSAS (via the Usage Analysis Wizard and the Usage-Based
Optimization Wizard, as we learned in our last
lesson) perform their functions based upon statistics captured in
the Query Log. Within the MSAS Server Properties dialog box,
which contains options that control user interfaces, server environment,
processing, logging, and add-ins, we can make the query logging process more
frequent than the default of one-in-every-ten queries. For that matter, from
the Properties settings, we can:
Stop and start
logging (we can leave it stopped entirely, if we desire);
Clear the log;
logging interval (Valid range is an integer between 1 and 10,000).
Although somewhat peripheral to our immediate
considerations, it might be useful to mention that we can also manage the automatic
logging of processing messages, directing the server to enable the logging of
the messages to a log file for which we can designate a file path and name.
While setting the logging interval too low might degrade
performance in a busy production environment, the logging interval setting for
a development environment would typically be a lower number than 10; this would
obviously allow us to capture more voluminous statistics to support intensive
analysis of cube performance and use prior to optimizing it for final delivery
to information consumers.
Let's practice the steps involved in setting this property
as if we were in a development scenario.
1. Start Analysis
2. Expand the Analysis
Servers folder by clicking the "+" sign to its immediate
Our server(s) appear.
3. Right-click the
desired server (mine appears as MOTHER in the illustrations).
4. Select Connect ...
from the context menu, as depicted in Illustration 4, as necessary, to
connect to the server.
4: Select Connect ... from the Context Menu
A Connecting to the Analysis Server message briefly
appears, and then the server icon displays the small, green arrow to its lower
right, indicating that a connection has been established, as shown in Illustration
5: The Icon Indicates Active Connection
5. Right-click the server
6. Click Properties
from the modified context menu that appears, as shown in Illustration 6.
6: Selecting Server Properties on the Context Menu
The Properties dialog appears, defaulted to the General
NOTE: Making a backup of the msmdqlog.mdb
before undertaking any of the modifications noted below is highly recommended
to avoid issues with an operating MSAS environment, damaging a production log,
7. Click the Logging
Logging frequency is set in the Write to Log Once per [integer]
8. Change the logging
frequency to "1."
Note that the Clear Log button is here, as well. We can choose
to clear the log of all entries with this option.
9. Click OK to
apply the frequency change.
A message box appears reminding us that we must restart Analysis
Services for the logging frequency change to take effect.
10. Click OK to
close the message box.
11. Select Console --> Exit from the console main
Analysis Services closes.
To see the results of our change in logging frequency, we can now
restart Analysis Services (performing steps 1 through 4 above will do the
trick), and then run a test query or two via the MDX Sample Application.
If we then look into msmdqlog.mdb, we can see the new entries,
containing the integer "1" in the SamplingRate field, for the
To give a common example of how lowering the Sampling Rate might be useful, let's consider a typical approach to optimizing performance based upon usage. We often begin the process by creating partitions with zero aggregations. We next adjust query logging to log every query for a period of time, to assist us in capturing typical usage patterns. We then use the Usage-Based Optimization Wizard to design aggregations appropriate to the usage. In short, we adjust the Sampling Rate to increase the size of the sample upon which we run the Optimization Wizard, presumably to more closely bring actual usage patterns into consideration within the process.
we have an understanding of the workings of the Query Log, we will
discuss options for producing customized reports to meet our business needs.