Data Warehousing Point-in-Time Views for Freshness
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Enterprise data-warehousing systems face challenges in meeting varying demands for data freshness across different departments, requiring duplicate data copies in multiple databases, which is inefficient and prone to inconsistencies.
Innovation Solution
A data-warehousing system that stores data with both a transaction date-time stamp and an observation date-time stamp, allowing users to view data as it existed at specific points in time without duplicating data, by filtering data based on these stamps to provide accurate and consistent views across different freshness levels.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If duplicate copies of data are maintained in multiple databases or tables to meet varying demands for data freshness, then data freshness requirements for different departments are satisfied, but device complexity and data consistency management become worse
Solution Approach 1:
The patent segments the data access layer by introducing temporal partitioning through point-in-time views. Instead of creating separate physical databases for different freshness requirements, the system divides the single database into temporal segments that can be independently queried. Each department can access data at its required freshness level by querying the appropriate temporal view, eliminating the need for multiple physical databases while still meeting diverse data freshness demands.
Solution Approach 2:
The patent makes a single database serve multiple functions by enabling it to simultaneously provide both real-time data access and historical data access. The database system is enhanced with temporal view capabilities that allow the same underlying data to be presented in different freshness states to different users or applications. This universal approach allows one database to replace multiple specialized databases, reducing overall system complexity.
2Adaptability or versatility
If duplicate copies of data are maintained in multiple databases or tables, then varying data freshness levels are achieved, but data consistency and reliability worsen due to potential inconsistencies
Solution Approach 1:
The patent merges multiple data storage requirements into a single unified database system. By combining real-time data and historical data into one database with temporal view capabilities, the system eliminates data consistency issues that arise from maintaining separate duplicate copies. The single source of truth approach ensures that all departments access consistent data from the same underlying storage, while temporal views provide the appearance of different freshness levels without actual data duplication.
3Speed
If data is updated frequently to meet real-time requirements, then customer service department needs are satisfied, but finance department efficiency worsens due to unnecessary frequent updates
Solution Approach 1:
The patent applies local quality by allowing different departments to access data with different freshness characteristics from the same database. The customer service department can query the most recent data with real-time update frequency, while the finance department can query historical snapshots with lower update frequency. This localized approach to data freshness means that frequent updates are only performed where needed (customer service), while other departments (finance) receive updates at appropriate intervals, improving overall system efficiency.
Solution Approach 2:
The patent introduces dynamic data access where the freshness of data can be adjusted based on the specific needs of different users or applications. The system dynamically presents data at different temporal resolutions - real-time data for customer service operations and historical fixed-period data for finance analysis. This dynamic approach allows the system to adapt data update frequency to local requirements rather than applying a uniform update schedule to all departments.
4Quantity of substance
If a single database is used with frequent refresh periods, then data storage efficiency improves, but querying performance for historical data worsens
Solution Approach 1:
The patent applies preliminary action by pre-computing and storing historical snapshots of data at specific point-in-time views. When historical data is needed, the system can quickly retrieve these pre-prepared temporal views without having to filter and process large volumes of raw data in real-time. This preliminary preparation of historical data states improves query performance for finance departments while maintaining efficient storage through the single database architecture.
Solution Approach 2:
The patent introduces temporal views as an intermediary layer between the physical database storage and the application layer. This intermediary abstraction allows the system to efficiently query historical data without directly accessing the underlying frequent-update tables. The temporal view mechanism mediates between the frequent refresh requirements and historical query needs, providing optimized access paths for different types of data requests while maintaining a single efficient storage system.
Data Source
AI summary
A data-warehousing system allows various areas of an enterprise to view data at varying levels of data freshness. The system acquires data that represents an event in the life of a business enterprise, such as a transaction between the enterprise and one of its customers, and loads this data into a database table. The system then makes the data available for retrieval from the table and stores information indicating when the data was made available for retrieval. In some embodiments, the system also acquires data that is related to and more current than the data representing the event and stores the more current data in the database. The system then stores information indicating when the more current data was stored in the database. Such a data warehouse allows decision-makers in the business to see some information (e.g., customer transaction or account data) up-to-the-moment and other information as it stood at some specific point-in-time, such as at the end of the previous month. The data-warehousing system does this without requiring duplication of data, i.e., without requiring the replication of data in multiple databases or tables. The system allows the enterprise to maintain all of its data in a single database with refresh periods as short as the enterprise wishes. Departments that demand data that changes less frequently can view the data as it stood at some point prior to the last refresh.