SAP HANA Advanced Data Modeling and Performance Tuning: Complete Technical Guide

Sarah Chen — Lead SAP Architect, SAPExpert.AI Weekly Deep Research Series
Target platforms: SAP HANA 2.0 (SPS05+), SAP HANA Cloud (latest), SAP S/4HANA Embedded Analytics

Executive Summary (≈150 words)

SAP HANA performance is rarely “fixed” by hardware alone; it is typically won (or lost) in the semantic layer and how the optimizer can prune, reorder, and execute work in the column engine / calculation engine. In 2026-era landscapes, best outcomes come from: (1) purpose-built semantic models (CDS VDM for S/4, Calculation Views for native/side-by-side), (2) pushdown without procedural anti-patterns, and (3) a repeatable tuning workflow anchored in Expensive Statements + PlanViz rather than intuition.

Key recommendations:

• Design Calculation Views “for pruning first”: sargable predicates, correct join types, cardinalities only when true, and avoid function-wrapped filter columns.
• Treat plan stability as an operational concern: keep statistics accurate, detect regressions after data loads, and baseline critical statements.
• Align physical design with workload: partition for predicate pruning and parallelism; manage delta merges for write-heavy tables; be deliberate about federation (SDA/SDI) pushdown boundaries.

Official references used throughout include SAP HANA SQL & SQLScript references and modeling/admin performance guides on SAP Help Portal.

Technical Foundation (≈450 words)

1) How HANA really executes your model (beyond the basics)

SAP HANA’s speed comes from executing set-based, columnar, and prune-able operations with minimal materialization. In practice:

• Column Store is the default for analytical and mixed workloads: compression reduces memory bandwidth, and vectorized execution accelerates scans/aggregations. Use Row Store only for small, high-update, point-lookup tables.
• MVCC prevents read/write blocking, but does not eliminate contention: hotspots can still appear in locks, log I/O, or memory allocation under concurrency.
• Execution domains matter:
  • The SQL Optimizer decides plans, rewrites, join order, and access paths.
  • The Calculation Engine (CE) executes parts of Calculation Views and can unlock pruning and join reordering—if the model stays “optimizable”.
• Persistence (data + log volumes) still determines recovery and write throughput. Log pressure is a common hidden limiter in write-heavy systems.

SAP reference: SAP HANA Cloud SQL Reference Guide, SAP HANA SQLScript Reference

2) Modern semantic stack choices (and why performance differs)

S/4HANA Embedded Analytics typically uses ABAP CDS as the canonical semantic contract (VDM layers). CDS performance is largely governed by generated SQL and association expansion patterns.
SAP reference: ABAP CDS Views (ABAP Platform) – Concepts and Usage

Native HANA / side-by-side uses HDI containers with Calculation Views as the main modeling artifact, optionally with SQLScript procedures/functions for encapsulated transformations.
SAP reference: SAP HANA Deployment Infrastructure (HDI)

3) The performance tuning truth hierarchy

In real programs, performance improvements usually come in this order:

1. Model/SQL fixes (pruning, join reduction, narrower projections, selectivity)
2. Logical redesign (grain alignment, separate consumption views, pre-aggregation for concurrency)
3. Physical design (partitioning, data types, merge strategy, selective indexes)
4. System tuning (memory, threads, I/O sizing, scale-out distribution)

SAP reference: Troubleshooting and Performance Analysis Guide for SAP HANA

Implementation Deep Dive (≈900 words)

1) A repeatable tuning workflow (the “architect’s loop”)

Step A — Identify the real hotspot (not the noisy one)

Use Expensive Statements and aggregate by total time and frequency. Then confirm with the actual execution plan.

• In HANA Cockpit / Database Explorer, use Performance Monitor and SQL Analyzer.
• For plan inspection, use PlanViz (visual plan) to find:
  • non-pruned table scans
  • join explosions (intermediate row count spikes)
  • expensive aggregates/sorts
  • remote execution vs local pull (SDA)

SAP reference: Plan Visualizer (PlanViz) in SAP HANA Database Explorer

Step B — Fix pruning blockers before anything else

Common pruning blockers:

• predicates on expressions: WHERE YEAR(posting_date) = 2026
• implicit casts from NVARCHAR to INTEGER
• filters applied after outer joins
• “mega views” with unused columns that prevent projection pruning

Rewrite to sargable predicates:

-- Anti-pattern (blocks efficient pruning)
SELECT ...
FROM FACT_SALES
WHERE YEAR(POSTING_DATE) = 2026;

-- Preferred
SELECT ...
FROM FACT_SALES
WHERE POSTING_DATE >= DATE'2026-01-01'
  AND POSTING_DATE <  DATE'2027-01-01';

SAP reference: SAP HANA Cloud SQL Reference – Predicates and Expressions

2) Advanced Calculation View modeling that stays “optimizable”

Pattern 1 — “Narrow-by-default” consumption views

Create separate consumption views for:

• high-concurrency dashboards (narrow, pre-aggregated, stable)
• exploratory analysis (wider, flexible)

