Skip to content

README.md

Latest commit

 

History

History
246 lines (150 loc) · 14.4 KB

README.md

File metadata and controls

246 lines (150 loc) · 14.4 KB

Exercise 6 - Create Calculation View and Expose via CAP (SAP HANA Cloud)

In this exercise you will build your first SAP HANA Calculation View inside Business Application Studio, then wire it into your CAP application so it is queryable as an OData endpoint — all without writing a single SQL CREATE VIEW statement.

Perform all the steps in 👉 tutorial: Create Calculation View and Expose via CAP (SAP HANA Cloud)

Background

What is a Calculation View?

A Calculation View is a HANA-native modeling artifact that lives in the HDI container alongside your tables. Unlike a SQL view (which is a single SELECT statement), a Calculation View is assembled graphically from nodes — Projection, Join, Aggregation, Union — and is compiled by HANA's query optimizer into a highly efficient execution plan at query time.

Key differences from a SQL view:

Feature SQL View Calculation View
Authoring SQL CREATE VIEW statement Graphical node editor in BAS / HANA Studio
Multiple data sources One query; joins written manually Dedicated Join/Union nodes
Aggregation Handled in the SELECT Dedicated Aggregation node with measure/dimension semantics
Column engine Standard SQL engine HANA column store engine — parallelised, in-memory
Deployment artifact .hdbview .hdbcalculationview

The .hdbcalculationview file you create is deployed into the HDI container during cds deploy. After deployment, HANA exposes it as a view-like object that can be queried with standard SQL.

The proxy entity pattern

CAP manages its own set of database objects. When you deploy a CAP project, the framework generates tables and SQL views from your CDS entities. If you want to expose a database object that CAP did not create — a Calculation View, a table from another schema, or a legacy view — you need to tell CAP to treat it as a proxy: an entity that maps to an already-existing database object without CAP trying to create or modify it.

Two annotations do this job together:

@cds.persistence.exists    // CAP: don't create or alter this object in the DB
@cds.persistence.calcview  // CAP: generate the OData-to-SQL translation layer for a calc view
Entity V_INTERACTION { ... }

@cds.persistence.exists alone would work for a regular SQL view or table. The additional @cds.persistence.calcview is needed because HANA Calculation Views require a thin SQL wrapper generated by CAP (a generated .hdbview) to be accessible over OData. Without it, the OData-to-SQL query generated by CAP would not reach the calculation view correctly.

Why context instead of namespace?

In the earlier exercises the CDS data model used a namespace declaration:

namespace app.interactions;
entity Headers { ... }   // becomes app.interactions.Headers in the DB

A namespace is file-scoped — it prefixes every entity in the file with app.interactions.. That works perfectly for your own entities because CAP controls their names in the database.

For the Calculation View proxy entity the name must match the underlying HDI object exactly — V_INTERACTION, not app.interactions.V_INTERACTION. To achieve this, the tutorial converts the namespace declaration to a context block:

context app.interactions {
    entity Headers { ... }   // still becomes app.interactions.Headers
    entity Items   { ... }   // still becomes app.interactions.Items
}

// V_INTERACTION is OUTSIDE the context — no prefix applied
@cds.persistence.exists
@cds.persistence.calcview
Entity V_INTERACTION { ... }

context applies the prefix only to entities declared inside the block, leaving V_INTERACTION at the top level where its name matches the HDI artifact exactly.

What the finished proxy entity looks like

After running hana-cli inspectView and copying the column definitions, the proxy entity in db/interactions.cds looks like this:

@cds.persistence.exists
@cds.persistence.calcview
Entity V_INTERACTION {
    key CREATEDAT    : Timestamp;
        CREATEDBY    : String(255);
        MODIFIEDAT   : Timestamp;
        MODIFIEDBY   : String(255);
        PARTNER      : String(10);
        COUNTRY_CODE : String(3);
        TEXT         : String(1024);
        DATE         : String;
        PRICE        : Decimal(10);
        CURRENCY_CODE: String(3);
}

And the service projection in srv/interaction_srv.cds exposes it as a read-only OData entity set:

@readonly
entity V_Interaction as projection on V_INTERACTION;

Why a database redeployment is required

Adding the Calculation View proxy entity to the CAP service definition triggers a database redeployment step (cds deploy --to hana). This is needed because CAP must generate and deploy a thin .hdbview wrapper on top of the Calculation View artifact so that the OData service layer can address it correctly. Without this deployment step the service will fail to resolve the entity at runtime.

This is different from the Stored Procedure you will add in Exercise 7 — stored procedures are exposed as CAP functions/actions and do not require a generated wrapper object, so no redeployment is needed there.

Summary

You've now built a Calculation View using the graphical editor in Business Application Studio and integrated it with CAP using the proxy entity pattern. The key takeaways are:

  • The graphical Calculation View editor is available in BAS only — it is not supported in a local VS Code setup
  • @cds.persistence.exists + @cds.persistence.calcview together tell CAP how to map an existing HANA artifact into the service layer without recreating it
  • Switching from namespace to context lets you keep namespaced CAP entities while still having a top-level V_INTERACTION entity whose name matches the HDI artifact exactly
  • A database redeployment is required after adding a new proxy entity to generate the SQL wrapper CAP needs

