MSSQL Server Reporting Services: Black Belt Administration: Prepare the Execution Log for Reporting - Page 2

January 18, 2005

Prepare the Execution Log for Reporting

Objective and Business Scenario

The Execution Log contains data surrounding the reports that we execute on our server(s). The information the log provides includes details that can assist us in determining report efficiency and frequency of usage, together with myriad facts that assist in resource planning, monitoring the activities of users, and so forth. The information we can obtain from the Execution Log includes:

• The name of the Report Server Instance that handled a given request;
• The Report Identifier;
• The User Identifier;
• The Request Type (either User or System);
• The Rendering Format of the report;
• Any Parameter values used for the execution of a given report;
• The Start and Stop times of a given report process, from which duration is derived;
• The Percentage of Processing Duration spent retrieving the data, processing the report, and rendering the report;
• The Source (including Live, Cache, Snapshot, or History) of report execution;
• Status of Processing for a given report (indicating either a successful process, or the code of the first Error Condition encountered);
• The size of each generated report.
• The number of rows returned from query(ies) underlying each report.

Reporting Services logs report execution details into an internal database table that, in its pristine form, is of limited use to us in administrative reporting. The good news is that, with the installation of Reporting Services, we are provided tools to help us to extract the data we need to be able to report effectively on the details we have mentioned. In this article, we will discuss these tools and how we can use them to transform the raw data found in the Execution Log into a form that we can easily access and query to produce various reports to support us in the analysis and administration of Reporting Services.

For purposes of our practice procedure, we will assume that information consumers at a client location have expressed the need to monitor report activity, for purposes of auditing and performance evaluation. The consumers have asked that we work with them to create a database that is populated and maintained with this data for many potential uses. We accept the project, and state that, while we have created numerous relatively elaborate databases of this sort for clients in the past, our initial pilot will include the Execution Log reporting capabilities that can be implemented easily, from tools that accompany the installation of Reporting Services.

While we certainly plan to expand the capabilities to many other measures for overall performance and auditing monitoring, we like to begin with this step, we explain, because Reporting Services provides a script to build the basic reporting data source for the Execution Log that it generates, as well a few basic sample reports that we can run as soon as we transform data from the log to the newly created database. We assure the client that we have found this to be a "quick win" with regard to getting basic functionality in place, in many cases (including this one) at a great time in the Reporting Services implementation - just as we are beginning to write our first enterprise reports. The sample reports serve several complementary functions in this scenario, including:

• Provision of a means for the Administrator to immediately see who is performing what actions within the new system;
• Provision of an excellent learning tool for new report authors, whereby they can obtain confirmation that their new reports execute;
• Presentation of basic report samples, which their ultimate users can base requests for customization. This can save a great deal of time in obtaining the requirements from users who, although they know they need these reports, may not know "where to start" in providing a useful specification for their design.
• Facilitation of optimization of reports and their underlying queries, both at times during and after creation, and at various points in time as a part of ongoing system upkeep and maintenance (tuning can be suggested, for example, as data sources grow in size, as number of information consumers increase, security needs change, etc.)

Having imparted our ideas to the client team, confirming our understanding of their needs as part of the process, we begin the setup of the Execution Log reporting.

Considerations and Comments

Before we can work with the Execution Log data, we need to take several preparatory steps to make the data it contains useful from a reporting perspective. Our preparation will include executing a DTS package that Reporting Services provides to extract the data from the Execution Log and put it into a table structure that we can easily query. As we noted earlier, the internal table in the report server database does not present the data in a format that is readily user-friendly. The DTS package included with Reporting Services resolves this problem by collecting all of the data we need and putting it into a table structure that is intuitive in its organization.

As part of our setup, we will be creating folders, copying files, creating a database, and performing other actions. To complete the procedure you will need to have the appropriate access and privileges, at the MSSQL Server 2000 level, within Reporting Services, MSSQL Server Analysis Manager, and elsewhere, to perform the respective actions. Setup is a one-time event, unless we decide to recreate the table elsewhere, or to otherwise modify the simple procedures to accommodate local requirements, and so forth.

As we have done in previous articles, we will make copies of the existing components we use, so as to preserve the originals in a pristine condition, in case we wish to perform (or are already performing) activities that require a "fresh set."