The MicrosoftВ Data Warehouse Toolkit: With SQL ServerВ 2005 and the MicrosoftВ Business Intelligence Toolset - page 117


Weve spent a lot of time in this chapter talking about the challenges of real-time DW/BI systems. These challenges are even greater if youre trying to deliver real-time data thats consistent across the enterprise with the well-managed data warehouse database.

Our goal in this chapter is to present you with a realistic description of the challenges, the alternatives and their pros and cons, and practical advice for implementing each significant alternative.

These alternatives start with encouraging you to keep the real-time data out of the data warehouse database, and away from the DW/BI team. Weve seen strategic-thinking DW/BI teams get sucked into delivering real-time data, never to be heard from again. A lot of the business requirements for real-time data can be met by the transaction system owners using the very nice functionality in SQL Server, especially Reporting Services but also Analysis Services, directly against the transaction systems.

If you need to present data in real time that integrates information from multiple sources, you will need to use Integration Services. Some problems can be solved by building reports (or cubes) that are populated directly from the Integration Services pipeline. Most often, however, you will want the results of these expensive integration and transformation operations in the relational database. We described several designs and techniques for populating the DW/ BI system in real time.

We believe that the greatest benefit to the real-time functionality offered in SQL Server 2005 will be to software vendors who are building and improving operational systems, and the future customers of those systems. Ideally, the operational systems will present information in a useful, timely , flexible way. We hope software developers will use these features to deliver products that delight rather than frustrate their users.

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.

image from book
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.