Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Toward More Sophisticated Analysis - Page 3
October 20, 2003
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.
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.
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.
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.
The Select Query dialog displays the newly added table and fields, appearing as shown in Illustration 5.
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:
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.
The Select Query dialog should now appear as partially shown in Illustration 12.
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).
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.