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.