Overview
In our previous article, Execution Log Reporting: Preparation as a Data Source, we began with a discussion about a valuable source of information for performance and auditing analysis, the Report Server Execution Log. We noted that the Execution Log captures data specific to individual reports, including when a given report was run, identification of the user who ran it, delivery destination of the report, and which rendering format was used, among other information.
After discussing the nature of Execution Logging in general, we touched upon several of the ways in which it can assist us in understanding the performance of our reports, the actions of users, and a host of other details about the reports we create in Reporting Services. Working within a practice example where we responded to the expressed business needs of a hypothetical group of information consumers, we then performed transformation of the data in the Execution Log to a user-friendly reporting data source. We used the tools provided as samples with the Reporting Services installation to create and populate a MSSQL Server database, noting several of the benefits that would accrue to the information consumers. For the detailed steps we undertook, and to prepare to accomplish the steps of this article, please see Execution Log Reporting: Preparation as a Data Source.
Our focus in this article will be an examination of some of the uses to which the new Execution Log database might be put. Our examination will consist of hands-on publication of the sample reports provided with Reporting Services as a “starter set;” and then go beyond that set and create a customized report to show the ease with which we might help the information consumers we support to meet general and specific needs. We will propose other considerations that will add value to this already rich resource, and discuss ways in which we can leverage Execution Log reporting to make us better report writers from multiple perspectives.
In this session we will:
-
Review the hypothetical business requirements behind the procedures in this and the preceding article;
Review the Execution Log reporting database we created in our last lesson, focusing on its schema and the information it contains;
Review possible value-adds that come with Execution Log reporting;
Make copies of the sample report set provided with Reporting Services in a convenient location;
Upload the sample report copies, for execution within Report Manager;
Customize a copy of a sample report, to meet extended business requirements from our hypothetical information consumers group.
Execution Log Performance and Access Reports
Considerations and Comments
As a scenario for our practice examples in both our previous and current articles, we established a Reporting Services business requirement of a group of information consumers at a hypothetical client location. The consumers expressed the need to monitor report activity, for purposes of performance and auditing evaluation, for their recently implemented Reporting Services installation. The consumers asked that we work with them to create a database that is populated and maintained with this data for many potential uses. Upon accepting the project, we informed the group that, while we had created numerous relatively elaborate databases of this sort for clients in the past, our initial pilot in this instance would include Execution Log reporting capabilities that could be implemented rapidly and easily, using tools that accompany the installation of Reporting Services.
We made it clear that we would likely expand the capabilities to many other measures for overall performance and auditing monitoring after our pilot. We explained that beginning with the prefabricated “starter set” would be efficient, because Reporting Services provided a script to construct a basic reporting database to house the Execution Log data that it generates. In addition to the creation script, a DTS package, for transformation of the data, and for subsequently populating (and prospectively updating) the new database, was also included. Finally, a set of basic sample reports were included in the same “extras” bundle, which we could run as soon as we had transformed data from the Execution Log to the newly created database.
We pointed out that we had found this approach to be a “quick win” with regard to getting basic functionality in place; we also indicated that this was a great time in the Reporting Services implementation to be making performance and audit analysis capabilities available – just as we were beginning to write our first enterprise reports. The sample reports would serve several complementary, value-added functions, including:
-
Provision of a means for the Administrator to immediately see who was performing what actions within the new system;
Provision of an excellent learning tool for new report authors, whereby they could obtain confirmation that their new reports had executed;
Presentation of basic report samples, upon which the ultimate users could base requests for customization. (This could save a great deal of time in obtaining the requirements from users who, although they knew they needed these reports, may not have known “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 could be suggested, for example, as data sources grew in size, as the number of information consumers increased, as security needs changed, and so forth.
With the Execution Log data source now in place, we are ready to proceed with aligning and publishing the sample report set to kick off the new reporting efforts. Included on the Reporting Services installation CD is a group of sample Execution Log reports. These reports include the following titles:
-
Longest Running Reports
Report Parameters
Reports by Month
Reports by User
Reports Executed by Day
Report Size
Report Success Rate
Today’s Reports
The sample files also include the Visual Studio project (.rptproj) and solution (.sln) files for the report set, together with a data source connection file (.rds).The reports and objects are designed to be used with the Execution Log database we created and populated in our last session. A database diagram of the database is shown in Figure 1.
Figure 1: Simple Database Diagram (MS Visio) of the New Reporting Database