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
elsewhere).
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
MS Access
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.
1.
Go to the Start
button on the PC, and then navigate to the Microsoft Access icon.
2.
Click the icon
to start MS Access.
MS
Access opens, and
may display the initial dialog. If so, close it.
3.
Select File
-> 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.)
4.
Select msmdqlog.mdb.
5.
Click Open.
We arrive
at the Database Window.
6.
Click Queries,
under Objects in the Database
window.
7.
Click New on the
Database window toolbar, just above the Objects pane.
The New
Query dialog appears, as shown in Illustration 7.
Illustration 7:
The New Query Dialog
8.
Ensuring that
the Design View option is selected, click OK.
The Select
Query dialog appears by default, with the Show Table dialog
appearing in front, (containing the sole QueryLog table) as shown in Illustration
8.
Illustration
8: The Select Query Dialog (Compressed View), with Show Table Dialog Foremost
9.
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
dialog.
10.
Click the Close
button on the Show Table dialog to close it.
The Select
Query dialog, upper portion, displays the newly added table, appearing as
shown in Illustration 9.
Illustration
9: The Select Query Dialog, with Selected Table (Compressed View)
For the
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.
-
MSOLAP_Cube
-
MSOLAP_User
-
Dataset
-
StartTime
The Select
Query dialog displays the newly added table and fields, appearing as shown
in Illustration 5.
Illustration
10: The Select Query Dialog, Selected Table and Fields (Compressed View)
We will
perform some added steps to make the query a more useful reporting data source
at this stage.
11.
Click the Criteria
field for the MSOLAP_Cube column, to place the cursor into the field.
12.
Type the
following expression into the Criteria field:
"Warehouse"
The
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
Environment series.
13.
Click the Criteria
field for the StartTime column, to place the cursor into the field.
14.
Type the
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
this juncture.
15.
Select File
-> Save As.
The Save
As dialog appears.
16.
Type the
following into the "Save Query 'Query1' to:" box:
Warehouse Cube Activity
The Save
As dialog should now appear as shown in Illustration 11.
Illustration
11: The Save As dialog, with New Query Name
The Select
Query dialog should now appear as partially shown in Illustration 12.
Illustration 12:
The Select Query Dialog with Alterations
Now, let's
run the query and examine the result set that it returns.
17.
Select Query
--> Run from the main menu.
The
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).
Illustration
13: The Data Set Returned by Our New Query
We have
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.
18.
Click File
--> Close to close the Warehouse Cube Activity
query.
19.
Click Yes,
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.
» 