Multi-axis Expansion in Relational Database Reports
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing relational database systems face difficulties in performing multi-axis expansion of result tables, particularly when both rows and columns need to be expanded, as this requires complex subqueries and significant storage resources, making it inefficient and costly, especially when using OLAP cubes which are storage-intensive and require pre-computation of all possible combinations.
Innovation Solution
A relational database device and method that performs multi-axis expansion by creating subqueries based on existing expanded nodes, using a SELECT filter and a WHERE filter to retrieve data from a relational database, allowing for dynamic expansion without pre-computation, thereby improving efficiency and scalability.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If OLAP cubes are used for multi-axis expansion, then query speed is improved, but storage requirements increase exponentially
Solution Approach 1:
The patent pre-computes and stores only the aggregation levels that are actually needed for the current query, rather than pre-computing all possible combinations. This allows fast retrieval of pre-computed aggregations while avoiding the exponential storage growth of full OLAP cubes.
Solution Approach 2:
The system computes aggregations locally at each node in the query execution plan, storing only the specific aggregation results needed for that particular query path. This localized approach avoids the global pre-computation of all possible aggregations required by traditional OLAP cubes.
2Loss of time
If all possible sub-query combinations are pre-computed, then query response time is reduced, but processing time for updates increases
Solution Approach 1:
The system dynamically determines which aggregations to compute and store based on the actual query being executed and the current expansion state. This dynamic approach allows the system to adapt to changing query patterns without requiring full pre-computation, reducing update overhead while maintaining fast query response for needed aggregations.
3Quantity of substance
If multi-axis expansion is implemented in relational databases without OLAP cubes, then storage efficiency is improved, but query complexity increases
Solution Approach 1:
The patent introduces an intermediary layer that translates complex multi-axis expansion queries into a series of simpler sub-queries against the relational database. This intermediary translation layer handles the complexity of coordinating multiple expansions, allowing the underlying relational database to remain simple while supporting sophisticated multi-axis query capabilities.
Data Source
AI summary
This disclosure describes systems, methods, and apparatus for expanding a row or column in an existing report, sourced from a relational database, where a column or row, respectively, of an opposite axis has already been expanded. Such a need often arises in reports sourced from a SQL database, and overcomes the storage-draining need to pre-compute all possible arrangements of data from the database prior to displaying the report. In some embodiments, a client can make a request for a multi-axis expansion, and a server can create filters for use in sub queries, one for each level of expansion in an axis of the report opposite to the axis where expansion is requested, the sub queries can be executed, results sets can be combined/formatted to form a new report, and the new report can be returned to the client for stitching into the existing report.