A Hybrid Row-column OLTP Database Architecture for Operational Reporting

1. A Hybrid Row-column OLTP Database Architecture for Operational Reporting Jan Schaffner, Anja Bog, Jens Krüger, Alexander Zeier

2. Agenda August 24, 2008 | A Hybrid Row-column OLTP Database Architecture • Operational Reporting • Related Work • Architecture of Hybrid System • Virtual Cube • Outlook and Discussion

3. Operational Reporting August 24, 2008 | A Hybrid Row-column OLTP Database Architecture • Dinstinction according to Inmon: • Informational Reporting • Supports long-term, strategic decisions • Summarized data • Long-term horizons  Typically done using a data warehouse (DW) • Operational Reporting • Supports day-to-day decisions • Data on a more detailed level • Takes up-to-the-minute data into account  Done using a DW or an OLTP system?

4. Operational Reporting (contd.) August 24, 2008 | A Hybrid Row-column OLTP Database Architecture • Using a DW for Operational Reporting • DW must be designed to the same level of granularity as the OLTP systems  huge data volumes • Updates are required to frequently be replicated into the DW  endless optimization • Using an OLTP Store for Operational Reporting • Operational reporting queries are relatively long-running in comparison to pure OLTP workloads • Resource contention:Locks of long-running queries block the short-running ones • Different data model:Not optimized for reporting (i.e. no star-schema)

5. Common Data Warehouse Architecture August 24, 2008 | A Hybrid Row-column OLTP Database Architecture • DW contains ETL processor which • ...extracts data from various OLTP sources into a staging area • ...applies transformations for cleansing and integration • ...stores data in a dimensional layout • OLAP engine runs queries against dimensional data store

6. “Real-Time” DW Architectures August 24, 2008 | A Hybrid Row-column OLTP Database Architecture • Microbatch • Configure ETL process to run in very short intervals • Up-to-date data but very resource intensive • Push Architectures • Handling of deltas on a business or database transaction level • Up-to-date data but still resource intensive • Operational Data Store (ODS) • Store copy of the OLTP data using an integrated schema • High data granularity but no up-to-date data

7. “Real-Time” DW Architectures (contd.) August 24, 2008 | A Hybrid Row-column OLTP Database Architecture • ELT • Data is extracted from the OLTP sources and loaded into the ODS • Transformations are done in the warehouse at query-runtime • High granularity (transactional data) but no up-to-date data • Virtual ODS • Virtual in the sense that queries are redirected against OLTP system • High granularity (transactional data) and up-to-date data • Performs ETL on-the-fly • Affects performance of OLTP system

8. Column-Stores: New “Trend” for OLAP • Column-store databases: • Vertical fragmentation • Fast aggregations (sum, min, max, avg, …) more flexibility for ad-hoc reporting • Each column can be compressed individually • Both disk-based … • Vertica • Greenplum • … and in-memory: • SAP BIA • MonetDB • Exasol August 24, 2008 | A Hybrid Row-column OLTP Database Architecture

9. Encoding Schemes 9 Few distinct values Many distinct values • Sequence of triples: • value • offset position • # occurrences Delta representation Ordered • Sequence of tuples: • value • bitmap for positional • occurence ? Unordered August 24, 2008 | A Hybrid Row-column OLTP Database Architecture

10. Architecture of Hybrid System • Essentially integration between row- and column store DBs • MaxDB is used as the row store • Database underlying SAP Business ByDesign • Supports ACID transactions • TREX is used as the column store • Main memory • Engine underlying SAP BIA • Has a copy of (some of) the OLTP data • Primary OLTP system and main-memory database (MMDB) aregoverned using a single resource manager August 24, 2008 | A Hybrid Row-column OLTP Database Architecture

11. Architecture of Hybrid System (contd.) August 24, 2008 | A Hybrid Row-column OLTP Database Architecture

12. Virtual Cube • Similar architecture as virtual ODS • Virtual Cube provides the same interfaceas a typical cube (slice, dice, drill-down, …) • Virtual Cube rewrites queries and issues them against the MMDB (TREX in our case) • TREX has a copy of the OLTP data • Primary OLTP system and MMDB aretied together as described above August 24, 2008 | A Hybrid Row-column OLTP Database Architecture

13. Outlook • Build a “real” hybrid database in-memory as part of ChunkyStore • Data can be stored as either: • Rows • Columns • Chunks (adjacent fragments of rows and columns) • DB decides which physical storage alternative is most suitable • Main-memory implementation will cater for fast updates as well as fast operational reporting capabilities August 24, 2008 | A Hybrid Row-column OLTP Database Architecture

14. Thank you August 24, 2008 | A Hybrid Row-column OLTP Database Architecture • Questions?