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

Sarah Chen, Lead SAP Architect — SAPExpert.AI Weekly Deep Research Series

Executive Summary (≈150 words)

SAP HANA performance is rarely “fixed” by a single parameter. In production, the largest gains come from model-driven performance engineering: designing tables, views (Calculation Views / ABAP CDS), and transformation logic so the optimizer can prune early, parallelize safely, and avoid row explosion under concurrency.

This report provides a practitioner-grade playbook for SAP HANA 2.0 SPS06+ and SAP HANA Cloud landscapes focused on advanced modeling and tuning. Key recommendations:

1. Model for pruning contracts: ensure every high-volume query path enforces selective predicates (time/org) early via parameters, partitioning, and column pruning.
2. Treat joins as the main risk surface: validate join cardinality, prevent many-to-many amplification, and use bridge/aggregation patterns deliberately.
3. Prefer declarative models over opaque SQLScript: use scripted logic only when it preserves set-based execution and optimizer visibility.
4. Operationalize performance: plan analysis, regression detection, statistics hygiene, delta merge discipline, and workload isolation.

Technical Foundation (≈400–500 words)

1) HANA execution reality: columnar, parallel, cost-based

SAP HANA’s column store enables high compression and vectorized processing, but only if the query plan can:

• Prune columns (avoid materializing unused attributes)
• Prune partitions (scan only relevant slices)
• Push down filters/aggregations early to reduce intermediate result size

This is why “semantic” correctness alone is insufficient—your model must be optimizer-friendly.

Official references:

• SQL semantics, hints, and explain capabilities: SAP HANA SQL Reference Guide (SAP HANA Cloud)
• System monitoring views used in tuning: SAP HANA Cloud: System Views Reference

2) Storage mechanics that directly impact performance

Delta/Main storage in column tables is a feature—not a footnote. Writes land in delta; reads become unstable when delta grows and merges compete with query CPU.

• Too frequent merges → CPU contention (HTAP pain)
• Too infrequent merges → read amplification and plan instability

Reference:

• Operational concepts and merge behavior: SAP HANA Administration Guide (SAP HANA Platform)

3) Modeling layers: CDS vs Calculation Views vs SQLScript

A modern enterprise standard typically looks like this:

• ABAP CDS (S/4HANA VDM) for semantics, authorizations, analytical consumption contracts
  Reference: ABAP CDS – ABAP Platform
• HANA Calculation Views (HDI-deployed) for reusable analytic transformations, high-performance star schemas, and cross-domain marts
  Reference: SAP HANA Cloud: Calculation Views
• SQL / SQLScript for targeted, set-based transformations where graphical/declarative modeling is insufficient
  Reference: SAP HANA Cloud: SQLScript Reference

4) The core tuning principle: intermediate result size is the real cost

Most HANA “slow queries” are not slow because scanning is expensive—they’re slow because a model produces:

• Wide intermediate row sets
• Explosive join multiplicities
• Late filtering (filters applied after big joins/unions)
• Unnecessary columns, conversions, or non-sargable predicates

Your objective: minimize intermediate data volume early while preserving correctness.

Implementation Deep Dive (≈800–1000 words)

1) A reference architecture for performant analytic modeling

flowchart LR
  A[Landing / Replication<br>SLT/SDI/ODP] --> B[Curated Persisted Layer<br>Column tables, partitions]
  B --> C[Transformation Layer<br>Calc Views (star), limited SQLScript]
  C --> D[Semantic Consumption<br>CDS Analytical Queries / SAC]
  B --> E[Federation (SDA)<br>Virtual tables] --> C

Rule of thumb: persist what is (a) heavy, (b) reused frequently, or (c) federated with weak pushdown; virtualize what is lightweight or highly selective.

2) Table design: column store, partitioning, and datatype discipline

2.1 Column store defaults—and when row store is justified

• Use column store for facts/dimensions and anything scanned/aggregated.
• Use row store sparingly for small control tables with point lookups.

Reference:

• Table types and SQL DDL: CREATE TABLE (SAP HANA SQL Reference)

2.2 Partitioning pattern for large facts (range-by-date, optional hash subpartition)

Partitioning is only useful if your predicates enable partition pruning.

