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.