Explore more sophisticated Usage
Analysis reporting options for your cubes. Author Bill explores the Query Log,
including its modification and use as a direct reporting source.
About the Series ...
This is the sixteenth
article of the series, Introduction to MSSQL Server 2000 Analysis
Services. As I stated in the first article, Creating Our First Cube, the primary focus of this series is an introduction
to the practical creation and manipulation of multidimensional OLAP cubes. The
series is designed to provide hands-on application of the fundamentals of MS
SQL Server 2000 Analysis Services ("MSAS"), with each
installment progressively adding features and techniques designed to meet
specific real-world needs. For more information on the series, as well as the hardware
/ software requirements to prepare for the exercises we will undertake,
please see my initial article, Creating Our
Note: Service Pack 3 updates are assumed for MSSQL
Server 2000, MSSQL Server 2000 Analysis Services, and the related Books
Online and Samples.
learned in our last lesson, MSAS
Administration and Optimization: Simple Cube Usage Analysis,
that Microsoft SQL Server 2000 Analysis Services ("MSAS") provides
the Usage Analysis Wizard to assist us in the maintenance and
optimization of our cubes. We noted that the Usage Analysis Wizard
allows us to rapidly produce simple, on-screen reports that provide information
surrounding a cube's query patterns, and that the cube activity metrics
generated by the wizard have a host of other potential uses, as well, such as
the provision of a "quick and dirty" means of trending cube
processing performance over time after the cube has entered a production status.
stated in Lesson 15,
however, I often receive requests from clients and readers, asking how they can approach the
creation of more sophisticated reporting to assist in their usage analysis
pursuits. This is sometimes based upon a need to create a report similar to
the pre-defined, on-screen reports, but in a way that allows for printing,
publishing to the web, or otherwise delivering report results to information
consumers. Moreover, some users simply want to be able to design different
reports that they can tailor themselves, to meet specific needs not addressed
by the Usage Analysis Wizard's relatively simple offerings. Yet others
want a combination of these capabilities, and / or simply do not like the
rather basic user interface that the wizard presents, as it is relatively
awkward, does not scale and so forth.
Each of these more
sophisticated analysis and reporting needs can be met in numerous ways. In this lesson, we will we will
examine the source of cube performance statistics, the Query Log,
discussing its location and physical structure, how it is populated, and other
characteristics. Next, we will discuss ways that we can customize the degree and
magnitude of statistical capture in the Query Log to enhance its value
with regard to meeting more precisely our local analysis and reporting needs; we
will practice the process of making the necessary changes in settings to
illustrate how this is done. Finally, we will discuss options for generating
more in-depth, custom reports than the wizard provides, exposing ways that we
can directly obtain detailed information surrounding cube processing
events in a manner that allows more sophisticated selection, filtering and
display, as well as more customized reporting of these important metrics.
At the Heart of Usage Analysis for the Analysis Services
Cube: The Query Log
Along with an
installation of MSSQL Server 2000 Analysis Services comes the installation of
two independent MS Access databases, msmdqlog.mdb, and msmdrep.mdb.
By default, the location in which these databases are installed is [Installation Drive]:\Program Files\Microsoft
The msmdrep.mdb database houses the repository, and will not be the
focus of this lesson. We will be concentrating upon the msmdqlog.mdb database,
the home of the Query Log where the source information for our usage
analysis and reporting is stored.
and Operation of the Query Log
of msmdqlog.mdb reveals that it consists of a single table, named, aptly
enough, QueryLog. Illustration 1 depicts the table within the
database, design view, so that we can see it's layout for purposes of
Illustration 1: The
Query Log Table, Design View, within Msmdqlog.mdb
NOTE: Making a copy of msmdqlog.mdb
before undertaking the steps of this lesson, including entering the database
simply to view it, is highly recommended to avoid issues with an operating MSAS
environment, damaging a production log, etc.
Optimization Wizard and Usage Analysis Wizard (see Lesson 15 for a discussion) rely on the Query Log,
as we learned in our last session. As we can see, the log is composed of
several relatively straightforward fields. The fields, together with their
respective descriptions, are summarized in Table 1.
name of the database used in the query
name of the cube used in the query
of the user that ran the query
numeric string indicating the level from each dimension used to satisfy the
string indicating the data slice for the query
time the query began
length of time (in seconds) of the query execution
number of different multidimensional OLAP (MOLAP) partitions that were used
to satisfy the query
number of different relational OLAP (ROLAP) partitions that were used to
satisfy the query
sampling rate at the time the query was executed
Table 1: The Fields of
the Query Log
In lockstep with a
review of the fields from a description perspective, we can view sample data in
Illustration 2, which depicts the same table in data view.
each of the fields has a great deal of potential, with regard to analysis and
reporting utility (the first three could be seen as dimensions), the fourth, Dataset,
can be highly useful with regard to the information that it reveals about cube
usage. The cryptic records within this column represent the associated
levels accessed for each dimensional hierarchy within the query. An
example of the Dataset field ("121411") appears in the
sample row depicted in Illustration 3.
Illustration 3: Example
of the Dataset Field
won't go into a detailed explanation in this lesson, I expect to publish an
article in the near future that outlines the interpretation of the digits in
the Dataset field. We will trace an example Dataset field's
component digits to their corresponding components in the respective cube
structure, along with more information regarding report writing based upon the Query
Log in general. Our purpose here is more to expose general options for
using the Query Log directly to generate customized usage analysis
fields provide rather obvious utility in analyzing cube usage, together with
performance in general. The Slice field presents information, in
combination with Dataset, which helps us to report precisely on the
exact points at which queries interact with the cube. These combinations can
provide excellent access and "audit" data. To some extent, they can
confirm the validity of cube design if, say, a developer wants to verify which
requests, collected during the business requirements phase of cube design, are
actually valid, and which, by contrast, might be considered for removal from
the cube structure based upon disuse, should the time arrive that we wish to
optimize cube size and performance by jettisoning little-used data.
StartTime and Duration provide the ingredients for evolved
performance trending, and act as useful statistics upon which to base numerous
types of administrative reports, including information that will help us to
plan for heavy reporting demands and other cyclical considerations. MOLAPPartitions
which provide counts on the different multidimensional OLAP or relational OLAP partitions,
respectively, that were used to retrieve the specified query results, can also
offer great advanced reporting options, particularly in the analysis /
monitoring of partitioning scheme efficiency, and the related planning for
adjustments and so forth.
Finally, SamplingRate displays the setting in effect for
automatic logging of queries performed by MSAS. This appears in Illustration
2 at its default of 10. The setting can be changed, however, as we shall
see in the next section.
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.
Direct Reporting for
Sophisticated Utilization Analysis
As most of us are
certainly aware, we can rely upon the fact that the options for reporting
choices for cube utilization analysis are plentiful. Whether we retain the
practice of maintaining the log in the original MS Access database format, or
whether we move the statistical data it contains, in whole or part, from the
Access environment a larger RDBMS (like MSSQL Server), house it in an
administrative reporting warehouse / mart, or some other such arrangement. We
will find that many common packages can be used in identical fashion to report
from these stores via ODBC and other channels. I have even created cubes (both
for Cognos PowerCubes and MSAS cubes)for larger clients from the
statistical information regarding cube usage, together with statistics that
come from other diagnostic sources, generating robust and useful mechanisms for
tracking cube usage and performance from many perspectives. (I expect to
publish articles that detail more of the specifics of some of these administrative
database and reporting options, in later articles in this series and
Because the concepts are
essentially the same, we will explore a basic approach to reporting from a copy
of the msmdqlog.mdb data source. Our "medium" for reporting
will be a combination of MS Access and a PivotTable view, but I have created
similar reporting processes in the past using a wide range of business
intelligence tools, including Cognos, Business Objects, Crystal Reports,
ProClarity, MicroStrategy, Microsoft Office, and, most recently, Microsoft
Reporting Services, among other less common tools.
Our focus surrounds the
entrainment of the data from the log, for use in a report in any of a multiple
choice of vendors, and we will not spend any time with formatting
considerations, aggregating data in the report, etc., as most of us will know
the steps required to create robust and attractive reports within our own
choices of solutions. Other of my articles elsewhere deal with the specifics
of working with various reporting options, and I will try to make the approach
here more generic, focusing on the "hows" of connection versus the
details of report writing.
Create a Query in
We will first create a
query in MS Access upon which we can base a reporting mechanism; this process
could be duplicated as stored procedure in an enterprise-level RDBMS, or we
could create the query at the reporting solution level and run the underlying
SQL against the tables of the database directly, among other approaches. To
keep it simple, let's set up an illustrative query in MS Access, mentally
extrapolating the process to other solutions where useful, to act as the source
of data for a sample report.
Go to the Start
button on the PC, and then navigate to the Microsoft Access icon.
Click the icon
to start MS Access.
Access opens, and
may display the initial dialog. If so, close it.
-> Open from the top menu, and navigate
to the copy of the msmdqlog.mdb database that we discussed
making earlier in the lesson (the file might also be accessed from the Open
a File menu atop the task pane, if it has not been disabled previously, at
the right side of the main window in MS Access 2002.)
at the Database Window.
under Objects in the Database
Click New on the
Database window toolbar, just above the Objects pane.
Query dialog appears, as shown in Illustration 7.
The New Query Dialog
the Design View option is selected, click OK.
Query dialog appears by default, with the Show Table dialog
appearing in front, (containing the sole QueryLog table) as shown in Illustration
8: The Select Query Dialog (Compressed View), with Show Table Dialog Foremost
Select the QueryLog
table, by highlighting it, and then clicking the Add button (a
double-click will also suffice), placing it into the Select Query
Click the Close
button on the Show Table dialog to close it.
Query dialog, upper portion, displays the newly added table, appearing as
shown in Illustration 9.
9: The Select Query Dialog, with Selected Table (Compressed View)
QueryLog table, double-click each of the indicated fields below, to
place it in the corresponding field of the matrix in the bottom half of the Select Query dialog.
Query dialog displays the newly added table and fields, appearing as shown
in Illustration 5.
10: The Select Query Dialog, Selected Table and Fields (Compressed View)
perform some added steps to make the query a more useful reporting data source
at this stage.
Click the Criteria
field for the MSOLAP_Cube column, to place the cursor into the field.
following expression into the Criteria field:
purpose of this expression is to filter the query selection to contain only log
entries for the Warehouse cube. We could easily make this a prompted
value, asking that the cube to be reported upon be selected at run time. For a
tutorial where such prompting is covered, see my article Reporting
in MS Access: Grouped Transactional Report Part I, a
tutorial in the DatabaseJournal MS Access for the Business
Click the Criteria
field for the StartTime column, to place the cursor into the field.
following expression, substituting dates that are appropriate for your local
dataset, into the Criteria field:
Between #10/1/2002# And #10/31/2002#
The purpose of this expression
is, again, to act as a very basic filter, which, just as the MSOLAP_Cube
column filter that we established above, could easily be enhanced to drive a prompt
for a runtime beginning date, end date, or both, instead of the "hard
coded" dates we have supplied above for the sake of simplicity. The
general idea, in any case, is to provide criteria to serve as the basis for
some of the cube usage statistics that we display in the report.
Let's save our work at
-> Save As.
As dialog appears.
following into the "Save Query 'Query1' to:" box:
Warehouse Cube Activity
As dialog should now appear as shown in Illustration 11.
11: The Save As dialog, with New Query Name
Query dialog should now appear as partially shown in Illustration 12.
The Select Query Dialog with Alterations
run the query and examine the result set that it returns.
--> Run from the main menu.
query executes, and then
returns a data set which should appear similar to that displayed in Illustration
13 (the data that appears in your own results will obviously differ).
13: The Data Set Returned by Our New Query
now created and saved a query in the surrogate MS Access database. This query
will serve as the data source for the report that we will build in following
sections to demonstrate the steps involved.
--> Close to close the Warehouse Cube Activity
if prompted to save the layout of query Warehouse Cube Activity.
We are returned to the Database
window, Queries view, where we see the new query appear.
Create the PivotTable
Now that we have put a
query in place to act as a data source, we are ready to set about the creation
of a sample report. In our present case, we will create a straightforward PivotTable
view to act as our report, keeping in mind, as we noted before, that any
number of more elegant, robust, and enterprise-oriented solutions might be more
appropriate in your own production environment.
within the Queries section of the Database window, let's take the
following steps to begin construction of our new PivotTable view.
the new Warehouse Cube Activity to select it
on the Database window toolbar.
query results set appears within the main window, as shown in Illustration 14.
--> PivotTable View from the
main menu, as shown in Illustration 15.
15: Select View --> PivotTable View
After a few seconds, a blank PivotTable appears, with a
floating PivotTable Field List appearing in the foreground, as shown in Illustration
16. Note the appearance of the two additional fields that are based upon
the StartTime field.
16: A Blank PivotTable View Appears
As I suggested
in the DatabaseJournal article Create
a PivotTable View in Access, the blank PivotTable offers an excellent "conceptual"
view of the basic makeup of a PivotTable. With a PivotTable we can create views
of our data that strongly resemble, but far out-power, a cross-tab query. The
PivotTable allows us to define the data values that we wish to occupy multiple
rows, columns, pages (or "layers") and summaries.
We can see above that the center of the table contains
numeric data ("measures"), while the rows and columns present (often
hierarchical) dimensional data. We will see a simple illustration of how these
areas of the conceptual "map" can be populated with our cube usage
analysis data in the steps that follow.
Let's define our view to
effectively present the data set returned from our new query.
If the PivotTable
Field List does not appear, initialize it clicking the Field List
button, shown in Illustration 17, on the toolbar.
17: The PivotTable Field List Button
In the Field
List, click MSOLAP_Cube to highlight it.
Area in the selector box, to the right of the Add to button (at the
bottom of the Field List), as shown in Illustration 18.
18: The PivotTable Field List with our Selection
7. Click the Add to button.
8. In the Field List, click MSOLAP_User to highlight it.
9. Select Row Area in the selector box, to the right of the Add to button.
10. Click the Add to button.
11. In the Field List, click StartTime to highlight it.
12. Select Detail Area in the selector box, to the right of the Add to button.
13. Click the Add to button.
The PivotTable view (with data appropriate to your own location) should resemble that shown in Illustration 19.
Illustration 19: The PivotTable with Our Field Assignments
NOTE: If the view appears, instead, with a "roll up" perspective that includes empty fields in the place of the StartTime column shown in Illustration 19 above, click the "+" sign to the right of the first MSOLAP_User name (shown as ISLO1\Administrator in Illustration 20).
Illustration 20: Expanding the StartTime Field
The dates of cube query events appear, giving us a view of StartTime by user.
14. Right-click the StartTime column heading in the PivotTable view.
15. Select Properties from the context menu that appears.
The Properties dialog appears.
16. Select the Captions tab.
17. Type the following into the Caption box:
18. Click the Bold format button to make the Caption bold.
19. Click the Centered alignment option to center the Caption in the view.
The Properties dialog, Caption tab, appears as depicted in Illustration 21.
Illustration 21: The Properties Dialog, Caption Tab, with Caption and Font Modifications
20. Close the Properties dialog.
The PivotTable view appears similar in structure (data, again, will differ) to that shown in Illustration 22.
Illustration 22: Completed PivotTable View
And so we see that reporting upon the Query Log is a relatively simple process, once a data connection is established between the reporting tool selection and the data source.
21. Save the PivotTable View as desired.
As we stated before, this illustration is only an attempt to show the process involved with entraining the data in the Query Log into a report. (Immediate improvements might include adding counts to the report, and thus supporting meaningful totals with numerous users, and a host of other possibilities.) In future articles, as I noted earlier, I will present more sophisticated reports, based upon data sources with more advanced usage, performance and other statistics. The demand is evident, in discussions with my clients and readers, for the capability to trend cube performance, create user access reports, and so forth and I will focus on some of those needs, together with ways to meet them. I will attempt to accomplish this over the months to come, with different reporting tools after we have exposed other components of the statistical data source, of which the Query Log is only a part.
In this lesson, MSAS Administration and Optimization: Toward More Sophisticated Analysis, we introduced the general need for more sophisticated analysis than that supported by the simple reports created by the Usage Analysis Wizard that we explored in our previous lesson. We examined the source of cube performance statistics, the Query Log, discussing its location and physical structure, how it is populated, and other characteristics. We then discussed ways to customize the degree and magnitude of statistical capture in the Query Log to enable it to meet more precisely our local analysis and reporting needs; we practiced the process of making the necessary changes in settings to illustrate how this is accomplished. Finally, we discussed options for generating more in-depth, custom reports than the Usage Analysis Wizard provides, exposing ways (and practicing with a simple example) that we can directly obtain detailed information surrounding cube processing events in a manner that allows more sophisticated selection, filtering and display, as well as more customized reporting of these important metrics.
» See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.