Example (range partition by posting date):

CREATE COLUMN TABLE MART.FACT_SALES (
  SALES_DOC_ID     BIGINT       NOT NULL,
  ITEM_ID          INTEGER      NOT NULL,
  POSTING_DATE     DATE         NOT NULL,
  COMPANY_CODE     NVARCHAR(4)  NOT NULL,
  CUSTOMER_ID      NVARCHAR(10),
  PRODUCT_ID       NVARCHAR(18),
  NET_AMOUNT       DECIMAL(15,2),
  CURRENCY         NVARCHAR(5),
  QUANTITY         DECIMAL(15,3),
  UNIT             NVARCHAR(3)
)
PARTITION BY RANGE (POSTING_DATE) (
  PARTITION '2025Q1' <= VALUES < '2025-04-01',
  PARTITION '2025Q2' <= VALUES < '2025-07-01',
  PARTITION 'MAX'   <= VALUES < MAXVALUE
);

Advanced guidance (often missed):

• Partition key must match query predicates and datatype—avoid TO_NVARCHAR(POSTING_DATE) filters that disable pruning.
• If you must support “company-centric” access patterns, consider composite strategies (range + hash), but validate with plan output.

Reference:

• Partition DDL and behavior: SAP HANA Cloud: Table Partitioning

3) Modeling joins to prevent row explosion (the #1 root cause)

3.1 Cardinality engineering (treat it as a design artifact)

A join is not “free” because HANA is in-memory. Wrong cardinality assumptions produce:

• Wrong join order
• Wrong join type selection
• Massive intermediate results

Checklist (practitioner standard):

• Keys aligned in datatype/length/collation
• Dimension uniqueness enforced (or deduped explicitly)
• Many-to-many is never “accidental”—it must be modeled with a bridge

3.2 Bridge pattern for many-to-many dimensions

Example: products assigned to multiple categories; avoid joining raw mapping directly to fact.

-- Bridge: ensure controlled grain (Product x Category) with a uniqueness constraint
CREATE COLUMN TABLE MART.BRIDGE_PROD_CAT (
  PRODUCT_ID   NVARCHAR(18) NOT NULL,
  CATEGORY_ID  NVARCHAR(40) NOT NULL,
  PRIMARY KEY (PRODUCT_ID, CATEGORY_ID)
);

-- Optional: pre-aggregate fact at product level before joining multi-valued dimension
CREATE VIEW MART.V_FACT_PROD_AGG AS
SELECT
  PRODUCT_ID,
  POSTING_DATE,
  SUM(NET_AMOUNT) AS NET_AMOUNT
FROM MART.FACT_SALES
GROUP BY PRODUCT_ID, POSTING_DATE;

Why this works: you control the grain before multiplying rows.

4) Calculation View design: optimizer-first modeling (HDI)

4.1 Node order is not just cosmetics: design for early reduction

Pattern: Projection (filter) → minimal joins → early aggregation → final enrichments.

Key practices:

• Put mandatory filters in the lowest possible projection
• Aggregate before joining to “wide” attributes when safe
• Avoid unioning wide datasets unless you normalize datatypes first

Reference:

• Calc View modeling concepts: SAP HANA Cloud: Calculation View Operators

4.2 Parameters as a “pruning contract”

Use input parameters to force selective access paths.

Example (conceptual): require P_FROM_DATE / P_TO_DATE and apply at the base projection so partition pruning triggers.

Operational trick (high impact, not common knowledge):
In shared enterprise views, treat “date/org parameters” as a contract, not convenience. Provide wrapper consumption views for optionality, but keep the core reusable view prunable by design.

5) ABAP CDS: keeping semantics without killing pushdown

5.1 Avoid non-sargable filters and implicit conversions

Common anti-patterns:

• Wrapping DB columns in functions in WHERE
• Comparing mismatched datatypes (NVARCHAR vs CHAR vs NUMC)
• Late filters applied above associations that expand results

Example CDS view (simplified) with parameterized, pushdown-friendly filter:

