Writing Queries for SAP Embedded Analytics

TL;DR

Queries for Embedded Analytics differ from operational queries. They run repeatedly by business users, against large data sets, and must be performant and robust. Always COALESCE nullable amount fields (no NULLs in dashboards), use meaningful column aliases (users see these names), and filter early (PERIOD_ID in WHERE clause for partition pruning).

Five canonical queries power most IM reporting: Quota Attainment (CSC_RESULTS + CSC_PARTICIPANT + CSC_QUOTA with CASE for attainment bands), Pipeline Monitoring (CSC_PIPELINE_RUN with SECONDS_BETWEEN for duration), Earnings Distribution (CASE WHEN binning + COUNT), Coverage Gap (LEFT JOIN to find unassigned participants), and Participant Hierarchy (tree query for org validation).

Performance tips: Filter by PERIOD_ID to leverage HANA partitioning, avoid SELECT *, push WHERE conditions early, use CTEs instead of nested subqueries, leverage indexes on PARTICIPANT_ID and PERIOD_ID, and use COALESCE instead of CASE for simple null substitution.

Most Embedded Analytics stories run on pre-built Calculation Views. But when you need custom reporting, you write SQL queries against SAP HANA Database CSC_ tables. The difference between a query that works and a query that is part of a reliable production dashboard is subtle—it comes down to NULL handling, performance, and naming discipline. This lesson walks through five canonical reporting queries and shows you how to write production-ready SQL for Embedded Analytics.

Reporting Query Principles

Queries for reporting are different from operational queries. They have different requirements:

Aspect	Operational Query	Reporting Query
Execution context	Runs once, in a batch or script, by a developer	Runs repeatedly, in a BI tool, by business users
Debugging	Developer can examine code and logs	User sees a dashboard. If it is wrong, they do not know why.
NULL handling	NULLs are often acceptable (represent missing data)	NULLs break dashboards (a chart cannot add up a column with NULLs)
Column naming	Names are for developers (internal naming conventions)	Names are for users (they appear in dashboards, exports, etc.)
Performance	Acceptable if it runs in minutes	Must be fast enough for interactive use (sub-second is ideal)
Error tolerance	Errors are caught and handled in code	No error handling—if it fails, users see a blank dashboard

The key insight: Reporting queries are written for a different audience and require different discipline.

Query Principle 1: COALESCE All Amount Fields

In a reporting context, NULLs are poison. If a quota amount is NULL and you compute RESULT_AMOUNT / QUOTA_AMOUNT, the result is NULL. If you sum a column of amounts with NULLs in it, the SUM ignores the NULLs but the user sees a dashboard number that does not match their spreadsheet total (the numbers they added up include zeros, not NULLs).

Rule: COALESCE every amount field to 0 (or the appropriate default).

-- WRONG: NULLs cause problems downstream
SELECT
  p.NAME,
  r.QUOTA_AMOUNT,
  r.RESULT_AMOUNT,
  r.INCENTIVE_AMOUNT
FROM CSC_RESULTS r
JOIN CSC_PARTICIPANT p ON p.ID = r.PARTICIPANT_ID;

-- CORRECT: Always default amounts to 0
SELECT
  p.NAME,
  COALESCE(r.QUOTA_AMOUNT, 0)       AS QUOTA_AMOUNT,
  COALESCE(r.RESULT_AMOUNT, 0)     AS RESULT_AMOUNT,
  COALESCE(r.INCENTIVE_AMOUNT, 0) AS INCENTIVE_AMOUNT
FROM CSC_RESULTS r
JOIN CSC_PARTICIPANT p ON p.ID = r.PARTICIPANT_ID;

Query Principle 2: Meaningful Column Aliases

When a user sees a dashboard, they see column headers. Make them readable and business-aligned. Not "R.RESULT_AMOUNT" but "RESULT AMOUNT (USD)". Not "ATTAINMENT_PCT" but "ATTAINMENT %".

