Semantic Data Slice Framework for OLTP Read Separation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
OLTP systems are optimized for transaction entry workloads but suboptimal for large-scale data extraction, leading to performance degradation and inefficient queries due to complex table joins and unpredictable query patterns.
Innovation Solution
Implement a twin database architecture with a read-only operational store (RODS) synchronized with an OLTP database, using column-oriented storage and semantic layers to optimize read operations, separate physical and logical layers, and employ selective replication for near-real-time data synchronization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If OLTP database is optimized for transaction entry workloads with normalized data model and row-oriented storage, then transaction processing efficiency is improved, but read performance for extraction workloads deteriorates
Solution Approach 1:
The patent segments the database system into two distinct components: an OLTP database optimized for transaction processing with normalized data model and row-oriented storage, and a separate RODS (Read-Only Data Store) optimized for extraction workloads with denormalized data model and column-oriented storage. This segmentation allows each component to be optimized for its specific workload type without compromising the other, resolving the contradiction between transaction processing efficiency and read performance for extraction workloads.
2Loss of information
If table joins are used to present functional view of data for reporting and extraction, then data completeness is improved, but query performance deteriorates
Solution Approach 1:
The patent applies preliminary action by pre-computing and storing denormalized data views in the RODS before extraction workloads are executed. Data is transformed and aggregated in advance according to common reporting requirements, so that when extraction queries are run, they can retrieve pre-prepared functional views directly without performing complex table joins at query time. This eliminates the contradiction between data completeness and query performance.
3Speed
If indexes are added to improve query performance, then read speed is improved, but transaction processing speed deteriorates
Solution Approach 1:
The patent extracts the indexing and optimization functions from the OLTP database and relocates them to the separate RODS. The RODS maintains its own optimized indexes and data structures specifically tailored for extraction and reporting workloads, while the OLTP database remains lightweight and optimized purely for transaction processing. This extraction resolves the contradiction by allowing read speed optimization without impacting transaction processing speed.
4Adaptability or versatility
If custom reports are written by implementers without full understanding of transaction data model, then reporting flexibility is improved, but query efficiency deteriorates
Solution Approach 1:
The patent introduces an intermediary layer between the OLTP database and extraction workloads: the RODS with its semantic layer. This intermediary presents simplified, business-friendly data models and pre-defined views to report writers, shielding them from the complexity of the underlying normalized transaction data model. Implementers can create flexible custom reports using intuitive schemas without needing to understand complex table relationships, while the RODS handles the complex data transformation efficiently in the background, resolving the contradiction between reporting flexibility and query efficiency.
Data Source
AI summary
Techniques are described herein for implementing, deploying, and utilizing an in-memory information access framework for enterprise data using highly-resolved, semantically-complete, read-only data objects. Extraction workloads efficiently access enterprise data without incurring significant performance degradation to transaction entry workloads. Consumers access enterprise information in a user-intelligible fashion without any knowledge of the underlying data storage and relationship details of complex physical schema designs. Consumers access subsets of the read-only data objects by defining reusable and parametrized data slices. Consumers combine related data slices into data slice collections. Initially, the system receives a request for a dataset by specifying a data slice. The system accesses metadata for an object or view associated with the data slice to determine operations for accessing the requested dataset. The system generates a database query for retrieving the requested dataset. The system executes the database query on a database to retrieve and return the requested dataset.