@AbapCatalog.sqlViewName: 'ZV_SALESQ'
@EndUserText.label: 'Sales Query (Prunable)'
@Analytics.query: true
define view entity ZI_SalesQuery
  with parameters
    p_from_date : abap.dats,
    p_to_date   : abap.dats
  as select from I_BillingDocumentItem as item
  association [0..1] to I_Customer as _Customer
    on _Customer.Customer = item.SoldToParty
{
  key item.BillingDocument,
  key item.BillingDocumentItem,
      item.BillingDocumentDate,
      item.SoldToParty,
      _Customer.CustomerName,
      item.NetAmount
}
where item.BillingDocumentDate between :p_from_date and :p_to_date;

Notes:

• The filter is directly on the base field (sargable).
• Association cardinality is explicit, reducing the chance of accidental multiplicities.

Reference:

• CDS concepts and performance implications: ABAP CDS Views (ABAP Platform)

6) SQLScript: how to be powerful without becoming opaque

6.1 Set-based SQLScript template (avoid loops, avoid row-by-row)

CREATE OR REPLACE PROCEDURE MART.P_BUILD_SALES_MART (IN P_DATE_FROM DATE, IN P_DATE_TO DATE)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN

  -- 1) Reduce early (pruning contract)
  sales_base =
    SELECT PRODUCT_ID, CUSTOMER_ID, POSTING_DATE, NET_AMOUNT
    FROM MART.FACT_SALES
    WHERE POSTING_DATE BETWEEN :P_DATE_FROM AND :P_DATE_TO;

  -- 2) Aggregate early
  sales_agg =
    SELECT PRODUCT_ID, POSTING_DATE, SUM(NET_AMOUNT) AS NET_AMOUNT
    FROM :sales_base
    GROUP BY PRODUCT_ID, POSTING_DATE;

  -- 3) Join small-to-large after reduction
  UPSERT MART.SALES_MART
    SELECT a.PRODUCT_ID, a.POSTING_DATE, a.NET_AMOUNT, p.BRAND_ID
    FROM :sales_agg a
    JOIN MART.DIM_PRODUCT p
      ON p.PRODUCT_ID = a.PRODUCT_ID;

END;

Advanced guidance (often missed):

• Table variables can still cause materialization depending on plan; validate with explain/plan visualization.
• Prefer patterns that keep predicates on base tables and preserve join reorder freedom.

Reference:

• SQLScript language and semantics: SQLScript Reference (SAP HANA Cloud)

7) Plan analysis and observability: make it operational

7.1 Explain plan and plan cache (systematic workflow)

• Capture the exact SQL generated by CDS or Calc View consumption.
• Use EXPLAIN PLAN / plan visualization to locate:
  • where row counts spike
  • where partition pruning failed
  • expensive operators (hash joins, large aggregations)

Reference:

• Explain capabilities and syntax: EXPLAIN PLAN (SAP HANA SQL Reference)

7.2 System views that matter in real tuning

Start with:

• Expensive statements (top SQL by elapsed time / CPU / memory)
• Active statements (current bottlenecks)
• Plan cache (regressions, multiple plans per statement)

Reference:

• Where to find these views and how to interpret them: SAP HANA System Views Reference (Monitoring)

Advanced Scenarios (≈500–600 words)

1) HTAP contention: stabilizing read latency under write + merge load

Symptom pattern: “fast in the morning, unpredictable in peak hours.”
Root causes often include:

• delta store growth on hot tables
• merges competing with query CPU/memory
• write-heavy ingestion colliding with reporting storms

Mitigations (architectural, not just knobs):

1. Hot/warm separation: keep “today” data in a hot partition, periodically compact or move older data (and ensure consumers filter by date).
2. Staging + batch merge: ingest into write-optimized staging, then merge into curated column tables in controlled windows.
3. Workload isolation: separate ingestion users/services from reporting users; apply workload management where available (limits, classes, resource groups).

References:

• Administration/operations guidance: SAP HANA Administration Guide
• Workload management concepts (cloud): SAP HANA Cloud: Workload Management

2) Federation with SDA: the “pushdown or persist” decision

Federation is attractive—until a cross-source join forces:

• large data shipping
• remote full scans
• unpredictable latency

Decision matrix (field-tested):

