Parameterized Virtual Semantic Layer Columns for Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current virtual semantic layers lack the ability to parameterize columns, leading to inaccuracies and inefficient search processing due to the large cardinality of measures and dimensions, which complicates data management and integration, especially with varying currency values.
Innovation Solution
A novel framework that enables parameterization of columns within a virtual semantic layer, allowing for dynamic query optimization by using templated Structured Query Language (SQL) to execute parameterized queries, thereby simplifying data selection and aggregation.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If traditional semantic layers expose all measures and dimensions to users, then users can access comprehensive data, but the system becomes inefficient and inaccurate due to large cardinality and complex data management
Solution Approach 1:
The patent segments the large set of measures and dimensions into parameterized groups using templates. Instead of exposing all 180 currency measures individually, the system creates parameterized templates that represent groups of related measures, dividing the overwhelming cardinality into manageable, organized segments that can be efficiently processed.
Solution Approach 2:
The patent applies parameterization to transform fixed column names into parameterized templates with variables. By changing from static column references to parameterized forms with placeholders and arguments, the system enables dynamic query construction that adapts to different data scenarios while maintaining efficiency.
2Loss of information
If the semantic layer includes all possible measures and dimensions, then data completeness is improved, but data management complexity and integration difficulty increase
Solution Approach 1:
The patent creates universal parameterized templates that can represent multiple specific measures and dimensions through a single template definition. These templates serve multiple functions by adapting to different arguments and contexts, allowing the system to maintain data completeness while reducing management complexity through multi-functional template structures.
Solution Approach 2:
The patent implements a nested structure where parameterized templates contain and organize groups of related measures and dimensions. The templates act as containers that nest multiple specific columns within a unified structural framework, enabling hierarchical organization that preserves data completeness while simplifying management through nested grouping.
3Adaptability or versatility
If users can select from all available currency measures, then currency selection flexibility is improved, but navigation and selection efficiency deteriorate due to lack of hierarchy
Solution Approach 1:
The patent adds a hierarchical dimension to currency measure selection by organizing measures within parameterized templates. Instead of presenting a flat list of 180 currencies, the system creates a multi-dimensional selection space where templates provide categorical groupings and arguments provide specific selections, adding organizational structure that improves navigation while preserving flexibility.
Data Source
AI summary
The disclosed systems and methods provide a novel framework that parameterizes columns in a templated virtual semantic layer. The disclosed framework enables inter- and cross-column relationships between stored data within a SQL database to be determined and stored, and then leveraged at query time to enable an optimized search of the data within the database. The disclosed framework enables a database to be construed, modified and/or configured according to a hierarchy, and/or with types of metadata, that corresponds to the determined column relationships. This not only reduces the amount of data stored and reduces the time to process queries, but also enables a more streamlined approach for searches to be constructed and executed, which can increase the performance of the database and the operations of systems that are backed by the database.