Why it’s advanced: many teams model one “universal” cube, then spend years chasing regressions caused by new joins, columns, and semantics. Purpose-built views reduce plan variance and keep pruning intact.

Pattern 2 — Join discipline with intentional outer joins

Outer joins are not “safe by default”; they frequently inflate intermediates and delay filter pushdown.

Rule of thumb

• Fact → Dimension: prefer inner join when referential integrity holds.
• Use left outer join only when missing dimension members are expected and required for the business result.

Cardinality settings

• Setting cardinality can help CE optimization only if true. Incorrect cardinality can cause catastrophic plan choices.
• Treat cardinality as a contract, not a guess.

SAP reference: Calculation View Modeling – Join Types and Semantics

Pattern 3 — UNION ALL for scale, UNION for correctness only

-- Prefer UNION ALL if deduplication is not required
SELECT ... FROM STAGE_A
UNION ALL
SELECT ... FROM STAGE_B;

UNION forces deduplication (sort/hash), which can dominate runtime at scale.

Pattern 4 — Calculated columns: push to base or precompute

Per-row expensive expressions (regex, complex CASE trees, repeated conversions) on large scans often become CPU hotspots. Options:

• precompute in ingestion/ELT layer
• persist in a curated table
• isolate in a “detail view” not used by dashboards

3) ABAP CDS: controlling generated SQL (S/4HANA reality)

Problem: Association expansion causing join storms

A consumption view that exposes many fields from many associations can lead to a massive join graph at runtime (especially when the consumer selects “everything”).

Design controls

• Split into: interface/basic views → cube → consumption views per persona
• Make selective filters mandatory (date, company code, plant)
• Avoid long association chains in high-volume analytics

SAP reference: ABAP CDS – Associations

Example: enforce selectivity via parameterization

@EndUserText.label: 'Sales KPI (Date Mandatory)'
define view entity ZC_SalesKPI
  with parameters
    p_from : abap.dats,
    p_to   : abap.dats
as select from I_SalesDocumentItem as s
{
  key s.SalesDocument,
      s.Material,
      s.NetAmount
}
where s.PostingDate between :p_from and :p_to;

Advanced note: parameters can improve plan stability by keeping runtime predicates explicit and selective, especially for interactive queries.

4) SQLScript / AMDP: set-based or don’t do it

A high-performance SQLScript pattern: staged, set-based transforms

CREATE OR REPLACE PROCEDURE APP.SP_BUILD_SALES_MART (IN p_from DATE, IN p_to DATE)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN

  -- Stage 1: filter early (minimize volume)
  lt_fact =
    SELECT SalesDoc, Item, Plant, PostingDate, NetAmount, Currency
    FROM APP.FACT_SALES
    WHERE PostingDate >= :p_from AND PostingDate < :p_to;

  -- Stage 2: enrich with dimensions (inner join when valid)
  lt_enriched =
    SELECT f.Plant, f.PostingDate, d.Region, SUM(f.NetAmount) AS NetAmount
    FROM :lt_fact f
    JOIN APP.DIM_PLANT d
      ON d.Plant = f.Plant
    GROUP BY f.Plant, f.PostingDate, d.Region;

  -- Persist result (optional mart table)
  UPSERT APP.MART_SALES_DAILY
    SELECT * FROM :lt_enriched;

END;

Performance-critical details

• Filter first to shrink datasets before joins.
• Avoid row-by-row loops; loops are almost always slower and block optimizer strategies.
• Persist only when it supports a clear SLA/concurrency need (avoid unnecessary materialization).

SAP reference: SQLScript Reference – Procedures

5) Physical design: partitioning + data types + merge discipline

Partitioning for pruning and parallelism

Partition on columns that are:

• frequently used in filters (date/period, client/tenant, org unit)
• reasonably distributed

CREATE COLUMN TABLE APP.FACT_SALES (
  SALES_ID     BIGINT,
  POSTING_DATE DATE,
  PLANT        NVARCHAR(4),
  NET_AMOUNT   DECIMAL(15,2),
  CURRENCY     NVARCHAR(5)
)
PARTITION BY RANGE (POSTING_DATE) (
  PARTITION '2026Q1' VALUES LESS THAN (DATE'2026-04-01'),
  PARTITION '2026Q2' VALUES LESS THAN (DATE'2026-07-01'),
  PARTITION '2026Q3' VALUES LESS THAN (DATE'2026-10-01'),
  PARTITION '2026Q4' VALUES LESS THAN (DATE'2027-01-01')
);

Advanced insight: Partitioning helps only when your predicates match the partitioning expression. If users filter on fiscal period but you partition on calendar date without mapping, pruning may not trigger.

SAP reference: SAP HANA SQL Reference – CREATE TABLE / Partitioning

Data types: the silent performance multiplier

• Replace “default NVARCHAR(500)” with tight domain types.
• Prefer integers/codes over free-text for join keys.
• Keep currency/unit columns consistent to avoid runtime casts.

Delta merge management (write-heavy realities)

Column-store writes accumulate in delta store and merge into main. Too much delta hurts reads; too aggressive merges hurt writes.

Monitor delta behavior and merges; tune only with evidence.
SAP reference: SAP HANA Administration Guide – Column Store Delta Merge

