SQL Filter Normalization for MDX Query Translation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Most SQL queries cannot be directly converted into MDX queries due to the inability to translate filter conditions as predicates, resulting in inefficiencies in accessing and analyzing data from multi-dimensional databases like OLAP cubes.
Innovation Solution
A method is developed to receive a query in a first format, parse it to determine the result object and filter condition, convert the filter condition to a normalized form by analyzing semantic context and replacing logical operators, and then translate it into a multi-dimensional expression query in a second format, utilizing normalized operators such as CAND, IAND, UOR, and AGGREGATION FILTER.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If SQL queries are directly converted to MDX queries without normalization, then query translation is simpler, but conversion fails due to inability to translate filter conditions as predicates
Solution Approach 1:
The patent applies preliminary action by normalizing the SQL filter condition into conjunctive normal form (CNF) before translation. This preprocessing step breaks down complex filter conditions into a standardized structure of conjunctions and disjunctions, making them translatable to MDX predicates. The normalization happens before the actual conversion process, enabling successful translation that would otherwise fail.
Solution Approach 2:
The patent introduces an intermediary normalization process between SQL and MDX translation. The filter condition passes through an intermediate CNF representation with normalized operators, which serves as a bridge that enables translation. This intermediary form allows the system to handle complex SQL filter conditions by converting them into a structured format that can be systematically translated to MDX.
2Productivity
If filter conditions are normalized by analyzing semantic context and replacing logical operators, then query translation efficiency improves, but processing time increases due to additional analysis steps
Solution Approach 1:
The patent applies segmentation by dividing the filter condition into discrete logical components (conjunctions and disjunctions in CNF form). Each component is independently identified and replaced with corresponding MDX operators. This segmentation allows systematic processing of complex filter conditions by handling them as separate, manageable units rather than as a monolithic expression.
Solution Approach 2:
The patent changes the parameters of the filter condition by replacing logical operators (AND, OR, NOT) with normalized operators suitable for MDX translation. This parameter transformation converts the filter condition from SQL's logical operator syntax to a normalized form that maps directly to MDX predicate structures, enabling efficient translation while maintaining the original condition's semantic meaning.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
Disclosed is a method and system for receiving a query in a first format, parsing the query to determine a result object, a data source and a filter condition in the query. The filter condition is converted to a normalized filter condition by analyzing a semantic context of one or more member sets in the filter condition, converting the one or more member sets into a normal form and replacing logical operators between the one or more member sets with normalized operators. The normalized filter condition along with the result object and the data source is then translated to a multi-dimensional database query in a second format.