Business Intelligence System Monitor
Designed for SQL Server

There is a wealth of typically untapped information that collectively describes the health of a Business Intelligence environment and the trustworthiness of the data it stores. Often, problems aren't detected until after a business user notices them: a report won't run, two reports present conflicting results, an ETL process failed, or a log drive filled up. BI System Monitor is a solution designed to provide a one-stop, quick-glance dashboard to allow you to assess the quality of your data and the health of the hardware and software supporting your BI platforms. It’s BI about BI.



Environment Overview.jpg


TSQL data audits may evaluate various SQL Server databases and capture important metrics so that issues can be handled before business users discover them or serious system problems are encountered. Well thought-out data audits should result in business users spending less time questioning whether or not the data in their reports is correct and spending more time using the BI platform to manage their business. What types of audits might be performed? There are numerous possibilities. Here are a few suggested audits:
  • Fact Period Comparison – What is the period-over-period percent change amount for each fact of interest?
  • Fact Problem – Are facts calculated correctly? – i.e. Is profit equal to the revenue minus cost? Are the same facts on different fact tables consistent? Are the Analysis Services cube measures consistent with the relational facts they are sourced from?
  • Data Profiling - Are there problems with source system data that should be communicated to the appropriate data stewards?
  • Data type Headroom – How much "headroom" does each fact column have until it reaches the maximum value allowed by its data type?
  • Dummy Reference – How many current fact rows reference dummy dimension members?
  • Dates – How many days away are my facts from the last date in the date dimension?
  • Dimension Management – What percentage of each dimension’s members are unreferenced?
  • Dimension Utilization – What percentage of each dimension’s members are inactive? What is the growth rate of each slowly changing dimension?
  • ETL Problems – How many rows of data for each ETL process failed to load?

file.png


If the SSIS log summary highlights problems, click on the summary to launch a detailed SSIS log report. This report enables rapid navigation and exploration of the SSIS log.


SSIS Logging.jpg


The SSIS Parameters report displays SSIS project and package parameters that are stored in SQL Server 2012's SSISDB.


SSIS Parameters.JPG


Before going to the effort of setting up BI System Monitor, please note:
  • The SQL Server 2008 SSIS Logging and SSIS Configurations reports are similar to their 2012 counterparts which are pictured above. The SSIS Configurations report supporting 2008 requires that your configuration data be stored in the stock SSISConfigurations table. Expect to do significant work to a report view that presents this data to the SSRS report if you're using 2008 - i.e. CASE logic, string parsing, etc. No such effort is required with 2012 which is plug-n-play by comparison.
  • The data audit SQL required to support the Data Audit report may require significant effort to initially create. (Many sample scripts are provided in the code download.)
  • The SSIS Logging report will be far more useful if the packages whose log activity it reports have descriptively and consistently named components.
  • The deployment documentation has received significant testing. However, you should expect 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,
LG Consulting

Last edited May 8, 2013 at 7:26 PM by ChipL, version 49