Dimensional Hierarchy Collapsing for SQL Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Structured Query Language (SQL) queries used in database operations often return more rows than necessary, especially when dealing with requests containing dimension hierarchies, leading to sub-optimal performance and large result sets.
Innovation Solution
A method is introduced to collapse a data tree hierarchy into a two-column data structure, where characteristic leaf-node key values are in the first column and corresponding System Identification (SID) values are in the second column, allowing for efficient aggregation and filtering, thereby generating an optimal SQL query that minimizes the number of rows returned.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If a SQL query is generated based on dimension hierarchy leaf-node data, then the query can be constructed using available BW InfoObjects, but the result set contains many more rows than necessary
Solution Approach 1:
The patent applies preliminary action by pre-calculating and storing the mapping between hierarchy node SIDs and their corresponding leaf-node key values in a translation table before query execution. This allows the query generation process to directly use aggregated SID values without having to traverse and aggregate leaf-node data at query time, thus reducing the result set size while maintaining query correctness.
Solution Approach 2:
The patent introduces a translation table as an intermediary between the BW InfoObjects and the SQL query execution. This translation table stores the pre-computed mapping relationships, acting as a mediator that translates hierarchy node SIDs into their corresponding leaf-node key values, thereby avoiding direct aggregation of large numbers of leaf-node rows during query execution.
2Reliability
If data is aggregated at the leaf-node level, then all necessary detail data can be retrieved, but query performance deteriorates due to processing millions of rows
Solution Approach 1:
The patent performs preliminary aggregation by pre-computing the mapping from hierarchy node SIDs to their descendant leaf-node key values and storing this mapping in a translation table. This allows queries to aggregate data at the appropriate hierarchy level by working with a small set of SID values rather than traversing and aggregating millions of leaf-node rows, thus maintaining data completeness while dramatically reducing query processing time.
3Loss of information
If the query returns all leaf-node rows, then complete detail data is available, but the result set size becomes unmanageably large
Solution Approach 1:
The patent uses a translation table as an intermediary structure that stores the mapping between hierarchy node SIDs and their corresponding leaf-node key values. This intermediary allows the system to work with compact SID representations during query processing while still being able to retrieve complete leaf-node detail data when needed, thus maintaining data detail availability while keeping result set sizes manageable.
Solution Approach 2:
The patent creates a copied representation of the hierarchy relationships in the translation table, storing the mapping from parent node SIDs to child node key values. This copied structure allows queries to operate on a compact representation of the data hierarchy without losing access to the complete detailed data, effectively decoupling the query processing workload from the actual data volume.
Data Source
AI summary
A tree-structured database is collapsed into a two-column table. The two-column table includes a characteristic key value for a given leaf node in the first column and the system identification (SID) relating to the characteristic key value in the second column and also identifying the node that leads to the characteristic key value. Data is presented by coupling the characteristic key value with a transaction table. A query that is formulated in an infocube may be translated to a relational data base query, collapsed, and reformulated into an infocube result set, or vice versa.