Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 20, 2003

Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Toward More Sophisticated Analysis - Page 5

By William Pearson

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:

Query Event

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.

Summary ...

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM