Transactional Query Log Analysis for Implicit Join Detection
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing data warehouse and OLAP systems face challenges in automatically identifying analytically useful relationships in transactional data, as these relationships may not be explicitly defined in the schema or metadata, and users may be unaware of such connections.
Innovation Solution
A system that analyzes query logs from a transactional data store to identify join relationships, dimensions, and hierarchies by detecting coinciding fields and their frequencies, using aggregations and groupings in queries to form n-dimensional cubes and automatically generate analytical queries.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If automated query generation uses explicitly defined schema information (primary key and foreign key relationships), then query generation is more reliable, but it misses analytically useful relationships that are not explicitly defined
Solution Approach 1:
The patent introduces query logs as an intermediary data source between the transactional database and the automated query generation tool. By analyzing actual query patterns in the logs, the system discovers implicit relationships that are not captured in the explicit schema, thereby bridging the gap between reliable schema-based generation and versatile relationship detection
Solution Approach 2:
The system performs self-service by automatically analyzing its own query logs to identify join relationships and dimensions. This self-analysis enables the system to discover analytically useful relationships without manual intervention while maintaining the reliability of explicit schema information
2Measurement precision
If manual configuration of data structure details is required, then analytical accuracy is improved, but the process becomes more involved and time-consuming
Solution Approach 1:
The system performs preliminary action by pre-analyzing query logs to automatically identify join relationships, dimensions, and hierarchies before automated query generation begins. This preliminary discovery phase captures analytically useful relationships that would otherwise require manual configuration, reducing both configuration time and potential errors
Solution Approach 2:
The system uses feedback from actual query execution patterns in the logs to automatically refine its understanding of data relationships. By continuously analyzing how queries actually access and join data, the system improves analytical accuracy without requiring manual configuration of data structure details
3Adaptability or versatility
If the system analyzes query logs to discover implicit relationships, then relationship detection capability is improved, but system complexity increases
Solution Approach 1:
The patent makes the database system multi-functional by enabling it to simultaneously handle traditional transactional operations and automated analytical query generation. The same system that executes transactions also analyzes their patterns to discover relationships, eliminating the need for separate complex relationship discovery tools
Data Source
Figure 1
Figure 2
Figure 3
AI summary
A corpus of information describing queries used to access a transactional data store may be used to identify analytical relationships that are not explicitly defined in a schema or supplied by a user. Join relationships may be identified based on field coincidence in elements of queries in the corpus. Join relationships may be indicative of dimensions and attributes of a dimension. Hierarchy levels for a dimension may be identified based on factors including data type, reference in an aggregating clause, and reference in a grouping clause.