Chapter 18: Present Imperatives and Future Outlook

The endless loop

Our goal with this book was to teach you how to build a successful business intelligence system and its underlying data warehouse using the Microsoft SQL Server 2005 product set. In this chapter we provide a brief review of the overall approach we described and highlight the elements that are critical to success. We conclude with a brief wish list of how we hope to see the Microsoft BI toolset evolve over the next few years .

The Big Risks in a DW/BI Project

We cant resist showing you the Business Dimensional Lifecycle drawing one last time. This time, weve grouped the Lifecycle task boxes into phases that align more closely with the actual order of occurrence than with the major sections of the book. These phases are:

• Requirements, realities, plans, and designs

• Developing the databases and applications

• Deploying and managing the DW/BI system

• Extending the DW/BI system

These phases are essentially linear with each phase building on the previous one.

Figure 18.1: The four phases of the Business Dimensional Lifecycle

Phase IRequirements, Realities, Plans, and Designs

Phase I involves understanding and prioritizing the business requirements, and creating the system architecture, business process dimensional model, and applications specification needed to meet the top-priority requirements.

The biggest problem we see in the projects we get called into is that the DW/ BI team essentially skipped Phase I. Other than doing some project planning around system development tasks , they dove right into developing the databases. This haste leads to unnecessary pain and suffering and is often fatal to the project. A good way to tell if youre headed in the wrong direction is that the technology involved in Phase I should be limited to a project management tool, a word processor, a presentation tool, a modeling tool, and a spreadsheet. If youre installing server machines or SQL Server at this point, youre getting ahead of yourself.

After skipping the requirements step, the next most common problems in Phase I are failing to secure business sponsorship, and failing to take on full responsibility for the BI applications.

Phase IIDeveloping the Databases and Applications

Phase II is the hard, systems-oriented work of designing and developing the ETL systems, the DW/BI databases, and the BI applications. This is the comfort zone for most DW/BI teams . Its where you wrestle with the tough technical issues, like how to handle changes in various attributes, or how to recreate historical facts. Every decision, every design tradeoff in Phase II, must weigh the development effort against the business requirements identified in Phase I. Without those requirements, the design decisions are based on technical expediency. Statements like The eight-character Product description is fineits always worked in the source system and it will save a lot of space, and Well save a lot of time if we include only the base numbers ; the users can create any calculations they like on the fly are warning flags that your developers are making decisions that will undermine the ultimate acceptance and success of the system. These statements are much harder to make when the primary goal of the DW/BI team is to meet a set of clearly defined user requirements.

Building the BI applications is the fun part of Phase II (well, for some of us, its all fun). You get to play with the data, building reports and other applications that you can show to the business users and get immediate feedback on. The technology is pretty easy and straightforwardalthough not without its frustrationsand the development process usually goes quite swiftly. Even if you need to develop complex analytic applications, perhaps including data mining technology, this is generally easier and more fun than slogging through the mountains of bad data that you uncover when building the ETL system.

The biggest risk in Phase II is that some teams think of this phase as the complete project. These teams are doomed to fail because they dont do the upfront planning and design work in Phase I. Although Phase II is where the hard technical challenges are met and overcome , these technical challenges are seldom the point of failure. Missing the underlying business requirements is the root cause of almost every collapse.

Beyond that egregious error, the most common risk we see in Phase II involves underestimating the effort required to extract, cleanse , transform, and load the required data. Its always worse than it initially appeared.

Its also common for a team to omit the BI applications from its project. This is a bad idea. First of all, why cut out the fun and rewarding piece of the project? Second and most important, if you dont pave the path to the door of the data warehouse, only a few hardy souls will make the trek. The other risk is to start designing the BI applications too late, or without involving the business users. Getting user input on the BI applications early will help you validate your design and allow you to make relatively minor adjustments to the DW/BI system that can really please the business users.

Phase IIIDeploying and Managing the DW/BI System

The effort in Phase III revolves around the testing, training, and support needed to reach an all-systems-are-go state. This involves making sure the system works as promised , the data is correct, the users are properly trained and prepared, the system is documented, the standard reports are working and are correct, support is available, and deployment and maintenance plans are in place and tested .

The biggest problem in Phase III comes when the team views its primary goal as delivering technology rather than a solution. In this case, most of the user-oriented work in Phase III is seen as not our job or unnecessary. The team defines success as making the database available. But if the goal is to meet the business requirements, all of the pieces in Phase III are crucial links in the chain. Omit any piece and the chain will break. The team must view success as delivering real, measurable, substantial business value.

Another common problem in Phase III is associated with underestimating the effort required to fully test and maintain the DW/BI system, and to start the planning for ongoing operations too late in the development cycle. For example, your strategy for backing up each days extract is inextricably linked to the ETL system. If you dont think about this issue until the system is developed and ready for deployment, your maintenance plan may be awkward or weak.

Quality-assuring the data in the DW/BI system takes a lot of time to do right. You should get the business users involved with this process. They need to have full confidence in the data, and what better way than to have helped with the testing? Besides, sometimes deep business knowledge is needed to determine whether the data truly is accurate.

Phase IVExtending the DW/BI System

Extending the DW/BI system is about adding new business process dimensional models to the databases, adding new users, and adding new BI applications. In short, its about going back through the Lifecycle again and again, incrementally filling in the bus matrix to build a solid, robust enterprise information infrastructure. One or more of these new business process dimensional models may require data thats near real-time. As we discussed in Chapter 17, including real-time data in the DW/BI system presents some interesting technical and design challenges.

The two main challenges in this phase are an interesting paradox. Often, the success of the first round leads to too much demand and the DW/BI team must carefully balance these demands and maintain an achievable scope based on prioritized business requirements. This may also involve securing additional resources and revisiting priorities with senior management.

At the same time, the DW/BI team must begin an ongoing education program to make sure the organization understands and takes advantage of the incredible asset that is the DW/BI system. In the age of what have you done for me lately, the DW/BI team needs to have a detailed, compelling answer.