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