• If filters are highly selective and push down to the remote source → keep virtual
• If transformations are heavy and reused frequently → persist curated layer
• If cross-source joins are required → persist at least one side to local HANA

Reference:

• Smart Data Access concepts and limitations: SAP HANA Cloud: Smart Data Access

3) Plan regressions at scale: statistics hygiene + skew engineering

What changes over time: data distributions, not just volume. A plan that was optimal at 10M rows can fail at 500M rows if:

• a predicate becomes non-selective
• a “top customer” dominates
• partitions become unbalanced

Advanced practices:

• Establish weekly statistics review for core marts (or post-load stats refresh).
• Detect skew and redesign:
  • repartition by the actual selective predicate
  • introduce summary tables for high-concurrency dashboards
  • separate “elephant customer” workloads if necessary

Reference:

• Statistics and optimizer behavior: SAP HANA Cloud: Optimizer and Statistics

Real-World Case Studies (≈300–400 words)

Case 1 — Retail margin analytics: fixing accidental many-to-many

Situation: A retailer built a Calculation View joining FACT_SALES to PROMO_ASSIGNMENTS (multi-valued) and PRODUCT_HIER (snowflaked). Single-user tests were “okay,” but month-end concurrency collapsed.

Findings (PlanViz + operator row counts):

• The join between sales items and promo assignments amplified rows 8–20× depending on product and date.
• Filtering by date existed—but was applied after the join due to modeling node placement.

Fix:

1. Enforced a pruning contract: date parameter applied in the base projection on the fact.
2. Introduced a bridge + pre-aggregation: aggregated sales to (Product, Date, Store) before joining to promo assignments.
3. Flattened the worst snowflake links into a curated dimension table for the hot path.

Result: 5–12× reduction in intermediate rows, stabilized memory per query, and predictable performance under 200+ concurrent SAC users.

Case 2 — Manufacturing event telemetry: partitioning + early aggregation

Situation: High-volume append-only event table (~2B rows/year). Queries filtered by time range but still scanned broadly.

Findings:

• Partition key existed but queries used TO_NVARCHAR(EVENT_TS) in filters, disabling partition pruning.
• Several scripted views used loops to compute shift buckets.

Fix:

• Rewrote filters to be sargable on timestamp/date.
• Partitioned by date range and introduced an hourly/day summary table maintained incrementally.
• Replaced procedural logic with set-based bucketing.

Result: Partition pruning activated reliably; dashboards moved from minutes to seconds.

Strategic Recommendations (≈200–300 words)

1) Standardize on “performance contracts” for shared models

For enterprise reusable views (CDS entities, Calc Views):

• Mandatory time/org parameters (or enforced filters in consumption contracts)
• Documented grain and join cardinality expectations
• “No many-to-many without bridge/aggregation design review”

2) Build a performance engineering lifecycle (shift-left)

• Automated capture of:
  • generated SQL (from CDS/Calc Views)
  • explain plans
  • runtime KPIs (elapsed/CPU/memory)
• Plan diffs as part of CI/CD gates for high-impact artifacts (HDI + ABAP transports)

3) Operationalize concurrency, not just single-query speed

• Define per-workload budgets: max statement memory, concurrency caps, and peak-hour rules.
• Use workload isolation patterns (separate users/schemas/services) and, where available, workload management features.

4) Invest in curated persistence strategically

Persist the transformations that are heavy, reused, or federated; virtualize the final semantic layer. This typically reduces total cost by improving concurrency and lowering “performance firefighting” time.

Resources & Next Steps (≈150 words)

Official SAP documentation (start here)

• SQL and execution fundamentals: SAP HANA SQL Reference Guide
• System monitoring and tuning views: SAP HANA System Views Reference
• Calculation View modeling: Calculation Views (SAP HANA Cloud)
• SQLScript best practices: SQLScript Reference (SAP HANA Cloud)
• ABAP CDS modeling: ABAP CDS (ABAP Platform)

Action plan for the next 2 weeks

1. Baseline top 20 statements (elapsed/CPU/memory) and capture their plans.
2. For each offender: validate pruning, fix join cardinality, aggregate early, remove non-sargable predicates.
3. Add a regression harness (plan + runtime thresholds) to your delivery pipeline for key marts/queries.