Business Intelligence System Monitor(Designed for SQL Server 2008)There is a wealth of typically untapped information that collectively describes the health of a Business Intelligence environment. Often, problems aren't detected until after a business user notices that a report won't run, report data hasn't been refreshed, or a log drive fills up. I have created a multi-environment Environment Overview SSRS dashboard that presents configurable environment health metrics (defined by metadata stored in a SQL Server table), a 24-hour summary of the SSIS log and a recent snapshot of the status of the SQL Agent jobs running.
If the SSIS log summary highlights problems that deserve attention, by clicking on the problem a detailed SSIS log drill-down report is lauched that reveals the log details relating to the problems identified on the dashboard. This report enables rapid navigation and exploration of the SSIS log.
The SSIS Environment Configurations report displays SSIS package configurations that are stored in SQL Server.
Before going to the effort of setting up BI System Monitor, please note:
- The SSIS Logging report and infrastructure supporting it are designed to facilitate reporting of data logged by SSIS to the stock SSIS log table in a SQL Server database.
- The SSIS Logging report will be far more useful if the packages whose log activity it reports have descriptively named and structured components.
- The deployment documentation has received significant testing and the feedback has been positive. However, you should expect to have to dig into a number of scripts, understand what they are doing (comments have been added) and then modify them to reflect your environment.
Chip Lambert