Business Intelligence Projects

Large data warehousing projects have over time developed a bad reputation for being money pits with no quantifiable benefits. This somewhat gloomy picture can probably be explained by looking at many different systemic factors, but the fundamental issue was probably that the only thing many of these projects produced was gigantic wall-sized schema diagrams and the general unavailability of conference room B for 18 months or so.

A Business Value-Based Approach to BI Projects

Our recommended approach to BI projects is to focus on the business value of a proposed solution. That is, every BI project should have a clearly defined business case that details how much money will be spent and exactly what the expected business benefits are. As described in the final section of this chapter, these benefits may or may not be financially quantifiable, but they must be clearly defined and include some criteria to assess the results.

We recommend an iterative approach of short (three months or so) projects that focus on one specific business case. This approach has a lot of benefits in that it provides opportunity for improvement and learning through the different phases and can more easily adapt to changing business conditions as well as delivering business value along with way.

Instead of thinking about BI as a single large project that can deliver a defined set of features and then stop, you need to think about BI as an iterative process of building complete solutions. Each phase or version that you ship needs to have a clearly defined business case and include all the standard elements that lead to successful solutions, such as a deployment plan and training for end users.

In general, if a team realizes that it is not going to meet the deadline for this phase, they should be cutting scope. This usually means focusing attention on the key areas that formed the business case and moving the less-valuable features to a subsequent release. This kind of discipline is essential to delivering business value, because if your timelines slip and your costs increase, the expected return on investment will not materialize and you will have a much harder time getting commitment for a follow-up project. Of course, you can't cut many of the features that are essential to the "value" part of the equation either.

The challenge with this iterative approach is that without a careful approach to the data architecture, you might end up with disconnected islands of information again. The solution to this problem is that the architectural team needs to focus on conformed dimensions.

This means that whenever a particular dimension shows up in different areas, possibly from different source systems, the design approach must force all areas to use a single version of the schema and data for that dimension. Using conformed dimensions everywhere is a difficult process that costs more in the short term but is absolutely essential to the success of BI in an organization in order to provide "one version of the truth."

Kimball and Inmon

No overview of an approach to BI projects would really be complete without mentioning two of the giants in the field, who have somewhat different approaches. There are many comparisons of the two approaches (some biased, and some less so), but in general Ralph Kimball's approach is to build a dimensional data warehouse using a series of interconnected projects with heavy emphasis on conformed dimensions. Bill Inmon's approach is to introduce a fully normalized data warehouse into which all the data for a corporation is loaded, before flowing out into dependant data marts or data marts for specific purposes. This approach was previously known as Enterprise Data Warehouse, but more recently has become known by the somewhat awkward term Corporate Information Factory or CIF.

Our approach is more aligned with Kimball's because in our experience, it's difficult to justify the effort and expense of creating and maintaining an additional normalized data warehouse, especially because of the difficulty of tying concrete returns on the investment back to this central data warehouse.

Business Intelligence Project Pitfalls

Many technology books present BI as if the actual process of delivering a solution was so elementary and so obviously added value that readers are often surprised to find out that a significant number of BI projects are failures. That is, they don't provide any value to the business and are scrapped either during development or after they are delivered, often at great cost.

Throughout this book, we try to reinforce the practices that in our experience have led to successful BI solutions, but it is also important to highlight some of the major common factors that lead to failed BI projects.

Lack of Business Involvement

The team that is designing the solution must have strong representation from the business communities that will be using the final product, because a BI project team that only consists of technical people will almost always fail. The reason is that BI projects attempt to address one of the trickiest areas in IT, and one that usually requires practical business experienceproviding the users with access to information that they didn't already know that can actually be used to change their actions and decisions.

BI projects have another interesting challenge that can only be solved by including business people on the team: Users usually can't tell you what they want from a BI solution until they see something that is wrong. The way to deal with this is to include lots of early prototyping and let the business representatives on the team help the technical people get it right before you show it to all the users.

