Refreshing Data Warehouses with near Real-Time Updates

Abstract

In traditional decision support systems, data warehouses have been used to analyze historical information. In the past it was relatively easy to keep data acquisition and maintenance activities to an as-needed basis by using batch windows at night when the business users went home. Now, however, decision makers need up-to-date information to make strategic business decisions, requiring data warehouses to be refreshed several times a day. This paper presents a technical outline for a near real-time decision support system where data warehouses are refreshed using a metadata model and incremental refreshes to increase the frequency of batch cycle runs. We propose a staging area in the data warehouse to capture data updates from external sources. Based on new data in the staging tables, we propose to load the actual analytical tables in the data warehouse using the database system as a transformation engine. We also propose making the database transformation tasks, such as stored procedures execution, metadata driven. The metadata model lets the stored procedures in different business and analytical subject areas run only when source data changes in the source subject area tables, and then implements a delta refresh of tables for which new data has arrived from the operational databases. Skipping unnecessary loads via this metadata-driven approach allows for faster cycle refreshes. The cycle refresh time statistics captured from an actual production data warehouse demonstrate the excellent reductions in cycle times achieved by our batch technique.