Multi-Model Data in SAP HANA: JSON, Spatial and Graph Explained

Most databases pick a lane: relational, document, graph, geospatial. Your data, unfortunately, doesn't. A logistics app needs delivery addresses (relational), route geometries (spatial), driver check-ins as JSON blobs (document), and a network of warehouses and routes (graph) — often in the same application.

The traditional answer is polyglot persistence: four databases, four drivers, four operational headaches, and application code stitching it all together. HANA's answer is different: one database, multiple engines, and SQL that reaches all of them.

---

One database, several engines

HANA is a multi-model database. Alongside the familiar relational (columnar and row) engines, it ships with:

Document store — schemaless JSON collections. You store JSON documents as-is, query into their structure with SQL, and index the fields you filter on. No shredding documents into relational tables, no object-relational mapping gymnastics.

Spatial engine — native geometry and geography types with spatial predicates: distance, intersection, containment. "Find all warehouses within 50 km of this point" is a single query, not a post-processing loop in application code.

Graph engine — nodes and edges with built-in traversal: shortest path, neighborhood expansion, pattern matching. Supply-chain networks, bill-of-material explosions, fraud rings — anything where the connections matter as much as the entities.

The point isn't that each engine exists — it's that they coexist. A single query can join a relational customer table to a JSON order document to a spatial warehouse lookup. Try doing that across four separate databases without crying.

Key takeaway: multi-model means one database serving relational, document, spatial, and graph workloads together — so cross-model queries stay inside the database instead of being stitched together in application code.

---

When to use each model

Document store fits semi-structured data that resists a fixed schema: IoT telemetry with varying fields per device type, user preferences, audit payloads, API request logs. If your relational design is sprouting "custom_field_1 … custom_field_47" columns, that's a document-shaped problem.

-- Querying into a JSON document collection
SELECT doc.checkin_id,
       doc.driver ->> '$.name' AS driver_name
FROM DRIVER_CHECKINS AS doc
WHERE doc.payload ->> '$.status' = 'DELAYED';

Spatial fits anything with a location question: delivery zones, asset tracking, store catchment analysis, route planning. HANA understands both planar geometry and real earth geography, so distance calculations come out in actual meters, not degrees of wishful thinking.

Graph fits connected data where traversal is the workload: BOM explosions ("all components under this assembly, recursively"), organizational hierarchies, network impact analysis, recommendation paths. Recursive SQL can do some of this, but the graph engine's traversal algorithms are built for it and dramatically faster at depth.

---

The practical payoff

Consider a delivery-tracking scenario. Orders live in relational tables. Driver devices send JSON check-ins with GPS coordinates (document + spatial). Warehouses, hubs, and routes form a network (graph). One operational question — "which delayed shipments are heading to warehouses affected by the storm zone?" — touches all four models.

In a multi-model HANA setup, that's one query with joins across models. In a polyglot setup, it's four database round-trips, application-side joins, and consistency headaches. The operational savings compound too: one backup strategy, one HA setup, one security model, one team that knows the system.

That's the real argument for multi-model. Not a feature checklist — fewer moving parts.

---

Modeling tips for the document store

Flexible schema doesn't mean no schema thinking. A few habits keep JSON collections fast: index the fields you filter on (HANA supports indexes into document structure), keep documents reasonably sized (a 5 MB document per row will hurt), and decide up front which fields are queryable versus opaque payload. The most common mistake is treating the document store as a dumping ground — schemaless storage still rewards deliberate design.

Versioning deserves thought too. When the producing system adds fields, old documents won't have them — write queries defensively (WHERE doc.payload ->> '$.newField' returns null for old docs, which is usually what you want, but verify).

---

Graph in practice: a traversal example

Graphs in HANA are typically modeled as vertex and edge tables. A supply-chain example: warehouses and plants as vertices, transport lanes as edges with a cost attribute. The classic question — cheapest path from plant to customer region — is a shortest-path traversal:

-- Shortest path over a weighted graph workspace
SELECT * FROM GRAPH_TABLE ( SUPPLY_NETWORK
    MATCH SHORTEST_PATH ( :start_vertex TO :end_vertex )
    COLUMNS ( vertex_id, edge_cost )
);

Writing this as recursive SQL is possible and painful; the graph engine's traversal operators handle cycles, depth limits, and weighted costs natively. If your "hierarchy" queries are getting deeper than three levels of recursion, that's the signal to model it as a graph.

---

Spatial indexing and real queries

Spatial queries live or die by the spatial index. Without one, "warehouses within 50 km" becomes a full scan computing distances for every row. HANA builds spatial indexes automatically for geometry columns in most cases, but verify on large tables — and remember that mixing coordinate systems (WGS84 vs. planar) in one query is a classic source of silently wrong results. Keep a consistent SRID across your spatial columns and say so in your data dictionary.

