Transparency Views Bridge Legacy SQL to Analytic View Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Legacy reporting tools are unable to leverage analytic views (AVs) due to their incompatibility with nonstandard SQL extensions, resulting in slower query execution and missed performance improvements, as they cannot access the acceleration and optimization provided by AV metadata.
Innovation Solution
The introduction of transparency views acts as adapters, allowing standard SQL queries to access and utilize the metadata and optimizations of analytic views, enabling legacy tools to benefit from acceleration and optimization techniques like vector transform, single pass aggregation, and automatic caching, without requiring changes to existing AVs or base tables.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If analytic views with nonstandard SQL extensions are used, then query execution speed and performance are improved, but legacy reporting tools cannot access these views due to incompatibility
Solution Approach 1:
The patent introduces a translation layer that acts as an intermediary between legacy reporting tools and analytic views. This translation layer converts standard SQL queries from legacy tools into the nonstandard SQL syntax required by analytic views, enabling compatibility without modifying the legacy tools themselves while still allowing access to the performance benefits of analytic views
2Speed
If legacy queries are manually reauthored to use analytic views, then performance improvements are achieved, but development time and maintenance costs increase
Solution Approach 1:
The patent implements automatic query translation that performs the reauthoring task automatically without human intervention. The system self-services by intercepting standard SQL queries from legacy tools and automatically translating them into analytic view-compatible syntax, eliminating the need for manual query rewriting while still achieving performance improvements
3Adaptability or versatility
If standard SQL queries are executed against base tables, then compatibility with legacy tools is maintained, but performance optimization from analytic view metadata is lost
Solution Approach 1:
The translation layer serves as an intermediary that receives standard SQL queries and transforms them into optimized queries that leverage analytic view metadata. This allows the system to maintain compatibility with legacy tools while simultaneously unlocking performance optimizations through automatic query rewriting and analytic view utilization
Data Source
AI summary
Herein are techniques for automatically leveraging metadata of an analytic view to accelerate a relational query. In an embodiment, a computer stores model metadata that defines an analytic view that contains a join operation that is based on a dimension column of a dimension table and a join column of a fact table. The analytic view also contains a measure that is based on an aggregation operation and a measure column of the fact table. Also stored is denormalization metadata that defines a transparency view that is based on the analytic view. In operation, a query that references the transparency view is received. The query does not reference the analytic view. The query that references the transparency view is executed based on: a) the denormalization metadata that defines the transparency view, b) the model metadata that defines the analytic view, and c) the measure that is based on the aggregation operation and the measure column of the fact table.