Snapshot Measure Storage in Data Warehouses
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional approaches are inefficient in modeling and querying snapshot measures in databases, leading to increased data volume, query time, and Extract, Transform, Load (ETL) times due to the non-cumulative nature of these measures and the rapid growth of snapshot data.
Innovation Solution
The facility efficiently stores and queries snapshot measures by employing periodic and interval snapshot techniques, pre-computing range queries, and pre-computing relevant dates to reduce data volume and query times, allowing for the computation of intermediate snapshot measures at any specified time without storing additional measures.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If snapshot measures are stored completely for every time point, then query accuracy is improved, but data volume grows rapidly and query time increases
Solution Approach 1:
The patent segments the continuous time dimension into discrete periodic intervals (e.g., daily, weekly, monthly snapshots). Instead of storing data for every possible time point, the system stores snapshots at regular intervals and uses these segments to answer queries for any time point within or between intervals through interpolation or approximation, thereby reducing data volume while maintaining acceptable query accuracy.
Solution Approach 2:
The patent performs preliminary computation by pre-calculating and storing snapshot measures at periodic intervals during the ETL process. These pre-computed snapshots are stored in the data warehouse and can be quickly retrieved for querying, eliminating the need to compute measures on-demand for every query and reducing both data volume and query time.
2Loss of information
If snapshot data is stored for every time point, then measurement completeness is improved, but ETL time increases
Solution Approach 1:
The patent implements periodic snapshot storage where measures are computed and stored at regular time intervals (e.g., daily, weekly, monthly) rather than continuously for every time point. This periodic approach significantly reduces the number of snapshots that need to be extracted, transformed, and loaded during ETL processes, thereby reducing ETL time while maintaining sufficient measurement completeness for analytical purposes.
3Adaptability or versatility
If range queries are used to compute snapshot measures, then query flexibility is improved, but query performance deteriorates
Solution Approach 1:
The patent performs preliminary computation of snapshot measures at periodic intervals and stores these pre-computed values in the data warehouse. When queries are executed, the system directly retrieves these pre-computed snapshots using simple equality or range conditions on the snapshot date, rather than performing complex range queries and computations at query time. This approach maintains query flexibility while dramatically improving query performance.
4Measurement precision
If non-cumulative snapshot measures are stored, then measurement accuracy is improved, but data complexity increases
Solution Approach 1:
The patent creates a universal snapshot storage structure that handles both cumulative and non-cumulative measures uniformly. The system stores snapshot measures with their associated time stamps and uses a standardized retrieval mechanism that works for all measure types. This universal approach maintains measurement accuracy for non-cumulative measures while simplifying the overall data model and reducing complexity compared to separate handling mechanisms.
Data Source
AI summary
A facility for efficiently storing and querying snapshot measures from a relational data warehouse is provided. The facility allows for the efficient query of measures at any given point in time by efficiently modeling snapshot measures. In one technique, the facility stores periodic snapshots of a metric at longer time intervals, and computes intermediate snapshots of the metric as necessary in between these time intervals. Under this technique, the facility can compute an intermediate snapshot measure of a metric for any specified time by determining the last snapshot measure of the metric from the most recent periodic snapshot, and adding to the last snapshot measure the value of the changes to the metric that happened since the time of the most recent periodic snapshot and the specified time.