The number of users creating and consuming Business Intelligence (BI) at my organization has experienced explosive growth over the last few years. One of the things I need to do is ensure the organization’s BI infrastructure can meet existing and future needs so I decided to incorporate a method for monitoring BI utilization and its rate of change over time.
There’s a great tool on Codeplex called Server Management Reports that I integrated into the DBA Repository project. This tool loads SSRS utilization information from the ReportServer database to a data warehouse called ReportServerExecutionLog on a nightly basis. Although Server Management Reports was a great starting point, it had a few deficiencies for my needs:
- It was designed to warehouse SSRS utilization from a single SSRS instance
- It is designed to work with only one version of SSRS at a time
- Its reports are not quite at detailed as I needed
Multiple SSRS Instances
I modified the SSIS package to automatically find installed instances of SSRS on the DBA Repository’s server list. It looks for any database with “ReportServer” in the name and will include data from that database in the ReportServerExecutionLog database.
Once this was complete the data warehouse is able to find and gather utilization for all SSRS instances and data gathering will begin without additional configuration from a DBA. Also, if an SSRS instance is removed data gathering will automatically cease without error.
Multiple SSRS Versions
My organization has multiple SSRS servers some are SSRS 2005 others are SSRS 2008 R2. The ReportServer database schema changed in those versions and the SSIS package provided on CodePlex works for one or the other. I adapted the SSIS package to gather data from either version so multiple packages do not need to be maintained.
I revised or extended all the reports that came with Server Management Reports so they fit my needs better, were incorporated nicely in the DBA Repository’s dashboard and assisted the DBA team with troubleshooting SSRS errors.
SSRS Utilization Home
The main SSRS Utilization report displays overall statistics for the last full month but the data range is parameterized. It also displays the Top 10 reports by Size, Duration and Data. Most of the data on this report is interactive and clicking on it will drill-through to additional detail, I’ll demonstrate a couple examples below.
The Execution Exemption report display all the errors that have occurred in a time frame, defaulting to the last full month. The report list at the bottom shows all reports that experienced an error and allows drill-through to the specific report executions to aid troubleshooting errors.
The report Summary displays all the executions of the report in a given time period, what exceptions were thrown at runtime and what the values of any parameters were to aid in determining if bad data is causing a report to fail
Gathering utilization data from all enterprise SSRS server allows the DBA team to monitor how the reporting BI infrastructure is being use and assist in troubleshooting report exceptions and optimize slow running reports