-- WRONG: Developer naming
SELECT
  p.NAME AS PNAME,
  r.RESULT_AMOUNT AS RSLT_AMT,
  ROUND(r.RESULT_AMOUNT / COALESCE(q.QUOTA_AMOUNT, 1) * 100, 1) AS ATTAIN_PCT;

-- CORRECT: User-facing naming
SELECT
  p.NAME                                       AS "Participant Name",
  COALESCE(q.QUOTA_AMOUNT, 0)           AS "Quota (USD)",
  COALESCE(r.RESULT_AMOUNT, 0)         AS "Result (USD)",
  ROUND(r.RESULT_AMOUNT / COALESCE(q.QUOTA_AMOUNT, 1) * 100, 1)
                                               AS "Attainment %";

Query Principle 3: Filter Early for Performance

SAP HANA Database CSC_ tables are partitioned by PERIOD_ID. When you filter by PERIOD_ID in the WHERE clause, HANA can skip partitions that do not match and only scan the relevant partition. This is partition pruning and it is a huge performance win.

Always include PERIOD_ID in your WHERE clause, never in a HAVING clause or in a subquery filter.

-- WRONG: Filter in HAVING (no partition pruning)
SELECT p.NAME, SUM(r.RESULT_AMOUNT)
FROM CSC_RESULTS r
JOIN CSC_PARTICIPANT p ON p.ID = r.PARTICIPANT_ID
GROUP BY p.NAME
HAVING r.PERIOD_ID = :period_id;

-- CORRECT: Filter in WHERE (partition pruning applied)
SELECT p.NAME, SUM(r.RESULT_AMOUNT)
FROM CSC_RESULTS r
WHERE r.PERIOD_ID = :period_id
JOIN CSC_PARTICIPANT p ON p.ID = r.PARTICIPANT_ID
GROUP BY p.NAME;

Query Principle 4: Avoid SELECT *

Explicit is better than implicit. When you write a query for reporting, list the columns you actually need. This makes the query self-documenting, reduces data transfer, and avoids surprises when new columns are added to the table.

The Five Canonical Reporting Queries

Query 1: Quota Attainment Detail

-- Quota attainment with bands and formatted names
SELECT
  p.NAME                                   AS "Participant",
  mgr.NAME                                 AS "Manager",
  cp.PLAN_NAME                             AS "Plan",
  pd.PERIOD_NAME                           AS "Period",
  COALESCE(q.QUOTA_AMOUNT, 0)          AS "Quota (USD)",
  COALESCE(r.RESULT_AMOUNT, 0)        AS "Result (USD)",
  COALESCE(r.INCENTIVE_AMOUNT, 0)   AS "Incentive (USD)",
  CASE
    WHEN COALESCE(q.QUOTA_AMOUNT, 0) = 0
    THEN NULL
    ELSE ROUND(r.RESULT_AMOUNT / q.QUOTA_AMOUNT * 100, 1)
  END                                    AS "Attainment %",
  CASE
    WHEN COALESCE(r.RESULT_AMOUNT, 0) >= COALESCE(q.QUOTA_AMOUNT, 1) * 1.2
    THEN 'ABOVE TARGET'
    WHEN COALESCE(r.RESULT_AMOUNT, 0) >= COALESCE(q.QUOTA_AMOUNT, 1) * 0.8
    THEN 'ON TRACK'
    ELSE 'BELOW TARGET'
  END                                    AS "Status"
FROM      CSC_RESULTS      r
WHERE     r.PERIOD_ID = :period_id
JOIN      CSC_PARTICIPANT  p ON p.ID = r.PARTICIPANT_ID
LEFT JOIN CSC_PARTICIPANT  mgr ON mgr.ID = p.MANAGER_ID
JOIN      CSC_COMP_PLAN    cp ON cp.ID = r.PLAN_ID
JOIN      CSC_PERIOD       pd ON pd.ID = r.PERIOD_ID
LEFT JOIN CSC_QUOTA        q ON  q.PARTICIPANT_ID = r.PARTICIPANT_ID
                                           AND q.PERIOD_ID      = r.PERIOD_ID
                                           AND q.PLAN_ID        = r.PLAN_ID
