SAP HANA Database: Architecture, SQL, and Optimization for SAP Incentive Management

SAP Incentive Management runs on SAP HANA Database. If you're writing reports, building integrations, investigating pipeline results, or preparing for a migration from CallidusCloud Commissions — you're working with SAP HANA Database. This article explains what makes HANA different and how to use those differences to your advantage.

Why Columnar Storage Changes Everything

Traditional databases like Oracle Database store data row by row: all columns of row 1, then all columns of row 2, and so on. SAP HANA Database stores data column by column: all values of column 1 for every row, then all values of column 2.

For ICM analytics this matters enormously. When you run SUM(RESULT_AMOUNT) across 10 million result rows, Oracle Database must read every column of every row to get the amount. SAP HANA Database reads only the RESULT_AMOUNT column — and that column is already compressed and loaded in memory. The speed difference on aggregation-heavy ICM reports is dramatic.

SAP HANA Database SQL: What's Different

HANA SQL supports most ANSI SQL. The differences that matter most for SAP Incentive Management work:

-- Top N rows: LIMIT (not ROWNUM like Oracle)
SELECT NAME, RESULT_AMOUNT
FROM   CS_PAYMENT
ORDER BY RESULT_AMOUNT DESC
LIMIT 10;

-- Date functions: HANA-specific
SELECT
  CURRENT_DATE,                         -- today (not SYSDATE)
  DAYS_BETWEEN('2026-01-01', CURRENT_DATE), -- days diff
  ADD_MONTHS(CURRENT_DATE, 3),          -- add 3 months
  YEAR(CURRENT_DATE),                    -- extract year
  MONTH(CURRENT_DATE)                    -- extract month
FROM DUMMY;

-- Null handling: IFNULL (not NVL like Oracle)
SELECT
  NAME,
  IFNULL(RESULT_AMOUNT, 0) AS AMOUNT,    -- HANA style
  COALESCE(RESULT_AMOUNT, 0) AS AMOUNT2   -- ANSI, works on both
FROM CS_PAYMENT;

-- String functions
SELECT
  UPPER(NAME),
  LEFT(NAME, 10),
  LENGTH(NAME),
  TRIM(NAME),
  REPLACE(NAME, ' ', '_')
FROM CS_PARTICIPANT;

Recursive CTEs in SAP HANA Database (Replacing Oracle CONNECT BY)

Oracle Database has a special CONNECT BY PRIOR syntax for hierarchical queries — heavily used in CallidusCloud Commissions participant hierarchy logic. SAP HANA Database uses standard recursive CTEs instead. This is one of the most common rewrites in a Callidus → SAP Incentive Management migration.

-- SAP HANA Database: recursive CTE for participant hierarchy
-- (This replaces: CONNECT BY PRIOR ID = PARENT_ID in Oracle)
WITH RECURSIVE hier (ID, PARENT_ID, NAME, LVL) AS (
  -- Anchor: root participants
  SELECT ID, PARENT_ID, NAME, 1 AS LVL
  FROM   CS_PARTICIPANT
  WHERE  PARENT_ID IS NULL

  UNION ALL

  -- Recursive: children of current level
  SELECT p.ID, p.PARENT_ID, p.NAME, h.LVL + 1
  FROM   CS_PARTICIPANT p
  JOIN   hier h ON h.ID = p.PARENT_ID
  WHERE  h.LVL < 20   -- depth guard
)
SELECT LVL, ID, PARENT_ID, NAME
FROM   hier
ORDER BY LVL, NAME;

Calculation Views: HANA's Query Acceleration Layer

Calculation Views are database objects in SAP HANA Database that define pre-built query logic — similar to database views in Oracle, but with a graphical designer and the ability to do complex aggregations, joins, and transformations natively in the HANA engine.

In SAP Incentive Management, Calculation Views are the recommended way to expose data for SAP Embedded Analytics reporting. Instead of writing raw SQL against the CS_ tables each time, you create a Calculation View that joins the relevant tables, computes attainment percentages and derived fields, and exposes a clean interface for reporting tools.

-- Calculation Views are queried like regular tables/views
-- but execute with HANA's native columnar engine optimizations
SELECT
  PARTICIPANT_NAME,
  PERIOD_ID,
  TOTAL_EARNED,
  QUOTA_AMOUNT,
  ATTAINMENT_PCT,
  TIER_NAME
FROM   "_SYS_BIC"."sfim/CV_INCENTIVE_RESULTS"
WHERE  PERIOD_ID = '2026-Q1'
  AND  ATTAINMENT_PCT >= 80
ORDER BY ATTAINMENT_PCT DESC;

HANA SQL Script: Procedural Code in SAP HANA Database

HANA SQL Script is the procedural extension for SAP HANA Database — the equivalent of Oracle PL/SQL. Key structural differences: table variables (no cursors needed for set-based operations), and the emphasis on set-based rather than row-by-row processing.

CREATE OR REPLACE PROCEDURE
  get_period_summary (
    IN  iv_period_id    NVARCHAR(20),
    OUT ot_results      TABLE (
      PARTICIPANT_NAME  NVARCHAR(200),
      TOTAL_EARNED      DECIMAL(18,2),
      ATTAINMENT_PCT    DECIMAL(8,2)
    )
  )
LANGUAGE SQLSCRIPT AS
BEGIN
  -- Table variable: set-based, no cursor needed
  -- Note: CS_QUOTA stores quota definitions (QUOTASEQ, NAME, DESCRIPTION).
  -- Quota values are in CS_QUOTA_VARIABLES; attainment calc omitted here.
  lt_results = SELECT
    p.NAME              AS PARTICIPANT_NAME,
    SUM(r.RESULT_AMOUNT) AS TOTAL_EARNED
  FROM   CS_PAYMENT r
  JOIN   CS_PARTICIPANT p ON p.ID = r.PARTICIPANT_ID
  WHERE  r.PERIOD_ID = :iv_period_id
  GROUP BY p.NAME;

  ot_results = SELECT * FROM :lt_results;
END;

Performance Tips for SAP Incentive Management Data

• Avoid SELECT * — in columnar storage, selecting all columns defeats the column-pruning optimization. Select only what you need.
• Filter early on indexed columns — PARTICIPANT_ID, PERIOD_ID, and PLAN_ID are always indexed in the CS_ tables. Use them in your WHERE clause first.
• Use LIMIT during development — add LIMIT 1000 while developing queries on production-sized data. Remove before final use.
• Calculation Views over raw SQL for reporting — they benefit from HANA's pre-aggregation and caching mechanisms that raw ad-hoc queries don't.
• Check query plan with EXPLAIN PLAN — EXPLAIN PLAN FOR SELECT ... shows you whether HANA is doing a full column scan or using an index.