Federating Multidimensional OLAP Data via Native Query Execution
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current data federation solutions are limited to relational data models and cannot effectively process multidimensional online analytical processing (OLAP) data, missing out on features like hierarchies, named sets, and calculated members, which limits the ability of computer systems to integrate and process multidimensional data from different sources.
Innovation Solution
A method and system for federating multidimensional OLAP data from different sources by creating views of OLAP cubes in a data foundation layer, generating metadata objects, and executing native queries to combine and unify OLAP data models with other data models using joins, enabling the use of OLAP features without requiring OLAP system administrator actions or adding new metadata.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If current data federation solutions are used, then relational data models can be processed, but multidimensional OLAP data cannot be effectively processed
Solution Approach 1:
The data foundation layer is designed to universally support multiple data models including both relational and multidimensional OLAP data models. The system can process different data types through a unified architecture that maintains OLAP features like hierarchies, named sets, and calculated members while integrating with relational data sources.
Solution Approach 2:
The data foundation layer acts as an intermediary between different data sources and the business intelligence layer. It provides a common interface that translates and coordinates access to both relational and OLAP data sources, enabling federated queries that leverage OLAP capabilities without requiring direct access to underlying data sources.
2Adaptability or versatility
If data from multiple OLAP sources are integrated, then comprehensive analysis capability is improved, but system complexity increases
Solution Approach 1:
The system segments the data federation architecture into distinct layers: a data foundation layer that handles data source connections and a business intelligence layer that handles analysis. Each layer manages specific responsibilities, with the data foundation layer abstracting the complexity of multiple OLAP sources and the business intelligence layer focusing on query processing and analysis.
Solution Approach 2:
The data foundation layer serves as an intermediary that consolidates connections to multiple OLAP sources, managing the complexity of data integration centrally. This intermediary layer handles schema mapping, data type conversion, and coordinate transformations, shielding the business intelligence layer from underlying data source complexities.
3Productivity
If OLAP features are used in federated queries, then analytical capability is enhanced, but query processing time increases
Solution Approach 1:
The system performs preliminary actions by pre-computing and caching aggregated data from OLAP sources during data loading phases. The data foundation layer can pre-aggregate data across hierarchies and prepare summary statistics, reducing the need for complex calculations during actual query execution and thereby decreasing query response times.
Solution Approach 2:
The system applies partial aggregation at the data foundation layer for frequently accessed data, processing only the necessary portions of data rather than entire datasets. This selective pre-processing approach balances analytical capability with query performance by aggregating only when and where needed.
Data Source
AI summary
A computer-implemented method may comprise creating a first view of a first data source comprising a first online analytical processing (OLAP) cube based on a first user input, creating a second view of a second data source based on a second user input, combining the first view and the second view, and creating metadata objects for elements of the first view and the second view. The method may further comprise generating a query execution plan comprising a first native query and a second native query based on a user-defined query specification and the metadata objects, executing the first native query on the first data source to retrieve a first dataset from the first data source and the second native query on the second data source to retrieve a second dataset from the second data source, and generating a federated dataset using the first dataset and the second dataset.