Factless Materialized Query Table for OLTP Metric Reporting
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Classic data warehouse designs based on star schema in OLTP databases face inefficiencies due to complex joins, scalability issues, and double counting of metrics, especially when dealing with sparse data and temporal metrics, which are not up-to-date.
Innovation Solution
Creating a factless materialized query table with maximum sparsity that includes all primary keys from a set of base tables, allowing for efficient data aggregation and avoiding double counting by using determinants to uniquely identify rows and define granularity values.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If a classic data warehouse design based on star schema is used, then reporting capabilities are improved, but query complexity and join operations increase significantly
Solution Approach 1:
The patent pre-computes and materializes sparse join results into a factless materialized query table before reporting queries are executed. This preliminary action stores the complex join relationships between dimension tables, so that subsequent reporting queries can directly query the materialized table without performing complex joins at query time, thus reducing query complexity while maintaining reporting capabilities
Solution Approach 2:
The patent introduces a factless materialized query table as an intermediary structure between dimension tables and reporting queries. This intermediary table captures the sparse relationships between dimensions and pre-computes join results, allowing reporting queries to access aggregated data without directly joining multiple dimension tables, thereby simplifying query operations
2Productivity
If materialized views are used to store query results, then query performance is improved, but storage space requirements increase
Solution Approach 1:
The patent creates a factless materialized query table with maximum sparsity, storing only the necessary primary key combinations and their relationships without duplicating full dimension data. This local quality approach materializes only the specific join relationships needed for reporting, rather than materializing entire dimension tables or all possible combinations, thus improving query performance while minimizing storage requirements
Solution Approach 2:
The patent employs a sparse data structure for the materialized query table that allows gaps and null values, storing only present primary key combinations. This porous approach enables the table to occupy minimal storage space by not materializing nonexistent or null relationships, while still providing fast access to existing relationships through efficient indexing on primary keys
3Device complexity
If dimensional fact model with de-normalized structures is used, then number of joins is reduced, but data freshness and temporal accuracy are compromised
Solution Approach 1:
The patent creates a materialized copy of the sparse join relationships between dimension tables, storing primary key combinations and their relationships. This copy is updated through triggers when source tables change, providing fresh data without requiring complex joins. The materialized table copies only the necessary relationship information, maintaining data freshness through automatic updates while avoiding the join complexity of the original dimensional model
4Loss of information
If complex joins are performed to retrieve data from multiple base tables, then data completeness is improved, but query execution time increases
Solution Approach 1:
The patent pre-computes the complete set of primary key combinations and their relationships across multiple base tables, materializing this information in advance. This preliminary computation ensures all data relationships are captured, so that subsequent queries can retrieve complete data without performing complex joins, thus maintaining data completeness while eliminating the time cost of complex join operations
Solution Approach 2:
The patent introduces a factless materialized query table as an intermediary that pre-stores the complete relationship information between dimension tables. This intermediary captures all necessary data relationships through pre-computed sparse joins, allowing queries to retrieve complete data by simply querying the materialized table without executing complex join operations, thereby maintaining data completeness while reducing query execution time
Data Source
AI summary
Storing and/or accessing data in a transactional database, with use of the following technique: (i) selecting a set of base tables in a transactional database; and (ii) creating a factless materialized query table, having maximum sparsity, for the set of base tables. The set of base tables includes at least two base tables. The set of base tables includes a set of keys including at least two distinct primary keys. The factless materialized query table includes one record associated with each record in the set of base tables. Each record in the materialized query table includes a value for every primary key in the set of base tables.