SAP HANA sidecar for reporting and finance flows

Set up a read-only SAP HANA sidecar next to L'Oréal USA's Oracle-based ECC system: selected and sized the replicated tables and rewrote reports and programs to push calculations into the database.

Business context

Most SAP systems at the time ran on Oracle. SAP was promoting HANA, its in-memory, columnar database, which is much faster on very large tables, and that made it attractive for L'Oréal USA's data volumes. L'Oréal chose not to replace Oracle. Instead it added HANA alongside it as a secondary, read-only copy fed by near-real-time replication: a sidecar. Master data stayed in Oracle, and nothing was updated or deleted in HANA. The goal was to speed up the costliest reports, transactions and calculations, especially finance flows and BI reporting programs, and to open up new BI possibilities.

Key contributions

Selected and sized the tables to replicate into HANA, where both licensing (priced on consumed space) and server sizing depended on a precise estimate.
Configured replication, deciding which tables needed real-time replication and which did not.
Rewrote custom ABAP programs to move sorting, grouping and aggregation into SQL run on HANA, redirecting their queries to the secondary database connection.
Applied SAP's HANA accelerators for standard programs after checking them against custom enhancements with version-comparison tools.

Responsibilities in detail

Analysis & design

Took part in workshops with SAP consultants on the HANA architecture and explained how L'Oréal's data was organized.
Researched the SAP data model to build the list of tables to replicate.
Sized the data for the migration, including growth over time.
Technical design with HANA.

Configuration

Configured the replication system (real-time vs. non-real-time tables).
Configured SAP so that specified programs and tables used HANA automatically.

Development

ABAP programming optimized for HANA.
HANA Studio modeling: analytic and calculation views.
Used SQL trace and performance-analysis tools to find slow queries and costly ABAP algorithms.

Handover

Knowledge transfer.

The challenge

The two databases work very differently. Oracle stores data by row, on disk, with buffers in RAM, primary keys and indexes. HANA keeps everything in memory, stores data by column (row store is possible but not optimal) and makes most indexes unnecessary. It is strongest on very large tables, on wide tables with many unused fields, and on aggregations and sums: the bigger the table, the bigger its lead over Oracle.

L'Oréal's existing programs had been written for a row database, so moving them to HANA did not help them equally. Some gained little and others gained a lot. Two constraints shaped the design:

Cost. HANA was licensed on the space consumed, and the physical server had to be sized for CPU and RAM. The machine was very expensive, so my capacity estimate had to be precise.
No cross-database joins. A SQL join could not span Oracle and HANA, so any table often joined to a replicated table had to be replicated too.

Solution design

Architecture: HANA ran in tandem with Oracle as a read-only secondary database with near-real-time replication. Oracle stayed the primary database and the system of record.
Table selection: I chose the largest, most read and most reported tables, plus the small tables often joined to them, and estimated their size including growth over time.
Replication: I configured which tables replicated in real time and which did not need to.

Technical implementation

From "data to code" to "code to data". The classic Oracle-era ABAP pattern is simple SELECTs, then calculating and sorting in ABAP on the application servers. With HANA I moved calculations, sorting, grouping and aggregation into SQL executed in the database.

Finding candidates. I used SQL trace and performance-analysis tools to find the slow queries and the expensive ABAP algorithms most likely to benefit.

Standard SAP programs. SAP delivered HANA accelerators as patches and notes. Before applying them, I used version-comparison tools to check for custom enhancements that the changes might affect. The accelerators then applied automatically, with no code changes.

Custom programs. I analyzed and rewrote them. Oracle remained the default connection, so I used the statements that send a query to the secondary HANA connection instead, and I pushed more of the calculation into the database. I also built HANA Studio models (analytic and calculation views).

Redirect only. Sometimes redirecting a program to HANA was enough, because even basic SQL runs faster in memory. For those cases I used SAP technical configuration that tells the system to use HANA automatically for specified programs and tables.

Results

The client noticed the improvement immediately, because the user experience got better straight away. Expectations were high, since HANA was expensive in licenses, space and hardware. In the dossier I described some optimizations as having "spectacular results, like processing times divided by a thousand or more". I also wrote that BI teams could get reports in real time, where the data used to be extracted over a day, giving the business real-time views of stock, sales results and goods transfers. These are my own statements in the dossier, not independently measured figures.

What I learned

I had prepared through SAP's HA400 course (ABAP programming on HANA). The main lesson was the paradigm shift: on HANA, performance comes from pushing logic down to the data instead of pulling data up to the application server. Shortly after this project, SAP announced S/4HANA, with HANA as the mandatory primary database, so these skills carried over to every later SAP project.