ETL-Less OLTP Reporting Through Synchronized Column Storage

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Current BI systems rely on resource-intensive ETL processes that replicate OLTP data into read-optimized structures in batch jobs, leading to outdated data and limitations in real-time reporting, especially in scenarios requiring ad-hoc reports and detailed information not predefined in aggregation levels.

Innovation Solution

A computer system and method that integrates a relational database management system with a column-oriented data processing component, allowing real-time data updates and query responses directly from OLTP systems, eliminating the need for ETL procedures and providing up-to-date data for reporting.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If ETL processes are used to replicate OLTP data into read-optimized structures, then data accessibility and reporting performance are improved, but data timeliness deteriorates and system complexity increases

Engineering Contradiction:

Improvereporting performanceVSAvoiddata timeliness

Core Design Contradiction:

ProductivityVSLoss of time

Solution Approach 1:

The patent merges OLTP and OLAP systems into a unified architecture where a single database system handles both transactional processing and analytical reporting. The column-oriented data processing component enables the system to serve both OLTP and OLAP workloads simultaneously, eliminating the need for separate ETL processes and data warehouses, thus resolving the contradiction between reporting performance and data timeliness

Inventive Principle:

Principle #5Merging (Combining)

Solution Approach 2:

The system dynamically switches between row-oriented and column-oriented data access modes based on the workload type. For OLTP operations, row-oriented access is used for efficient transaction processing, while for OLAP reporting, column-oriented access is activated to provide optimized read performance. This dynamic adaptation allows the system to maintain both speed and timeliness without requiring separate static systems

Inventive Principle:

Principle #15Dynamics

2Adaptability or versatility

If ETL processes are used to replicate OLTP data, then reporting capabilities are enhanced, but device complexity and programming effort increase

Engineering Contradiction:

Improvereporting capabilitiesVSAvoidsystem complexity

Core Design Contradiction:

Adaptability or versatilityVSDevice complexity

Solution Approach 1:

The database system is designed to be universal, handling both OLTP and OLAP workloads within a single system. The column-oriented data processing component provides multi-functionality by enabling analytical reporting directly on OLTP data without requiring separate ETL infrastructure, data warehouses, or complex integration layers, thus reducing overall system complexity while maintaining versatile reporting capabilities

Inventive Principle:

Principle #6Universality (Multi-functionality)

Solution Approach 2:

The patent extracts the analytical reporting functionality from the traditional ETL pipeline and implements it directly within the OLTP database system. By taking out the need for separate data warehouse infrastructure, ETL processes, and intermediate buffering layers, the system achieves simplified architecture while preserving full reporting adaptability

Inventive Principle:

Principle #2Taking out (Extraction)

3Speed

If data is aggregated at predefined levels, then data access performance is improved, but adaptability for ad-hoc reports deteriorates

Engineering Contradiction:

Improvedata access performanceVSAvoidad-hoc report capability

Core Design Contradiction:

SpeedVSAdaptability or versatility

Solution Approach 1:

The system dynamically computes aggregations on-demand based on query requirements rather than relying on static pre-defined aggregation levels. When a report is requested, the column-oriented data processing component dynamically determines the necessary aggregation level and performs calculations in real-time, enabling both fast access to aggregated data and flexibility for ad-hoc reporting scenarios

Inventive Principle:

Principle #15Dynamics

Data Source

PatentUS12361005B2ETL-less zero-redundancy system and method for reporting OLTP data

Publication Date: 2025.07.15 SAP SE

AI summary

A system includes a relational database management system component and a column-oriented data processing component. The relational database system component stores database information in a row format. The column-oriented data processing component stores the database information in a column format. In response to a database update request, the relational database management system component updates the database information stored in the row format; the relational database management system component notifies the column-oriented data processing component of the database update request; and the column-oriented data processing component updates the database information stored in the column format. In response to a query request, the column-oriented data processing component generates a query response based on the database information stored in the column format. In this manner, the system is able to generate up-to-date reports without the need for extraction, translation and loading procedures.