OLTP Reporting via Column-Oriented Virtual Cube
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current Business Intelligence (BI) systems rely on centralized data warehouses or decentralized data marts, leading to outdated data due to periodic ETL processes, which restrict real-time reporting capabilities, especially in industries requiring immediate responses, and limit ad-hoc reporting flexibility due to predefined aggregation levels.
Innovation Solution
A computer system that combines a relational database management system storing data in row format with a column-oriented data processing component, allowing real-time updates and queries, eliminating the need for data replication and enabling on-the-fly transformations for reporting, using main memory technology and a virtual cube architecture.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If periodic ETL processes are used to transport data into dedicated storage, then data integration and reporting capabilities are achieved, but data is not always up-to-date and real-time reporting is restricted
Solution Approach 1:
The patent merges OLTP and OLAP workloads into a single database system, eliminating the need for separate data warehouses and periodic ETL processes. The column-oriented storage engine enables both transactional and analytical queries to execute directly on the same data, ensuring real-time reporting capability while maintaining data freshness.
Solution Approach 2:
The patent creates a virtual copy of the database schema and data in a column-oriented format within the same system, allowing analytical queries to access data without physical replication to separate storage systems. This virtual copying enables real-time analytical processing while maintaining data consistency with the transactional database.
2Speed
If data is aggregated to achieve higher data access performance, then query performance is improved, but ad-hoc reporting flexibility is limited due to predefined aggregation levels
Solution Approach 1:
The patent implements dynamic aggregation capabilities where the database can automatically aggregate data at query time based on the specific query requirements. The column-oriented storage engine enables flexible aggregation at any level without pre-defining aggregation structures, allowing both high-performance querying and adaptable ad-hoc reporting.
Solution Approach 2:
The patent performs data aggregation in advance at multiple levels during data loading, storing pre-aggregated data structures that can be quickly accessed. However, these pre-computed aggregates serve as optimizations rather than constraints, allowing the system to dynamically select appropriate aggregation levels based on query requirements.
3Reliability
If centralized data warehouses or decentralized data marts are used, then integrated data storage is achieved, but resource consumption is high and system complexity increases
Solution Approach 1:
The patent combines data storage and processing functions into a single integrated system. The column-oriented storage engine serves both OLTP and OLAP workloads, eliminating the need for separate data warehouses, data marts, and ETL infrastructure, thereby reducing system complexity while maintaining data integration.
Solution Approach 2:
The patent creates a universal database system that can handle both transactional and analytical workloads. The column-oriented storage engine provides multi-functional capabilities, allowing the same data storage to serve diverse query types and analytical needs without requiring specialized infrastructure.
4Ease of manufacture
If ETL processes are run as batch jobs during low system load windows, then data transformation and cleansing are performed, but data access time and reporting responsiveness are delayed
Solution Approach 1:
The patent enables continuous data processing where analytical queries can execute directly on transactional data in real-time. The column-oriented storage engine continuously maintains data in an analytics-ready format, eliminating the need for periodic batch ETL processes and ensuring continuous data availability for reporting.
Solution Approach 2:
The patent replaces the mechanical batch ETL process with a software-based virtualization approach. Instead of physically moving and transforming data through ETL pipelines, the system uses virtual column-oriented data structures that provide on-demand data transformation and analytics capabilities without physical data movement.
Data Source
Figure 1
Figure 2
Figure 3~4
AI summary
While OLTP systems store up-to-date data, efficient reporting on top of these systems is not practicable due to performance reasons. OLAP systems provide sophisticated reporting capabilities, but do usually not use up-to-date data: common reporting architectures rely on complex, resource-intensive ETL processes that replicate OLTP data into read-optimized data structures in a batch job fashion during low system load times. The present invention introduces an architecture for reporting directly on top of OLTP data that preserves the short response times of OLAP systems. To do so, data transformations typically carried out during ETL are performed at query-runtime in a column-oriented main memory database. The advantages over traditional reporting architectures are that up-to-date data can be provided and that additional OLAP data stores are no longer required.