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

17.3. Loading the DW/BI System in Real Time

It's time to discuss how to load the DW/BI system in real time. The problems with loading the relational data warehouse database are design issues, not technology issues. There's no technical reason that you couldn't trickle-feed your relational data warehouse database every hour, even every minute, possibly even faster. Integration Services has lots of features that make this possible, even easy. The problem, as we've already discussed, is meeting a diverse set of requirements with a single integrated system.

17.3.1. The Integrated Approach

The most intellectually appealing solution is to integrate the real-time data into the DW/BI system. This means processing dimension changes in real time: updating Type 1 attributes in place for all conformed dimensions, and correctly handling all Type 2 attribute changes for all conformed dimensions.

In this approach, the Integration Services packages are very similar to what you may already have designed for daily processing. In the simplest case, you'd simply run those packages more often. This assertion is over-simplifying the situation, but the main point is that the real-time packages are not wildly different from the packages for daily processing. Here are some differences, and issues to watch out for:

• The source data must be limited to changed rows. Sometimes, with daily processing, you may pull all dimension rows—and occasionally all fact rows—and use Integration Services to identify changes. ...