WHERE     r.STATUS IN ('APPROVED', 'PROCESSED')
ORDER BY  mgr.NAME, p.NAME;

Query 2: Pipeline Run Monitoring

-- Monitor recent calculation runs and their status
SELECT
  r.ID                                    AS "Run ID",
  r.PIPELINE_ID                           AS "Pipeline",
  pd.PERIOD_NAME                          AS "Period",
  r.STATUS                                AS "Status",
  r.START_TIME                            AS "Start Time",
  r.END_TIME                              AS "End Time",
  SECONDS_BETWEEN(
    r.START_TIME,
    COALESCE(r.END_TIME, CURRENT_TIMESTAMP)) AS "Duration (sec)",
  COALESCE(r.ROWS_PROCESSED, 0)      AS "Rows Processed",
  r.ERROR_MESSAGE                         AS "Error Message"
FROM   CSC_PIPELINE_RUN r
LEFT JOIN CSC_PERIOD pd ON pd.ID = r.PERIOD_ID
WHERE  r.START_TIME >= ADD_DAYS(CURRENT_DATE, -30)
ORDER BY r.START_TIME DESC;

Query 3: Earnings Distribution Histogram

-- Histogram of incentive amounts by earning band
SELECT
  CASE
    WHEN COALESCE(r.INCENTIVE_AMOUNT, 0) < 10000
    THEN '$0 - $10K'
    WHEN COALESCE(r.INCENTIVE_AMOUNT, 0) < 25000
    THEN '$10K - $25K'
    WHEN COALESCE(r.INCENTIVE_AMOUNT, 0) < 50000
    THEN '$25K - $50K'
    WHEN COALESCE(r.INCENTIVE_AMOUNT, 0) < 100000
    THEN '$50K - $100K'
    ELSE '$100K+'
  END                                    AS "Earning Band",
  COUNT(*)                           AS "Participant Count",
  ROUND(AVG(COALESCE(r.INCENTIVE_AMOUNT, 0)), 2)
                                           AS "Average Incentive",
  ROUND(SUM(COALESCE(r.INCENTIVE_AMOUNT, 0)), 2)
                                           AS "Total Incentive"
FROM   CSC_RESULTS r
WHERE  r.PERIOD_ID = :period_id
  AND r.STATUS = 'APPROVED'
GROUP BY "Earning Band"
ORDER BY "Earning Band";

Query 4: Coverage Gap Detection

-- Pre-period health check: who has no plan assignment?
SELECT
  p.ID                                    AS "Participant ID",
  p.NAME                                  AS "Participant Name",
  mgr.NAME                                AS "Manager",
  p.STATUS                                AS "Status",
  CASE
    WHEN COUNT(pp.PLAN_ID) > 0
    THEN 'ASSIGNED'
    ELSE 'UNASSIGNED'
  END                                    AS "Assignment Status",
  GROUP_CONCAT(DISTINCT cp.PLAN_NAME, '; ')
                                           AS "Plans"
FROM      CSC_PARTICIPANT p
LEFT JOIN CSC_PARTICIPANT mgr
  ON mgr.ID = p.MANAGER_ID
LEFT JOIN CSC_PLAN_PARTICIPANT pp
  ON  pp.PARTICIPANT_ID = p.ID
  AND pp.PERIOD_ID = :period_id
  AND pp.STATUS = 'ACTIVE'
LEFT JOIN CSC_COMP_PLAN cp
  ON cp.ID = pp.PLAN_ID
WHERE p.STATUS = 'ACTIVE'
GROUP BY p.ID, p.NAME, mgr.NAME, p.STATUS
HAVING COUNT(pp.PLAN_ID) = 0
ORDER BY p.NAME;

Query 5: Multi-Period Trend

