40 · HANA Views & Functions

Create CDS views vs Calculation Views, use HANA table functions, and run native SQL from CAP.

Episode 40 — HANA Calculation Views in CAP

What you'll learn

• What HANA Calculation Views are and when to use them
• Creating Calculation Views with HANA modeler vs SQL script
• Exposing Calculation Views as CDS projections
• Consuming them from CAP OData service
• Limitations compared to SQL views

Why this matters

Calculation Views are HANA's native analytical engine — column-store optimised for aggregations over millions of rows. For reporting use cases (sales totals, inventory analytics, financial aggregations) they outperform standard SQL views by orders of magnitude.

Calculation View types

Creating a Calculation View (SQL Script)

-- db/hana/CV_SALES_SUMMARY.hdbcalculationview
-- (stored in gen/db/src/ when hand-crafted, or in a separate HANA project)

<!-- CV_SALES_SUMMARY.hdbcalculationview (graphical format, SAP-generated) -->
<?xml version="1.0" encoding="UTF-8"?>
<Calculation:scenario
  xmlns:Calculation="http://www.sap.com/ndb/BiModelCalculation.ecore"
  id="CV_SALES_SUMMARY"
  applyPrivilegeType="NONE"
  dataCategory="CUBE"
  outputViewType="Aggregation">
  <!-- columns, join nodes, aggregation nodes defined here -->
</Calculation:scenario>

For CAP developers, the simpler approach is a SQL-script Calculation View:

-- CREATE CALCULATION SCENARIO via HDI procedure
-- Exposed as a SQL view-like object CAP can query

Practical approach: HANA SQL function

CAP can call HANA SQL scalar and table functions which give similar performance benefits:

-- gen/db/src/SalesReport.hdbfunction (deployed via HDI)
FUNCTION SalesReport(p_year INTEGER)
  RETURNS TABLE (
    product_ID NVARCHAR(36),
    product_name NVARCHAR(200),
    total_qty INTEGER,
    total_revenue DECIMAL(15,2)
  )
LANGUAGE SQLSCRIPT AS
BEGIN
  RETURN SELECT
    p.ID AS product_ID,
    p.name AS product_name,
    SUM(oi.quantity) AS total_qty,
    SUM(oi.quantity * oi.price) AS total_revenue
  FROM ORDERS o
  JOIN ORDER_ITEMS oi ON oi.ORDER_ID = o.ID
  JOIN PRODUCTS p ON p.ID = oi.PRODUCT_ID
  WHERE YEAR(o.CREATED_AT) = :p_year
  GROUP BY p.ID, p.name
  ORDER BY total_revenue DESC;
END;

Exposing in CDS

// srv/analytics-service.cds
service AnalyticsService {
  // Map to HANA table function
  @readonly
  entity SalesReport(year : Integer) as
    select from SalesReport(year);  // CDS doesn't support this directly

  // Use a CDS view instead — CAP generates a standard SQL view
  @readonly
  entity ProductRevenue as select from my.OrderItems {
    product.name as productName,
    sum(quantity * price) as revenue
  }
  group by product.name;
}

Consuming via CAP handler (native SQL)

For true Calculation Views, bypass the CDS layer:

// srv/analytics-service.js
const db = await cds.connect.to('db')

this.on('READ', 'SalesReport', async (req) => {
  const { year = new Date().getFullYear() } = req.data

  // Call the HANA table function directly
  const result = await db.run(
    `SELECT * FROM "SalesReport"(${year}) ORDER BY total_revenue DESC`
  )
  return result
})

Consuming a Graphical Calculation View

After the CV is deployed to HDI:

this.on('READ', 'SalesSummary', async (req) => {
  const result = await db.run(`
    SELECT product_ID, product_name, total_qty, total_revenue
    FROM "_SYS_BIC"."my.bookshop/CV_SALES_SUMMARY"
    WHERE YEAR = ${req.data.year}
  `)
  return result
})

Performance comparison

When to use Calculation Views vs CDS views

Hands-on exercise

1. Write a CDS view ProductRevenue that aggregates order items by product
2. Annotate it with @Aggregation.default: #SUM for Fiori Analytical tables
3. Deploy to HANA and test via OData: GET /analytics/ProductRevenue?$apply=aggregate(revenue with sum)
4. Try calling a HANA table function via db.run(native SQL) from a handler

Checkpoint ✓

• [ ] CDS views work on SQLite + HANA; Calculation Views are HANA-only
• [ ] HANA table functions can be called via db.run('SELECT * FROM "FuncName"(params)')
• [ ] Calculation Views are accessed via _SYS_BIC schema in native SQL
• [ ] Use Calculation Views for > 1M row analytics; CDS views for standard reporting