Ambiguous Join Metadata Generation for Accurate Data Aggregation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods in relational databases are unable to effectively aggregate measures across all dimensions in result sets containing ambiguous joins, leading to incorrect subtotals and inefficiencies, as they require re-reading data at different aggregation levels and struggle with table layouts involving multiple joins.
Innovation Solution
A system and method that generates metadata for table-like data structures with inner or left-outer joins, identifying ambiguous join dimensions and special measures, allowing for data aggregation at specified levels without re-reading data, and enabling correct handling of ambiguous joins by an analytics engine without knowledge of the internal structure.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If data is read multiple times at different aggregation levels, then aggregation accuracy is improved, but query performance deteriorates
Solution Approach 1:
The system performs preliminary identification of ambiguous joins and generation of metadata before executing the query. The metadata includes information about ambiguous join dimensions and special measures that need special handling. This preliminary action allows the aggregation engine to process data correctly in a single pass without needing to re-read data at different aggregation levels, thus maintaining both accuracy and performance.
2Adaptability or versatility
If traditional join aggregation methods are used, then simple queries are handled correctly, but complex queries with multiple joins and ambiguous joins fail or produce incorrect results
Solution Approach 1:
The system introduces metadata as an intermediary between the query and the data aggregation process. The metadata contains information about ambiguous join dimensions and special measures, which guides the aggregation engine through complex table layouts with multiple joins. This intermediary allows the system to handle both simple and complex queries reliably without needing to re-read data, resolving the contradiction between versatility and reliability.
3Loss of information
If data is aggregated at all levels, then complete information is obtained, but data processing time increases
Solution Approach 1:
The system applies local quality by treating different dimensions and measures differently based on their role in ambiguous joins. The metadata identifies which dimensions are ambiguous join dimensions and which measures are special measures, allowing the aggregation engine to apply special handling only where needed. This selective approach maintains information completeness for ambiguous joins while avoiding unnecessary processing time for straightforward aggregations.
Data Source
AI summary
A system, method and article for handling ambiguous joins among a plurality of tables based on a query are disclosed. Metadata associated with the plurality tables is generated. The metadata comprises ambiguous join dimensions and special measures, where the ambiguous join dimensions provide a list of all dimensions used in conditions of the query that are ambiguous, and the special measures provide a list of measures that are affected by the ambiguous join dimensions. Data is aggregated in the plurality of tables according to an aggregation level expressed in the query and based on the metadata.