If you get this wrong and produce a solution without appropriate business input, the major symptom only appears afterward when it becomes clear that no one is using the solution. This is sometimes tricky to establish, unless you take the easy approach and switch off the solution to see how many people complain (not recommended; when you start to lose user trust in a system that is always available and always correct, you will have a nearly impossible time trying to recover it).

Data Quality, Data Quality, Data Quality

A lack of attention to the area of data quality has sunk more BI projects than any other issue in our experience. Every single BI project is always going to have issues with data quality. (Notice that we are not hedging our bets here and saying "most BI projects.") If you have started a BI project and think you don't have a data quality problem, set up a cube and let a few business people have access to it and tell them the revenue numbers in the cube will be used to calculate their next bonus.

Dealing with Data Quality Problems

Data quality problems come in many shapes and sizes, some of which have technology fixes and many of which have business process fixes. Chapter 7, "Data Quality," discusses these in more detail, but the basic lesson in data quality is to start early in the project and build a plan to identify and address the issues. If you mess this up, the first users of your new BI solution will quickly figure it out and stop trusting the information.

Data quality challenges can often be partly addressed with a surprising technique: communication. The data is not going to be perfect, but people have probably been relying on reports from the source transaction systems for years, which, after all, use the same data. Although one approach is to never show any information to users unless it's perfect, in the real world you might have to resort to letting people know exactly what the data quality challenges are (and your plan to address them) before they access the information.

Auditing

The technical people who will be building the BI solution are typically terrible at spotting data quality issues. Despite being able to spot a missing curly bracket or END clause in hundreds of lines of code, they usually don't have the business experience to distinguish good data from bad data. A critical task on every BI project is to identify a business user who will take ownership of data quality auditing from day one of the project and will work closely with the project team to reconcile the data back to the source systems. When the solution is deployed, this quality assurance mindset must continue (by appointing a data quality manager).

One of the most obviously important tasks is checking that the numbers (or measures from the fact table) match up with the source systems. Something that might not be so obvious is the impact that errors in dimension data can have. For example, if the parenting information in product category and subcategory data is not correct, the numbers for the product SKU-level measures will roll up incorrectly, and the totals will be wrong. Validating dimension structures is a critical part of the process.

The Really Big Project

A fairly common approach to BI projects is to spend a year or two building a huge data warehouse with some cubes and deploying a fancy BI client tool. As discussed earlier, we favor a business value-based approach using smaller, targeted projects instead. Assuming that you manage to deliver a solution that the business can use as part of their decision-making process, you must accept that the BI solution starts to get out of sync with the business on the day that you ship it. If the company has committed to a strategy that requires real insight, the BI project will never actually be completed.

Problems Measuring Success

If you commit to the idea of focusing on business value for your BI solutions, one major challenge that you will face is figuring out whether you succeeded. Unlike transaction systems for which you can more easily do things such as measure the return on investment (ROI) by comparing the cost of doing business before and after the system is deployed, the success of BI projects is usually much harder to measure.

Working out the "cost" part of the equation is hard enough, factoring in indirect costs such as maintenance and support of the solution after it has been deployed in addition to hard costs such as software, hardware, and labor. Of course, you cannot stop at working out the costs of the BI solutionyou must also estimate the costs of not having the BI solution. For example, most organizations have people who spend many hours every month or quarter exercising their Excel skills to glue together data from different systems, which will typically take less time and be more accurate when they can access a proper data warehouse instead.

The "benefit" side is even more difficult. How do you quantify the benefits of having access to better information? The intangible benefits such as increased agility and competitiveness are notoriously difficult to quantify, and the best approach in putting together a business case is usually to describe those areas without trying to assign financial numbers. Some of the most successful BI projects are aligned with business initiatives so that the cost of the BI system can be factored into the overall cost of the business initiative and compared with its tangible benefits.