Relational Set Operations on Multidimensional Data
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current data analyzing applications face inefficiencies in performing relational set operations, such as union, intersect, and except, on multidimensional data sources, as these operations are not natively supported, requiring time-consuming ETL processes into relational databases, which are error-prone and inefficient.
Innovation Solution
A system and method that intercepts data agnostic business intelligence queries with relational set operations, decomposing them into OLAP sub-queries for execution on multidimensional data sources, using a query interceptor, decomposer, and operation module to perform set operations locally with the help of a relational query provider.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If ETL processes are used to transform dimensional data into relational databases for set operations, then relational set operations can be performed, but the process becomes time-consuming and inefficient
Solution Approach 1:
The patent introduces an intermediary layer (the query system) that translates relational set operations into native OLAP operations. Instead of transforming data from dimensional to relational format, the system acts as a mediator that converts the operation type while keeping the data in its native OLAP format, thereby eliminating ETL time while maintaining operational accuracy
Solution Approach 2:
The system changes the parameter of the operation mode from relational algebra-based set operations to native OLAP operations. By altering how the operations are performed (from SQL-based to OLAP-based), the system maintains the ability to perform union, intersect, and except operations while eliminating the need for time-consuming data transformation processes
2Ease of operation
If ETL processes are used to extract and transform dimensional data, then set operations can be performed on relational data, but the process requires IT department intervention and is error-prone
Solution Approach 1:
The query system serves as an intermediary that handles the complexity of operation translation internally. Users can perform set operations using familiar relational syntax while the system mediates by translating these operations into native OLAP operations, thereby simplifying the user experience while managing the operational complexity behind the scenes
Solution Approach 2:
The system enables self-service by allowing business users to perform set operations directly on dimensional data without requiring IT department intervention. The automated translation mechanism handles what would otherwise require manual ETL configuration, making the process accessible to end users while reducing human error
3Speed
If native OLAP operations are used, then fast response to queries is achieved, but relational set operations are not supported
Solution Approach 1:
The query system provides multi-functionality by supporting both native OLAP operations and relational set operations through a unified interface. It can handle complex queries involving union, intersect, and except operations on OLAP data sources while maintaining fast response times, effectively making the system adaptable to multiple operation types without sacrificing performance
Solution Approach 2:
The system dynamically changes the operational parameter based on the query type. For simple queries, it uses native OLAP operations for maximum speed; for queries requiring set operations, it translates them into equivalent OLAP operations. This parameter adaptation allows the system to maintain both speed and versatility across different query scenarios
4Reliability
If data is extracted from OLAP sources for set operations, then set operations can be performed, but the computation must be done in relational databases rather than directly in the BI system
Solution Approach 1:
The query system acts as an intermediary computation engine that performs set operations directly on OLAP data sources. Instead of extracting data to relational databases for computation, the system mediates by translating set operations into native OLAP operations that execute within the OLAP environment, thereby maintaining computational accuracy while dramatically improving efficiency by eliminating data extraction and transformation steps
Data Source
AI summary
A tabular set provider is provided for processing a data agnostic business intelligence query with relational set operations, herein called a relational query against a multidimensional datasource. The tabular set provider comprises a query interceptor, a query decomposer, an OLAP query provider interface, and an operation module. The query interceptor is provided for detecting and intercepting a data agnostic Business intelligence query that has relational set operations that is issued against one or more multidimensional data sources. The query decomposer is provided for decomposing the operands of the set operation in the input query into one or more sub-queries. The OLAP query provider interface is provided for sending the OLAP supported sub-queries to an OLAP query provider and receiving results. The operation module is provided for using set operation functionality provided by a relational query provider that performs the set operations locally on the results of the OLAP data extraction.