Key takeaway: reach for document, spatial, or graph models when the data's shape demands it — and keep it all in HANA so cross-model questions stay as single queries, not multi-database plumbing.

Read more →

SQLScript and Calculation Views in SAP HANA: A Practical Guide

Sooner or later, every HANA developer hits a wall with plain SQL. You need loops, conditionals, cursors, exception handling — real procedural logic — but you need it running inside the database, close to the data, not in a loop in ABAP or Node.js dragging rows back and forth. That's what SQLScript is for.

This post covers SQLScript, how ABAP calls it through AMDP, and where calculation views fit — including what's deprecated and what replaced it.

---

SQLScript: procedural logic inside HANA

SQLScript is HANA's extension of SQL with procedural constructs: variables, IF/CASE branches, loops, cursors, exception handlers, and table variables. The key idea is set-based processing — you operate on whole tables at once instead of row-by-row, and the database engine parallelizes it across cores.

A simple example — a procedure that computes total revenue per region:

CREATE PROCEDURE GET_REVENUE_BY_REGION (
    OUT result TABLE (REGION NVARCHAR(50), REVENUE DECIMAL(15,2))
)
LANGUAGE SQLSCRIPT AS
BEGIN
    result = SELECT region,
                    SUM(amount) AS revenue
             FROM SALES_ORDERS
             GROUP BY region;
END;

Nothing fancy, but notice the pattern: SQLScript procedures take table-typed parameters and return tables. They compose — one procedure's output feeds the next. The golden rule: keep it declarative and set-based. The moment you write a cursor loop over a million rows, you've usually lost the plot; there's almost always a set-based rewrite.

Key takeaway: SQLScript brings procedural logic into the database so compute-heavy work runs where the data lives. Write it set-based — table in, table out — and let HANA parallelize it.

---

AMDP: calling SQLScript from ABAP

ABAP developers don't write SQLScript directly. They use AMDP — ABAP Managed Database Procedures. An AMDP is an ABAP class method whose body is SQLScript, managed by the ABAP development tools (transport, syntax check, activation) but executed on the HANA database.

CLASS zcl_revenue_calc DEFINITION PUBLIC.
  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.
    METHODS get_revenue
      IMPORTING VALUE(iv_year)   TYPE gjahr
      EXPORTING VALUE(et_result) TYPE ztt_revenue.
ENDCLASS.

CLASS zcl_revenue_calc IMPLEMENTATION.
  METHOD get_revenue BY DATABASE PROCEDURE
         FOR HDB LANGUAGE SQLSCRIPT.
    et_result = SELECT region, SUM(amount) AS revenue
                FROM sales_orders
                WHERE year = :iv_year
                GROUP BY region;
  ENDMETHOD.
ENDCLASS.

The classic use case: logic too complex for a CDS view but too data-intensive to pull into the ABAP layer. Aggregations over huge tables, multi-step transformations, anything where moving the data would cost more than moving the logic. If you find yourself SELECTing a million rows into an internal table just to loop and aggregate — that's an AMDP-shaped problem.

---

Calculation views: the modeling layer

Calculation views are HANA's graphical (and SQL-based) modeling artifacts — the successors of the old analytic/attribute views. You build them in the database explorer by combining tables with joins, unions, aggregations, and filters, or you write them in SQL. They expose a clean, consumable result set that reporting tools, CDS table functions, and applications query like a table.

A typical pattern: a calculation view joins sales orders to customers and products, computes derived measures, and exposes it all as one virtual table. Downstream consumers never see the complexity.

Under the hood, calculation views execute on HANA's calculation engine — the optimizer path specifically built for these composed, columnar operations. When someone asks "which engine processes calculation views," that's the answer: not the row engine, not the join engine — the calculation engine.

---

What's deprecated (and what replaced it)

This trips people up in interviews and in real projects: scripted calculation views were deprecated back in HANA 1.0 SPS 11. The old approach — embedding SQLScript directly inside a calculation view — is dead.

The replacement is the CDS table function: you write the SQLScript in an AMDP method, then expose it as a CDS entity via a table function definition. Consumers query it like any other CDS view, but the heavy logic runs as SQLScript on the database.

-- CDS table function definition
@EndUserText.label: 'Revenue by region'
DEFINE TABLE FUNCTION ZTF_REVENUE_BY_REGION
WITH PARAMETERS @Environment.systemField: #CLIENT
                clnt : abap.clnt,
                year : abap.numc(4)
RETURNS {
  client : abap.clnt;
  region : abap.char(50);
  revenue : abap.curr(15,2);
}
IMPLEMENTED BY METHOD zcl_revenue_calc=>get_revenue;

So the modern stack is: SQLScript in AMDP for the logic, table function to expose it, calculation views (graphical or SQL-based) for pure modeling. Scripted calc views belong in migration plans, not new designs.