-- Show attainment trending over 4 periods
SELECT
  p.NAME                                   AS "Participant",
  pd.PERIOD_NAME                           AS "Period",
  COALESCE(q.QUOTA_AMOUNT, 0)          AS "Quota",
  COALESCE(r.RESULT_AMOUNT, 0)        AS "Result",
  CASE
    WHEN COALESCE(q.QUOTA_AMOUNT, 0) = 0
    THEN NULL
    ELSE ROUND(r.RESULT_AMOUNT / q.QUOTA_AMOUNT * 100, 1)
  END                                    AS "Attainment %"
FROM      CSC_RESULTS      r
JOIN      CSC_PARTICIPANT  p ON p.ID = r.PARTICIPANT_ID
JOIN      CSC_COMP_PLAN    cp ON cp.ID = r.PLAN_ID
JOIN      CSC_PERIOD       pd ON pd.ID = r.PERIOD_ID
LEFT JOIN CSC_QUOTA        q ON  q.PARTICIPANT_ID = r.PARTICIPANT_ID
                                           AND q.PERIOD_ID      = r.PERIOD_ID
                                           AND q.PLAN_ID        = r.PLAN_ID
WHERE     r.PLAN_ID = :plan_id
  AND     r.PERIOD_ID IN (:period1, :period2, :period3, :period4)
  AND     r.STATUS = 'APPROVED'
ORDER BY  p.NAME, pd.PERIOD_NAME;

Performance Tips for HANA Reporting Queries

Pattern	Slow	Fast	Why
Filtering	Filter in HAVING clause or late WHERE	Filter by PERIOD_ID early in WHERE clause	Partition pruning — HANA skips irrelevant partitions
Column selection	SELECT *	SELECT specific columns	Reduces data transfer and makes intent clear
Null substitution	CASE WHEN col IS NULL THEN 0 ELSE col END	COALESCE(col, 0)	COALESCE is a built-in function, optimized for this exact case
Subqueries	Nested subqueries in FROM	CTEs (WITH clauses) or inline joins	CTEs are easier to optimize, more readable
Joins	Join on non-indexed columns	Join on PARTICIPANT_ID or PERIOD_ID (indexed)	Indexes on CSC_ tables make these joins fast
Distinct	SELECT DISTINCT	Join more carefully to avoid duplicates	DISTINCT forces a full scan and deduplication

💡HANA indexing strategy: CSC_ tables have primary indexes on (PARTICIPANT_ID, PERIOD_ID, PLAN_ID) and (PERIOD_ID). Always filter by PERIOD_ID in your WHERE clause to leverage partition pruning. Join on PARTICIPANT_ID when possible—these joins are optimized. Avoid cross joins or joins on non-indexed columns.

Testing Your Queries

Before deploying a query to Embedded Analytics:

Test in HANA Database Explorer. Run the query manually with test parameters and verify the results. Check for edge cases (NULL handling, divide-by-zero scenarios).
Verify column names. Are they user-friendly? Would a business user understand what each column is?
Check performance. Does the query complete in under 5 seconds? If not, review filters and joins.
Validate against manual spot-checks. Pick a participant at random and verify that the attainment % calculation is correct by hand.
Test with edge cases. Zero quota (divide-by-zero protection), missing assignments, NULL amounts.

Key Takeaways

Reporting queries are written for business users, not developers. They must be robust, fast, and readable.
Always COALESCE amount fields to zero. NULLs break dashboards.
Use meaningful column aliases. Users see these names in their dashboards.
Filter by PERIOD_ID in the WHERE clause to leverage HANA partition pruning.
The five canonical queries cover 80% of IM reporting: Quota Attainment, Pipeline Monitoring, Earnings Distribution, Coverage Gap, and Multi-Period Trend.
Avoid SELECT *, use explicit columns. Avoid nested subqueries, use CTEs. Avoid DISTINCT, join more carefully.
Test in HANA Database Explorer before deploying to Embedded Analytics.

← Previous: Pre-Built Content Next: Crystal vs Embedded →