Business Intelligence Language Macros for Complex Data Topologies
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing query languages like SQL and MDX are limited in expressing complex data analyses and require multiple queries to retrieve complex data topologies, leading to intricate expressions and potential errors, especially when dealing with non-orthogonal dimensions and complex data structures.
Innovation Solution
A business intelligence language expansion and compilation platform that receives macro expressions, maps information from a data store, and generates native queries to facilitate intuitive and flexible data retrieval, allowing for recursive and reentrant operations to handle complex queries and data topologies.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If SQL is used for data retrieval, then data can be retrieved from data stores, but complex data analysis and high-level calculations cannot be performed
Solution Approach 1:
The patent introduces MDX as an intermediary language between the user and the underlying data storage system. MDX provides high-level constructs for multi-dimensional analysis, calculated members, and hierarchical navigation, while the system automatically translates these into SQL queries that can be executed by relational databases. This mediator approach allows users to perform complex data analysis without directly managing the complexity of multiple SQL queries and joins.
2Adaptability or versatility
If MDX is used for advanced analysis, then multi-dimensional analytical queries can be performed, but an underlying multi-dimensional model (information Cube) must be authored
Solution Approach 1:
The system automatically generates the multi-dimensional information cube model by copying and transforming the existing relational database schema. Instead of requiring manual authoring of cubes, the system creates the necessary MDX-compatible data structures and relationships by processing the relational schema, thereby providing MDX's advanced analytical capabilities without the manual modeling effort.
3Adaptability or versatility
If multiple queries are issued to retrieve complex data topologies, then data can be retrieved, but atomicity of the read transaction is not guaranteed and additional data processing is required
Solution Approach 1:
The patent merges multiple separate SQL queries into a single atomic query by using common table expressions (CTEs) and temporary tables within one transaction. The system rewrites complex multi-query operations as unified SQL statements that retrieve all necessary data in one atomic operation, ensuring either all data is retrieved successfully or none is retrieved, thereby maintaining transaction atomicity while still supporting complex data topology retrieval.
4Adaptability or versatility
If complex expressions are created to describe complex data topologies, then data can be retrieved, but the expressions become large and intricate requiring specialized training
Solution Approach 1:
The system segments complex queries into reusable macro expressions that can be defined once and invoked multiple times. By breaking down intricate query logic into modular, named components with clear parameters, the system makes complex data topology retrieval manageable and understandable without requiring specialized training. These macro expressions encapsulate complexity internally while presenting simple interfaces to users.
Data Source
AI summary
According to some embodiments, a business intelligence language expansion and compilation platform may receive a business intelligence language expression, containing at least one macro sub-expression from a user. Information may then be accessed in a business and data models data store to perform expansion and compilation operations on the received business intelligence language expression. As a result of these operations, the system may provide a native query to a data source database. A business intelligence language result set manager may retrieve data from a data source database responsive to the native query and output a business intelligence language result set to the user in response to the business intelligence language expression. According to some embodiments, the business intelligence language is reentrant such that the received business intelligence language expression is evaluated in one context and uses a result of another business intelligence language expression evaluated in a possibly different context.