The Microsoft® Data Warehouse Toolkit: With SQL Server™ 2005 and the Microsoft® Business Intelligence Toolset

17.2. Executing Reports in Real Time

The most common way to use SQL Server technology to access real-time data is to use Reporting Services. A report written against the transaction system will, by default, be executed on demand using live data. If your system is small, your usage is light, you have few cross-system integration requirements, and no one needs ad hoc access to the real-time data, you can serve real-time data from standard reports.

The main drawback of this approach is that it stresses the transaction system. Many companies decide to build a DW/BI system in part to move reporting off of the transaction systems. A popular report that queries a large section of the relational tables is going to be very expensive to run in real time. You shouldn't abandon Reporting Services immediately, however. It provides several caching features that will help you address this performance problem.

What if your users need to see integrated and cleaned data in real time? Don't abandon Reporting Services yet. It's really easy to source a report directly from an Integration Services package, and depend on Integration Services to perform the transformation and integration.

Later in this chapter we talk about how to populate the relational data warehouse database in real time. The same Reporting Services techniques that we discuss in this section can, of course, be used against a real-time data warehouse database.

17.2.1. Serving Reports from a Cache

To improve the performance of the ...