Pre-computed Aggregate Data Retrieval System
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current systems for calculating and retrieving analytic data from large transactional databases, such as those managing payment card transactions, face inefficiencies due to the need for real-time calculations, which result in slow query response times and require custom data structures for aggregation, leading to data duplication and maintenance challenges.
Innovation Solution
A data management system that pre-computes and stores aggregate values in flexible database structures, using a fact-analyzer module to generate and store aggregated values with links to measure and collection data elements, allowing for quick retrieval and periodic updates without duplicating data.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If real-time aggregation calculations are performed on large transactional databases, then query accuracy is maintained, but query response time increases significantly
Solution Approach 1:
The system pre-calculates aggregation results and stores them in a materialized view structure before queries are executed. When a query is received, the system retrieves pre-computed results rather than performing real-time calculations on the entire transactional database, thus maintaining accuracy while dramatically reducing response time.
Solution Approach 2:
The materialized view structure organizes pre-computed aggregation data in a hierarchical manner, where different levels of aggregation are stored locally. This allows the system to retrieve only the specific aggregation level needed for each query, optimizing both accuracy and response time by avoiding unnecessary data processing.
2Speed
If separate aggregate tables are created to improve query efficiency, then query speed increases, but data structure complexity and maintenance burden increase
Solution Approach 1:
The materialized view structure serves multiple functions simultaneously: it stores pre-computed aggregation results, provides hierarchical data organization, and enables various types of queries (sum, count, average, etc.) without requiring separate tables for each aggregation type. This universal structure reduces complexity while maintaining query speed.
Solution Approach 2:
The system dynamically updates the materialized view structure based on incoming transactions and query patterns. Rather than maintaining static aggregate tables that require manual updates, the materialized view automatically refreshes and adapts to changing data, reducing maintenance burden while preserving query performance.
3Speed
If aggregate tables store duplicate information to improve query performance, then retrieval speed increases, but data redundancy and storage requirements increase
Solution Approach 1:
The materialized view structure organizes aggregation data in a nested hierarchical format, where higher-level aggregations contain references to lower-level data rather than complete duplicates. This nested structure enables fast retrieval by allowing queries to access only the necessary level of aggregation while minimizing redundant data storage through shared references.
Data Source
AI summary
A data management system for calculating and retrieving analytic data involves a computing device coupled to a database. The system includes a measure data structure including a measure-data element, a collection data structure including a collection-data element, and a fact data structure configured to store a fact-data element which includes an aggregated value, a link to the measure-data element, and a link to the collection-data element. The data structures may be, for example, database table structures. The system also includes a fact-analyzer module configured to generate the aggregated value (i.e., a result generated by a query computation performed on the database). The measure-data element defines how the aggregated value is calculated and the collection-data element defines a categorization of the aggregated value. The fact-analyzer module is also configured to store the aggregated value and links to the measure-data element and the collection-data element in the fact-data element.