A 1000 Ways to Die in BI


Business Intelligence (BI) promises to simplify the use of information in an organization.   For the business it offers a place to bring data together, to find that single source of truth, and give business leaders the courage to act boldly backed by real information and real analysis.  However, for the BI development team the journey to this nirvana is a difficult and dangerous minefield.  End user confidence in validity of BI data can be razor thin at best. Even the slightest data integrity error sends ripples of distrust that are difficult to recover from.   The sources of BI problems are endless and include bad data, server failures, database failures,  performance problems and user confusion just to name a few. No amount of planning can protect the BI system from all unexpected challenges, but there are definitely a few simple techniques that can mitigate some of these risks.

Technique 1 – Monitors

A simple yet overlooked technique is to make extensive use of automated monitors to constantly ensure the integrity of the system. Even relatively small data problems can plant the seeds of doubt for end users.    As more data is added to the warehouse it becomes an increasingly complicated for the BI team to ensure that data sources and warehouses are in sync.  It just takes a few data integrity hiccups before earning back user trust becomes extremely difficult.   The solution is to get ahead of the problem by creating monitors to automatically validate data before any serious development begins.   For example, if the team is looking to load purchase orders into the warehouse, one of the first steps is to create an external check that verifies the information between the systems match.   In the end this serves two primary functions.  First, the monitor provides a nice indication when development is complete.  Second, on an ongoing basis the monitor ensures that new work doesn’t break previous development efforts.  We have implemented SQL Server Reporting Services (SSRS) to host the monitors and display the output in a dashboard format. No new functionality or data is added to the warehouse without creating a monitor in SSRS first.   Currently there are over 250 separate monitors that look at everything from data integrity to end user processes.  The monitors are critical because it keeps the BI team on track rather than constantly looking in the rearview mirror for problems in existing functionality.  The team is 100% forward looking and counts on the monitors to identify fixes that are required.

Technique 2 – Documentation

It should come as no surprise that documentation is critical to the long term maintainability of any BI solution. However, it requires a strategy that is more comprehensive than simply making self-documenting ETL scripts and putting together notes haphazardly in a word document.   The documentation has to be readable from the start.  One strategy that has really paid off for the Plano BI team is to require complete documentation on any new functionality before development starts. This includes both administrative and end user manuals.    As design concepts change or functionality is added the documentation again should be updated first.  There are a number of benefits of this approach. First the end solution is generally better because upfront documentation forces the architect to think through more design challenges at the beginning. Concepts that don’t make sense in documentation are good candidates for architecture redesign.  Second, the quality of the documentation will be far better. Tweaking existing documentation at the end of project is far easier than writing a document from scratch.  However, the real advantage is the long term maintainability of the system.  When there are challenges the BI team will be well prepared to handle the problem.

Technique 3 -Education Seminars

Outside of data integrity or hardware problems the single biggest threat to BI is user anxiety on how to use the system.  Sound documentation will certainly mitigate this challenge but it should only supplement hands on training.  We run an ongoing educational system with the sole purpose of educating users on BI.   On a regular basis the BI team hosts training seminars on various topics which are tailored to a specific group of users.   The goal is provide a small group setting where users can ask questions about BI related topics that are immediately pertinent to their role.   Each seminar covers one main “theme” topic although only a relatively short amount of time is spent on that topic.  The vast majority of the time is geared toward re-teaching topics from previous seminars.   It is through the technique of repetition that users will become skilled in the business intelligence tool.   For example, we will have a theme topic discussing how to use a new vendor performance dashboard.   We’ll give that main topic a thorough review but then use the remainder of the time to reinforce related topics from previous sessions.    The cycle repeats and after 3-4 times of hearing about the vendor performance dashboard users will become comfortable with its form, fit, and function.  The bottom line is a comprehensive training will make the organization better and more extensive users of the business intelligence system.

Running a business intelligence practice is tough.  There are truly far more than 1000 ways to die in the management of a BI data warehouse.  However, sometimes following simply techniques can make a profound difference. The BI team needs to create a well-documented solution, back it with extensive training, and make board use of data monitors to ensure the integrity to help eliminate many challenges.

                                           Mel Heckman
           Director of Business Intelligence | Plano Synergy