Data Aggregation System for High-Volume Transaction Processing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
High-volume transaction businesses face challenges in efficiently aggregating and retrieving large datasets due to the limitations of existing database indexing and caching strategies, which result in prolonged processing times even with optimized indexes.
Innovation Solution
A data aggregation and targeting system (D.A.T.A) that employs a two-phase approach using configuration tables to intelligently select the most efficient data tables for aggregation and retrieval, identifying the best source data table with the fewest fields required for each destination table, and dynamically updating and activating/deactivating tables to optimize data processing.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If traditional database indexing and caching strategies are used, then data retrieval speed is improved, but processing time for aggregating large datasets remains prolonged
Solution Approach 1:
The patent segments data into multiple destination tables with different aggregation levels (e.g., by day, by week, by month). Each destination table contains pre-aggregated data for specific time periods, allowing the system to retrieve only the necessary aggregated data rather than processing the entire large dataset for each query type.
Solution Approach 2:
The system performs preliminary aggregation of data into destination tables during off-peak times or in background processes. These pre-aggregated tables are then available for rapid retrieval during actual user queries, eliminating the need to perform time-consuming aggregation operations in real-time.
2Productivity
If automatic aggregation and caching are implemented, then reporting performance is improved, but the system cannot adapt to diverse user requirements for different data subsets
Solution Approach 1:
The patent implements a dynamic query optimization system that analyzes each query request and dynamically selects the most appropriate destination table to retrieve data from. The system adapts to different user requirements by choosing the optimal aggregation level and data subset, rather than using a fixed aggregation strategy.
Solution Approach 2:
Different destination tables are created with different aggregation granularities tailored to specific user needs. For example, one table may contain daily aggregates for financial reporting, while another contains hourly aggregates for operational monitoring. The system selects the appropriate table based on the specific query requirements, providing localized optimization for each use case.
3Loss of information
If data is stored in detailed format in source tables, then information completeness is maintained, but retrieval time for summarized reports increases
Solution Approach 1:
The system maintains detailed source tables for complete information storage while creating separate destination tables for aggregated data. This segmentation allows the system to retrieve summarized data from pre-aggregated destination tables for rapid reporting, while maintaining the ability to access detailed source data when needed.
Solution Approach 2:
Destination tables serve as intermediary structures between the detailed source tables and the query requests. These intermediary tables pre-compute aggregations at different levels, acting as a buffer that translates detailed data requests into efficient retrieval operations without requiring direct access to the full detailed dataset.
Data Source
AI summary
Systems and methods for data aggregation, targeting and acquisition are described. A method may receive data and storing the data in one or more source data tables and select one of the one or more source data tables. The selected source data table includes updated data fields. The method may also identify a plurality of destination data tables that need to be updated, in which each destination data table is linked to and contains an aggregation of a subset of data from the selected source data table, identify one or more data fields in the identified destination data tables that need to be updated with data from the updated data fields in the selected source data table, and determine using the processor, for each identified destination data table, a best aggregation source data table. The best aggregation source data table may be a data table that contains all of the data fields of the destination data table and has the fewest data fields, and the best aggregation source data table may be the selected source data table or another destination data table.