Optimizing Microsoft SQL Server 2000 Reporting Services: Execution Log Reporting: Preparation as a Data Source

What We Will Need to Complete the Series Tutorials

To get the most out of the Optimizing MSSQL Server Reporting Services series, we need to have installed Microsoft SQL Server 2000 Reporting Services, together with Microsoft Visual Studio.NET (required to access Report Designer for report creation). Reporting Services ships with MSSQL Server 2000, and many of my articles use MSSQL Server and MSSQL Server Analysis Services components, predominantly as data sources, within them.

Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples. The Service Pack 1 update for Reporting Services is also assumed to have been installed.

Images are from a Windows 2003 Server environment, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2000 Reporting Services, MSSQL Server 2000 and MSSQL Server 2000 Analysis Services (“MSAS”).

The Reporting Services installation can be the Developer, Standard, or Enterprise Editions (as can be the accompanying MSSQL Server installation), although we may occasionally undertake exercises that require Developer / Enterprise, and that go beyond the somewhat more limited features of Standard Edition. For purposes of the series, it is assumed that the required Reporting Services, MSSQL Server, and MSAS components are appropriately accessible to / installed on the PC, with the appropriate access rights to the sample cubes and other objects provided in a typical installation of MSSQL Server and MSAS. It is also assumed that the computer(s) involved meet the system requirements, including hardware and operating systems, of the applications we have mentioned.

In carrying out certain activities within the Optimizing MSSQL Server Reporting Services series, we may occasionally call upon components of the Microsoft Office suite, and other relatively common applications. Should any such software be needed for a given article, we will note that consideration in the introductory paragraphs to minimize interruptions, distractions and disappointments.

Introduction to the Series

I became a beta-tester of Reporting Services early in its development, and rapidly came to the conclusion that this new MSSQL Server 2000 component would literally change the face of enterprise reporting as we know it today. Not only does Reporting Services provide an integrated, end-to-end set of tools for creating, managing, and viewing / delivering reports, but it does so with a scalable engine that supports server-based hosting and processing of reports. This is enterprise reporting at its finest, with several impressive advantages over the current offerings in the enterprise business intelligence arena. And the potential savings that await the implementing organizations could rank right up there with those promised by the recent fads of outsourcing, among other “follow the leader” activities so prevalent in business today.

As a recovering Certified Public Accountant, who also holds credentials as a Certified Management Accountant and Certified Internal Auditor, I spent several years dealing with reporting systems from the perspective of an information consumer. I now have over eleven years’ experience as a data architect and implementer of business intelligence for many Fortune 500 organizations; For most of that time, I have worked daily with large enterprise reporting applications such as Cognos, Business Objects, Crystal Enterprise and Crystal Analysis, MicroStrategy, and other applications / combinations of applications.

To me, the Reporting Services model paints a bright future for all roles in the reporting life cycle, not only because an organization gets the entire solution in one package, but because the solution is open and extensible, allowing report authors, managers, and users at every level to benefit from familiar tools and systems that are already in place. The solution is a part of the Microsoft BI framework, and is the latest entry to a powerful family of tools that includes a relational database (SQL Server), a powerful ETL tool (Data Transformation Services), an OLAP engine for cube production (Microsoft Analysis Services), and a formidable data-mining component, among other substantial functionality. Needless to say, integration with Microsoft Office components is a given.

In this series of articles, I’ll share some of my insights and discoveries as I continue to implement Reporting Services, in hopes of shedding more light on the practical realities I find in the optimization arena. I’ll do this from the viewpoint of a practitioner who has worked closely and repeatedly with many BI products, sometimes comparing functionalities between some of these and the new Reporting Services offerings. To kick the series off, we will begin with a hands-on introduction to establishing the capability to monitor access and performance of our Reporting Services implementation itself. As part of this overview, we will discuss numerous “value adds” that this capability can afford us, in addition to the obvious benefits of performance and audit reports in general.

Overview

General optimization of Reporting Services’ performance is, beyond argument, one of the more important functions of the Administrator. In evaluating performance from various perspectives at the Administrative level, one readily useful source of information is the data we can obtain from the logs created by the system itself. Reporting Services generates a number of log files to capture information about server operations, status, and so forth. Within this group of logs, which we will explore individually within prospective articles within our series, the Report Server Execution Log is a great place to start in setting up a basic performance and auditing analysis capability.

The Execution Log captures data specific to individual reports, including when a given report was run, identification of the user who ran it, where the report was delivered, and which rendering format was used.

In this session we will:

• Discuss Execution Logging in general, and the steps involved in creating a useful data source for reporting purposes from the log as it appears in its native form;
   

• Discuss potential complementary functions we can leverage from implementing Execution Log reporting;
   

• Create a database to store the Execution Log data;
   

• Navigate to the location of the files provided with Reporting Services to assist us in transforming Execution Log data to a useful reporting data source;
   

• Open and execute the table creation script provided to create the schema for our new reporting database;
   

• Load and execute the DTS package provided to transform the Execution Log data and to populate the new database tables.