Unified Query Engine for Hierarchical Drill-Through Across OLAP and OLTP
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current business intelligence tools lack comprehensive drill-through and drill-up capabilities between multiple data sources, such as OLTP, relational, and OLAP databases, limiting user navigation and transparency, especially when dealing with large datasets across different storage structures.
Innovation Solution
A method and system that allow users to formulate abstract queries for combined hierarchical dimensions, mapping these queries into data source-specific queries using context expressions to navigate and retrieve data from multiple sources efficiently, enabling seamless drill-down and drill-up operations across various data sources without exposing users to underlying storage complexities.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of information
If users query multiple data sources separately to navigate between aggregated and detailed data, then data retrieval completeness is improved, but operation complexity and time consumption increase
Solution Approach 1:
The patent combines multiple separate queries to different data sources into a single unified query operation. The system executes queries against OLAP and OLTP databases simultaneously through a coordinated query mechanism, merging the results into a unified response. This eliminates the need for users to manually execute separate queries and manually combine results, thereby reducing operation time while maintaining complete data retrieval across hierarchical levels.
Solution Approach 2:
The system performs preliminary setup by establishing query templates and execution plans that define how data will be retrieved from multiple sources. Query structures, join conditions, and data mapping relationships are pre-configured based on the hierarchical dimension definitions. When a user executes a drill-through or drill-up operation, the pre-planned query execution plan is activated, significantly reducing the actual query execution time while ensuring complete data retrieval.
2Speed
If OLAP databases store aggregated data for fast response, then query speed is improved, but access to detailed raw data is lost
Solution Approach 1:
The patent implements a nested data access structure where OLAP aggregated data and OLTP detailed data are accessed in a hierarchical nesting pattern. The system first queries the OLAP database for aggregated results at higher hierarchical levels, then automatically drills down into the OLTP database for detailed raw data at lower levels. This nested access pattern allows the system to maintain the speed advantage of aggregated data while providing seamless access to detailed information when needed, effectively combining both data types in a unified query response.
3Adaptability or versatility
If proprietary API extensions are used for drill-through between data sources, then drill-through capability is provided, but system complexity and security risks increase
Solution Approach 1:
The patent implements a universal query interface that handles drill-through operations between OLAP and OLTP databases without requiring proprietary API extensions. The system uses a standardized query language and execution mechanism that can access both data source types through common protocols. This universal approach provides the necessary drill-through capability while avoiding the complexity and security risks associated with multiple proprietary interfaces, as the system manages data source connectivity through a single unified layer.
Data Source
AI summary
An abstract query is received for querying data sources for a combined hierarchical dimension having at least one hierarchy. The abstract query is mapped into data source specific queries by generating context expressions according to the at least one hierarchy. The data source specific queries are executed in an order according to the at least one hierarchy to produce value results for the combined hierarchical dimension.