Advanced Scenarios (≈550 words)

1) Plan stability under shifting data distributions (the “quiet killer”)

A query can be stable for months, then regress after:

• a large historical backfill
• a new plant/company code dominating volumes
• a data aging/tiering change
• new statistics after load

Operational pattern: performance baselining

• Identify “tier-1” statements (top dashboards, critical APIs).
• Capture baseline plans and runtime distributions (p50/p95).
• After major loads, compare plan shape (join order, intermediate cardinalities).

Key lever: ensure statistics are current (especially after bulk loads).
SAP reference: SAP HANA SQL Reference – UPDATE STATISTICS

2) Federation (SDA): making performance predictable

Federation is powerful but can become unpredictable when HANA pulls data locally due to:

• unsupported functions on remote adapters
• non-sargable predicates
• large projections
• implicit conversions

Advanced practice: “pushdown-friendly contract views”

• Create a remote source view that exposes only:
  • required columns
  • pushdown-safe predicates
  • remote-native functions (avoid HANA-only functions)

Then consume it from HANA Calculation Views with strict filters.

SAP reference: Smart Data Access (SDA)

3) High concurrency dashboards: designing for 200+ parallel users

Dashboards are not “just queries”; they are burst workloads with tight SLA.

Winning architecture

• Pre-aggregate to the dashboard grain (daily/weekly/store/product)
• Keep consumption views narrow (10–30 columns, not 200)
• Make time and org filters mandatory
• Avoid runtime currency conversions if possible; standardize conversions upstream

Advanced trick (often overlooked): separate “API views” from “analyst views”

• API views: stable semantics, strict filters, predictable cost
• Analyst views: flexible, but not used in concurrency hotspots

Real-World Case Studies (≈350 words)

Case 1 — Manufacturing OEE mart: join explosion eliminated (HANA 2.0 SPS06)

Symptoms: OEE dashboard intermittently hit 30–90s runtime. PlanViz showed intermediate row counts exploding after multiple left outer joins to small dimensions.

Fix:

• Converted two outer joins to inner joins after validating referential integrity.
• Split a universal calc view into:
  • CV_OEE_DASHBOARD_DAILY (pre-aggregated by line/day/shift)
  • CV_OEE_DETAIL (event-level exploration)
• Partitioned event fact table by EVENT_DATE (monthly), aligning with dashboard predicates.

Result: p95 runtime dropped from ~40s to <2s under 150 concurrent sessions; CPU flattened because intermediates stayed small and pruning became reliable.

Case 2 — Retail promotion analytics: CDS association blow-up contained (S/4HANA 2023)

Symptoms: Fiori analytics app generated massive SQL with many associations expanded; DB time dominated.

Fix:

• Created persona-based consumption views (merchandising vs finance).
• Enforced mandatory parameters for date range and sales org.
• Reduced field list; avoided wide “SELECT *” style consumption.

Result: runtime stabilized; plan variance reduced after data loads.

Case 3 — Utilities usage data ingestion: delta/log pressure (HANA Cloud)

Symptoms: commit times spiked; log throughput saturated during ingestion bursts; read queries also degraded due to large delta store.

Fix:

• Batched writes and reduced commit frequency.
• Isolated hot-write tables from heavy join paths (curated mart table used for reads).
• Implemented monitoring for delta store growth and merge events.

Result: ingestion stabilized and read SLAs recovered without over-scaling.

Strategic Recommendations (≈250 words)

1. Adopt a “semantic SLA” mindset

   • Treat CDS/Calculation Views as products with explicit consumers, grains, and performance targets.
   • Ban “one mega-view to serve all use cases” in governance.

2. Institutionalize the tuning workflow

   • Every performance incident must end with: root cause (operator), fix category (model/SQL/physical/system), and regression guardrail.
   • Make PlanViz screenshots and statement hashes part of the incident record.

3. Engineer for concurrency explicitly

   • Build at least one narrow, pre-aggregated model for each high-traffic dashboard domain.
   • Enforce mandatory selectivity (time/org) at the semantic layer.

4. Make plan stability observable

   • After major loads, validate tier-1 statement plans and cardinalities.
   • Refresh statistics intentionally; don’t let them drift.

5. Be deliberate about federation

   • Virtualize by default, but replicate hot subsets when latency, pushdown gaps, or concurrency makes federation unpredictable.
   • Define “pushdown-safe” contracts for remote sources.

Resources & Next Steps (≈150 words)

Official documentation (start here)

• Troubleshooting and Performance Analysis Guide for SAP HANA
• SAP HANA Cloud SQL Reference Guide
• SAP HANA SQLScript Reference
• Calculation Views (SAP HANA Modeling Guide)
• PlanViz in SAP HANA Database Explorer
• Smart Data Access (SDA)
• HDI (HANA Deployment Infrastructure)

Next steps (practitioner actions)

• Build a “top 20 statements” baseline, capture plans, and classify by workload type.
• Refactor one critical model into a narrow dashboard view + a separate exploratory view.
• Add performance regression tests with representative data skew and concurrency.