Questions for Discussion

  1. What is a Calculation View and how is it different from a SQL View?

    Answer

    A SQL View is a stored SELECT statement that is re-evaluated each time it is queried. It runs on the SQL engine and has no special awareness of column-store optimisations.

    A Calculation View is a HANA-native modeling artifact built from a graph of nodes (Projection, Join, Aggregation, Union). It is compiled into an optimised execution plan and runs directly on HANA's column store engine with in-memory parallelism. Calculation Views can model star-schema hierarchies, currency conversion, and other analytical patterns that would require complex, non-portable SQL in a plain view.

    The graphical editor in BAS (or HANA Studio) generates the underlying .hdbcalculationview XML definition — you are authoring a model, not writing SQL.

  2. Why did we change from a namespace to a context in the tutorial steps?

    Answer

    A namespace is file-scoped and prefixes every entity in the file. A context block applies the prefix only to entities declared inside it.

    The proxy entity for the Calculation View must have a name that matches the HDI object name exactly — V_INTERACTION, not app.interactions.V_INTERACTION. By placing the existing Headers and Items entities inside a context app.interactions { ... } block and declaring V_INTERACTION outside it, we keep the namespaced names for the CAP-managed entities while giving the proxy entity the exact bare name HANA expects.

  3. What is @cds.persistence.exists doing?

    Answer

    @cds.persistence.exists tells CAP that the underlying database object already exists and must not be created, modified, or dropped by the CAP deploy process. Without this annotation, CAP would attempt to generate a CREATE TABLE or CREATE VIEW statement for the entity during deployment, which would either fail (because the object already exists) or overwrite it.

    This annotation applies to any pre-existing database artifact you want to bring into a CAP service: tables from a legacy schema, SQL views created outside CAP, or — as here — HANA Calculation Views. The entity definition in CDS acts purely as a type description so the service layer knows the column names and types.

  4. What is @cds.persistence.calcview doing?

    Answer

    @cds.persistence.calcview tells CAP that the entity represents a HANA Calculation View specifically (not a plain table or SQL view). When this annotation is present, CAP generates a thin .hdbview SQL wrapper during cds build that sits between the OData service layer and the underlying Calculation View artifact. This wrapper is required because the OData-to-SQL translation CAP generates must address a SQL-accessible object, and the Calculation View is not directly addressable by generated SQL without the wrapper.

    Without @cds.persistence.calcview, CAP would still skip creation of the object (because @cds.persistence.exists is also set) but would not generate the wrapper, and runtime queries against the entity would fail.

  5. What would happen if you placed V_INTERACTION inside the context app.interactions { } block instead of outside it? What database object name would CAP look for?

    Answer

    If V_INTERACTION were inside the context block, CDS would prefix it with app.interactions., producing the fully-qualified name app.interactions.V_INTERACTION. CAP would then map that to the HANA object name APP_INTERACTIONS_V_INTERACTION — but no such object exists in the HDI container. The actual Calculation View artifact is named V_INTERACTION (no prefix).

    The result would be a runtime error when any OData request tried to query the entity: CAP would generate SQL targeting a view that does not exist. The error would only surface at runtime, not at build time, which makes it particularly easy to miss during development.

    This is exactly why the tutorial converts the namespace declaration to a context block: it lets you keep the app.interactions. prefix on the CAP-managed entities while placing V_INTERACTION at the top level where its name matches the HDI artifact exactly.

  6. The hana-cli inspectView command was used to generate the CDS entity definition for V_INTERACTION. What problem does this tool solve, and what would you have to do without it?

    Answer

    Without hana-cli inspectView, you would have to:

    1. Open the HANA Database Explorer and locate the Calculation View in your HDI container schema
    2. Inspect each column's name and HANA data type manually (e.g. NVARCHAR(255), DECIMAL(10,2), TIMESTAMP)
    3. Translate each HANA type to the corresponding CDS type (String(255), Decimal(10,2), Timestamp)
    4. Write the CDS entity definition by hand, getting the exact column names right (case-sensitive)

    hana-cli inspectView connects to your HDI container, queries the view's column metadata from the HANA system catalog, and generates a ready-to-paste CDS entity definition. This avoids transcription errors and is especially valuable for views with many columns or complex types. The broader hana-cli toolkit offers similar introspection commands for tables (inspectTable), procedures (inspectProc), and other HANA objects.

  7. You have now built a data model (Ex3), a UI (Ex4), and authentication (Ex5). Trace the full request path from the browser to SAP HANA Cloud for a GET that loads the list of Interactions_Header records. Which component handles each step?

    Answer 
    Browser
  │  GET /odata/v4/catalog/Interactions_Header
  ▼
Application Router (xs-app.json)
  │  Validates XSUAA session cookie
  │  Forwards request to srv-api with JWT in Authorization header
  ▼
CAP Service (interaction_srv.cds / interaction_srv.js)
  │  Validates JWT (checks signature, expiry, issuer)
  │  Checks @requires: 'authenticated-user' → passes
  │  Translates OData request to SQL SELECT
  ▼
HANA Cloud (HDI container)
  │  Executes SELECT against APP_INTERACTIONS_HEADERS
  │  Returns result set
  ▼
CAP Service
  │  Serialises rows to OData JSON format
  ▼
Application Router
  │  Passes response to browser unchanged
  ▼
Browser (SAPUI5 Fiori Elements)
  │  Receives OData JSON
  │  Renders list table columns defined by UI.LineItem annotation

    Each layer has a single, well-defined responsibility: the AppRouter owns authentication and routing, CAP owns authorization and data translation, HANA owns storage and query execution, and Fiori Elements owns rendering. No layer reaches into another's concern. This separation is what makes the stack maintainable and individually testable.

Further Study

Next

Continue to 👉 Exercise 7 - Create HANA Stored Procedure and Expose as CAP Service Function (SAP HANA Cloud)