Calculated Data Members in Business Intelligence Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Business intelligence servers face challenges in efficiently and simply executing logical structured query language (SQL) queries due to verbose and complex syntax, which hinders data querying and optimization, especially when dealing with online analytical processing (OLAP) sources.
Innovation Solution
Implementing custom or calculated data members in business intelligence servers that allow users to create ad-hoc dimension members through arithmetic operations, enabling users to define and display custom members within queries without requiring administrative tools, and optimizing queries for various data sources.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If traditional SQL queries are used in business intelligence servers, then data can be queried from relational databases and data warehouses, but the syntax becomes verbose and complex, making queries difficult to manage and optimize
Solution Approach 1:
The patent introduces an intermediary layer between the user and the data sources that translates simple natural language or simplified query syntax into complex SQL queries. This mediator handles the complexity of SQL syntax, join operations, and data source-specific queries, allowing users to work with simplified syntax while achieving the same results.
Solution Approach 2:
The system creates virtual copies of data sources and pre-defines complex query structures that can be reused. Instead of writing complex SQL queries each time, users can reference pre-defined query templates or virtual data models that encapsulate the complexity, simplifying the querying process while maintaining access to complex data relationships.
2Productivity
If complex SQL queries are executed against OLAP sources, then analytical processing can be performed, but the query shape does not lend itself well for rewrites and optimization
Solution Approach 1:
The patent segments complex analytical queries into smaller, manageable components that can be independently optimized. By breaking down OLAP queries into discrete operational steps and data transformation stages, the system can apply specific optimization techniques to each segment, improving overall query performance and making optimization more tractable.
Solution Approach 2:
The system dynamically changes query parameters and execution plans based on the specific OLAP data source and query requirements. By adapting query structures to match the optimal format for different OLAP sources and using parameterized queries that can be pre-compiled and cached, the system improves both analytical processing capability and optimization efficiency.
3Ease of manufacture
If administrative tools are required to create custom members, then data modeling can be performed, but the process becomes more complex and requires additional software
Solution Approach 1:
The patent enables end-users to create and define custom data members directly through the query interface without requiring separate administrative tools or software. Users can define custom calculations, aggregations, and data transformations inline within their queries, making the system self-sufficient and eliminating the need for additional administrative software layers.
Data Source
AI summary
A business intelligence (BI) server and repository are described which support a set of customized and/or calculated data members. In accordance with an embodiment, the BI server maintains a connection to a plurality of data sources which may store a set of dimension members. The data source can be relational, file storage based, multidimensional and other types. In accordance with an embodiment, the BI server can accept queries from the user that contain calculated members as a parameter. The calculated member is defined by an expression including multiple dimension members and one or more arithmetic operators. The BI server can parse and validate the query and rewrite the query for the data source. After the query is rewritten and optimized, it is executed against the data source and a set of results is received.