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:
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:
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."