Chapter 17: Real-Time Business Intelligence

Man waits not for time nor tideMark Twain

Overview

What does real time mean in the context of data warehousing and business intelligence? If you ask your business users what they mean when they ask for real-time data, youll get such a range of answers that you may decide it simply means faster than they get data today.

Throughout this book weve been assuming the DW/BI system is refreshed periodically, typically daily. All the techniques weve discussed are perfectly appropriate for a daily load cycle, which is the most common cycle for DW/BI systems. In this chapter, we turn our attention to the problem of delivering data to business users throughout the day. This could be every 12 hours, hourly, or possibly even very low latency of seconds.

Well begin the chapter by confessing that were not huge fans of the real-time DW/BI system. This isnt to say we dont think real-time data is interestingjust that putting it in the data warehouse database can be very expensive and may be requested impulsively by end users who havent made a solid case for real-time data. We begin the chapter with a discussion of why, and to whom, real-time data is interesting. We also talk about what makes it challenging to deliver.

Putting aside our doubts , and assuming your business users truly require intraday data, we turn our attention to the hard problem: getting low latency data to the business users. Depending on users requirements, as well as the technologies youre sourcing data from, there are several ways to deliver real-time data. The easiest approach is to skip the data warehouse database entirely and write reports directly on the source systems or even an Integration Services package.

Next, we talk about several approaches for bringing the real-time data into the DW/BI system. These techniques are most valuable for solving the data transformation and integration problems inherent in complex reporting. There are two approaches:

• Segregate the real-time data in its own database.

• Integrate the real-time data with the rest of the DW/BI system.

As we discuss, neither approach is entirely satisfactory.

If your business users need to perform ad hoc analysis on the real-time data, you should set up Analysis Services to process the incoming data stream. An important set of features of Analysis Services, called proactive caching , is the recommended technique for handling real-time data. We describe proactive caching and recommend several alternative configurations for a range of requirements.