Intermediary Data Storage for Transient Report Updates
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Reports generated from data-warehouses often do not include transient data due to the large intervals between ETL processes, which can lead to incomplete information and inefficiencies in data analysis.
Innovation Solution
Implementing an intermediary data storage system that receives and processes transient data from application databases at shorter intervals, allowing reports to incorporate this data alongside data from the data-warehouse, using a schema identical to the data-warehouse and updating the data-warehouse with transient information.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of information
If data is transferred from application databases to data-warehouse using ETL process at large intervals, then computational resources are conserved, but transient data is excluded from reports leading to incomplete information
Solution Approach 1:
The patent segments the data transfer process into two distinct pathways: (1) a lightweight interim transfer to an intermediary database at frequent intervals to capture transient data, and (2) a comprehensive ETL process to the data-warehouse at larger intervals. This segmentation allows reports to access both transient data from the intermediary database and historical data from the data-warehouse, eliminating information loss while maintaining resource efficiency.
Solution Approach 2:
The patent introduces an intermediary database as a mediator between application databases and the data-warehouse. This intermediary database receives and stores transient data at frequent intervals without requiring full ETL processing, enabling reports to incorporate up-to-date information while the data-warehouse continues its periodic comprehensive updates, thus resolving the contradiction between data completeness and processing efficiency.
2Loss of information
If ETL process is executed frequently to include transient data, then report completeness is improved, but computational overhead increases substantially
Solution Approach 1:
The patent divides the data transfer workload into two segments: a minimal interim transfer to the intermediary database that captures only transient data changes at frequent intervals, and the comprehensive ETL process to the data-warehouse that runs at larger, less frequent intervals. This segmentation ensures data completeness for reports while avoiding the substantial computational overhead of running full ETL processes frequently.
Solution Approach 2:
The patent applies partial action by implementing a lightweight data capture mechanism to the intermediary database that transfers only essential transient data at frequent intervals, rather than performing complete ETL processing. This partial action provides sufficient data for reports without the excessive computational resources required for full ETL execution at the same frequency.
3Reliability
If reports are generated only from data-warehouse data, then data consistency is maintained, but transient data changes are not reflected timely
Solution Approach 1:
The patent segments the data storage architecture into two consistent but independent systems: the data-warehouse maintaining long-term data consistency through periodic ETL processes, and the intermediary database maintaining short-term consistency by capturing transient data changes at frequent intervals. Reports can query both sources, ensuring data consistency from the data-warehouse while reflecting timely transient changes from the intermediary database.
Solution Approach 2:
The intermediary database serves as a mediator that bridges the time gap between data-warehouse updates. It captures and stores transient data changes frequently, allowing reports to access current data state without compromising the consistency maintained by the data-warehouse's periodic ETL process, thus reducing data propagation delay while preserving reliability.
Data Source
AI summary
A data-warehouse is updated with transaction data from various application databases periodically, and the transient data (the data not yet updated in the data-warehouse) from the application databases is transferred to an intermediary data storage. A report generator may consider the data in both the data-warehouse and the intermediary data storage in generating reports. Due to the use of the intermediary data storage, the reports may be generated on current data as well.