Temporal Data Update Methodologies for Data Warehousing

Abstract

Data warehouse applications and business intelligence (BI) communities have been increasingly feeling the need to collect temporal data for accountability and traceability reasons. This necessitates building capabilities to capture and view transaction lineage in data warehouses based on timestamps. Data as of a point in time can be obtained across multiple tables or multiple subject areas, resolving consistency and synchronization issues. In this article we discuss implementations such as temporal data update methodologies, coexistence of load and query against the same table, performance of load and report queries, and maintenance of views on top of the tables with temporal data. We show how to pull data from heterogeneous sources and how to perform join operations with inequality predicates with more than one source table and then load them in analytical subject area tables maintaining transaction lineage. We propose several business views based on timestamp filters for use by different applications.

SQL Scorecard for Improved Stability and Performance of Data Warehouses

“…They reported that their design makes analytics engines to scale large data sets, provided predictable execution times, and reduced contention. Gelbard and Spiegler (2005) proposed a Temporal Branching technique to resolve conflicts in heterogeneous and distributed databases by integrating and extending temporal oriented databases and temporal versioning (Rahman, 2014). Dempster et al (2003) proposed a parallel query option model which provides the predictions against actual measurements obtained.…”

Enterprise Data Warehouse Governance Best Practices

“…Data warehouse implementation has been a research topic for more than a decade. Most previous work on data warehousing focused on design issues (Rahman, 2013a;Rahman, 2014), data maintenance strategies in connection with relational view materialization (Rahman, 2013b) and implementation aspects (Rahman, 2010a;Rahman, 2014). A lot of research work has been done in the field of data warehouse refresh using ETL (Extract-Transform-Load) tools, with different alternative tools being proposed (Simitsis, et al, 2005).…”

“…The data originates from operational databases. The source systems provide change timestamp for temporal data (Rahman, 2014). In a data warehouse source data is initially landed in staging databases.…”