Key takeaway: put procedural logic in SQLScript (via AMDP from ABAP), model with graphical or SQL calculation views, and expose scripted logic through CDS table functions. Scripted calculation views are deprecated — don't build new ones.

Read more →

SAP HANA Architecture: Column Store, Dictionary Compression and Delta Merge Explained

Ask any SAP developer what makes HANA fast and you'll hear "in-memory" within seconds. That's true, but it's only half the story. Plenty of databases can hold data in RAM. What sets HANA apart is how it organizes that data — and the column store is the heart of it.

This post walks through the three ideas that do most of the heavy lifting in HANA's architecture: the column store, dictionary compression, and the delta merge. Understand these and a lot of HANA behavior — good and bad — starts making sense.

---

Why column store wins for analytics

Traditional databases store tables row by row. A sales order row — order number, customer, date, ten line items, amounts, statuses — sits together in one contiguous block. That's perfect when your workload reads or writes whole rows, like posting a document or displaying a single order.

Analytical workloads don't work like that. A typical report asks: "give me total revenue by region for the last quarter." It needs one or two columns out of fifty, across millions of rows. In a row store, the engine drags every full row off disk (or memory) just to read two fields. Most of the data it touches gets thrown away.

A column store flips the layout: each column is stored contiguously. Reading "revenue" and "region" means scanning exactly those two columns — nothing else. For wide tables with selective queries, that's an enormous win. It's the single biggest reason HANA chews through aggregations that would choke a row-based system.

There's a second, subtler advantage. Values inside one column look alike. A "country" column contains a few dozen distinct values repeated millions of times; a "status" column maybe five. Similar values compress far better than the mixed bag inside a row. Which brings us to...

---

Dictionary compression: small IDs instead of long strings

Here's the trick. Instead of storing the string "Germany" a million times, HANA builds a dictionary: a sorted list of every distinct value in the column. "Germany" becomes value ID 7, stored as a tiny integer. The column itself is just a dense array of small integers.

Two things fall out of this. First, memory usage collapses — a column of repeated strings shrinks to a fraction of its original size. Second, and less obvious, many operations get faster. Comparing integers is cheaper than comparing strings, so filters, joins, and group-bys on compressed columns run on the small IDs and only translate back to real values at the very end.

The dictionary is sorted, which enables another optimization: range queries and binary search on the dictionary itself. And because the IDs are assigned in sort order, min/max aggregations can sometimes be answered from the dictionary alone without touching the column data at all.

Key takeaway: dictionary compression isn't just about saving memory. Storing small integer value IDs instead of raw values makes scans, filters, and aggregations genuinely faster.

---

The write problem — and the delta store

Column stores have one famous weakness: writes. Appending a single row means touching every column's data structure — fifty separate writes for one logical insert. Do that for every incoming sales order and performance falls off a cliff.

HANA's answer is the delta store: a small, row-oriented staging area sitting in front of each column table. All inserts, updates, and deletes land in the delta first, where row-wise writes are cheap. Reads transparently combine both stores, so queries always see fresh data.

Think of it like a desk in-tray. New paperwork piles up in the tray (fast to drop in, messy to search), and periodically someone files it all into the cabinet (the columnar main store) in one organized batch. That batch filing is the delta merge.

---

Delta merge: filing the in-tray

During a delta merge, HANA takes everything accumulated in the delta store, sorts and compresses it, and folds it into the main column store — rebuilding dictionaries as needed. It's a heavier operation, so HANA doesn't do it on every write. Instead it triggers merges based on heuristics: delta size, time since last merge, memory pressure.

This is worth knowing because a bloated delta store is one of the classic HANA performance culprits. If writes vastly outpace merges, queries slow down — they're scanning an ever-larger uncompressed row store alongside the main store. Symptoms: gradually degrading read performance on a table with heavy insert activity.

You can check delta sizes in the HANA cockpit and, if needed, trigger a manual merge:

-- Force a delta merge on a specific table
MERGE DELTA OF "MYSCHEMA"."SALES_ORDERS";

In practice, HANA's automatic merge usually keeps up. Manual merges are for the exceptions — bulk loads, data migrations, or troubleshooting a slow table.

---

Putting it together

The full picture is elegant: writes land in the row-based delta (fast inserts), reads hit the compressed columnar main store (fast analytics), and the delta merge continuously converts one into the other. Dictionary compression keeps the main store small and scans quick.

Next time someone says "HANA is fast because it's in-memory," you'll know better. Memory helps. But the column store, the dictionaries, and the delta merge are doing the real work.

Key takeaway: HANA's speed comes from architecture, not just RAM — columnar layout for selective scans, dictionary compression for size and speed, and a delta store plus merge to keep writes cheap without sacrificing read performance.

Read more →