Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
- Microsoft SQL Server Programming
- Performance Tuning
- Practical Applications
- Professional Development
- Application Architecture Assessments
- BI Explained
- Developing a Data Dictionary
- BI Security
- Gathering BI Requirements
- Source System Extracts and Transforms
- ETL Mechanisms
- Business Intelligence Landscapes
- Business Intelligence Layouts and the Build or Buy Decision
- A Single Version of the Truth
- The Operational Data Store (ODS)
- Data Marts – Combining and Transforming Data
- Designing Data Elements
- The Enterprise Data Warehouse — Aggregations and the Star Schema
- On-Line Analytical Processing (OLAP)
- Data Mining
- Key Performance Indicators
- BI Presentation - Client Tools
- BI Presentation - Portals
- Implementing ETL - Introduction to SQL Server 2005 Integration Services
- Building a Business Intelligence Solution, Part 1
- Building a Business Intelligence Solution, Part 2
- Building a Business Intelligence Solution, Part 3
- Tips and Troubleshooting
- Additional Resources
Gathering BI Requirements
Last updated Mar 28, 2003.
A small church had gathered for a business meeting to discuss the needs the congregation had. These business meetings usually went quickly unless Brother Jacobs was in attendance. He usually found a way not to like whatever it was that was being voted on. He was also quite elderly, and not a little prone to drift off to sleep during the meeting.
The business meeting revolved around the purchase of a chandelier from a fancy hotel in town that had been shut down. It was a fair chunk of money for the little group, but it was quite a bargain and would bring needed brightness to the dim sanctuary. The decision was made to put it for a vote. Partway through the vote, Brother Jacobs roused from his nap and shouted "I'm against it! I'm against it!" The pastor leaned forward wearily and said, "Why are you against it, Brother Jacob?" "Because," Brother Jacob replied, ticking off the reasons on his fingers, "it's too expensive, no one knows how to play one, and besides, what we really need in here is some more light!"
I've been through projects like that. I'll spend a lot of time answering a question only to find that the person didn't have the right information to begin with. In this tutorial we'll spend a little time getting the whole Business Intelligence project off the ground properly, by gathering the right information right from the start. I've covered requirements in general in another tutorial, but BI landscapes have a few differences from other software projects.
While any project can have bad consequences from not having a clearly defined set of requirements, it is especially devastating when this happens on a BI implementation. The reason for this is twofold. The first reason is that the data will be changed, grouped, separated and re-evaluated several times as it travels through the system. That makes it difficult to check the numbers once the data leaves the various source systems. In standard On Line Transaction Processing systems, there's usually a way to generally tell something is off. For instance, we notice that a plant is suddenly producing thousands more items than they were last week. But in a Business Intelligence implementation, multiple plants are evaluated throughout the year, and the data is gathered periodically. It's rare that a single individual would be able to pick out a gross error from the hundreds of measurements that accumulate in the system.
The second reason that good requirements are needed right from the start is the consequences of bad information. Keep in mind that the whole point of setting up a Business Intelligence landscape is to provide management to make proper strategic decisions. Strategic decisions (as opposed to tactical decisions) are long-term, and usually very expensive. Your organization's leadership is going to depend on the information your system provides.
I think the best method to talk about these requirements is to propose a simple outline of the requirements documentation, and then explain the line items, highlighting those that are unique to a Business Intelligence project.
Here's an outline that I normally use as a starting point:
- Goals and Objectives
- Logical Flow
- Source Systems
- On-line Analysis Systems
- Growth and Archival Strategy
There are many more items that we can use, but this will start us off as a guide.
You might have guessed the first section, since any set of requirements has a Description area. What sets this apart is that the business goals and objectives are actually more important than the technical objectives. Let me qualify that statement a bit.
There are hundreds of BI systems on the market, from mega-spreadsheets to large software implementations. To be honest, most of them do much the same thing in different ways. Beyond the scaling aspects, there aren't a lot of differences if they are implemented properly. So as long as you follow the directions, the technical goals here just aren't going to be the ultimately important factor.
The organization goals, however, are another matter. I've seen many organizations try to implement a BI system when they don't have a coherent data substrate or a unified reporting model. If those items aren't sound, it is pointless to build strategies on them.
As part of these goals, you have to get buyoff from the highest levels of the company. You can't implement BI from the bottom up, since theoretically upper management is the ultimate beneficiary of the knowledge you'll create.
In the goals section, you also have to make the organization come up with a clear set of definitions. This process can take up the bulk of the implementation, but not having it will devastate your efforts.
The scope section of the requirements is much like the scope in any other project, with the exception that most often the scope is the entire enterprise. Because of that, you'll often include a phased approach at implementation. "Big Bang" implementations can work, but they have their own set of issues, and a strategic system usually doesn't justify the efforts.
The next section which defines the logical flow is highly important. Any BI landscape is going to be complex, so you'll need text and graphics to get this information across to everyone.
The inputs and outputs sections explain where the data will come from, and where it will end up. You should have quite a few interested parties that will need to ask hard questions about how you're going to access the data in their source systems, and who will be allowed to see it. Knowing whether you'll implement off-line reports or a client that can browse the BI system (or both) will tell you how expensive this is going to be. It's one matter to provide timed reports, and quite another system is required to allow on-line access to the system.
The staging section tells everyone where and how the data will sit while you're gathering it. This also helps them understand how current the data can be, which is also covered in the schedule section. Make sure you explain data doesn't "quantum leap" into the BI landscape; there's quite a bit of work that happens to get the data from here to there. Knowing that up front helps set expectations. Don't let them use your BI system as a replacement for a good on-line reporting strategy, it just won't work.
My last tutorial on BI explained the security considerations for a BI system. The security section in this document where that information comes from. The organization hierarchy will have to tell you which groups can access the data, and at which levels of aggregation.
Frightened yet? Don't be. In the next set of tutorials I'll take you step by step in getting all the variables you need lined up for your BI implementation.
Informit Articles and Sample Chapters
Larissa T. Moss and Shaku Atre have a great book on BI that also discusses security called Business Intelligence Roadmap: The Complete Project Lifecycle for Decision-Support Applications. (Preview this book on Safari
One of my favorite resources, the IEEE Computer Society, has a great technical article on Inquiry-based requirements gathering. You can check it out here.