I recently was involved in a BTP project where one of the use cases evolved around exposing a HANA Cloud calculation view via an OData API. The calculation view was developed in Business Application Studio (BAS) and deployed to an HDI container, which is the best practice when it comes to developing HANA native artifacts for HANA Cloud. To expose this calculation view via an API, the best approach is to add a Cloud Application Programming (CAP) service layer.

Multiple blogs and tutorials have been written about combining or consuming HANA Native artifacts in a CAP service layer, which you can refer to for practical information:

• Tutorial about consuming Calculation Views: Create Calculation View and Expose via CAP (SAP HANA Cloud)
• Blog about calling Stored Procedures: How to create an API to run a HANA Cloud Stored Procedure in Cloud Foundry

For our project, just exposing the calculation view itself was not enough: the calculation view is consuming data from an ECC system with multiple clients. This (ECC) client number is different for development, test or production deployments so the calculation view needs to know the correct client. Calculation views support this via the ‘Default Client’ feature (see SAP Help Documentation) and by specifying a column of the table to filter on. To keep the client value dynamic, the calculation view will rely on the session context.

Sample calculation view using the MANDT column for client-based filtering (CALCVIEWS_SAPDATA.hdbcalculationview)

Challenge

With calculation views configured to use the Session Client, the client value will be taken from the 'client' user parameter of the user querying the calculation view. In an HDI deployment, this would typically be a runtime user (RT user) which is automatically generated and as such doesn’t have a client user parameter set by default. We need to be able to set the client user parameter for the RT user based on the landscape the solution is deployed to. (See: How to set user parameters in HANA Cloud)

Solution

The solution described in this blog consists of 4 main steps:

1. HANA Cloud configuration:
   We need to enable HANA Cloud to give privileges to HDI object owners (#OO users) to set the client user parameter for their runtime users (RT users).
2. New HDI artifacts:
   We need to create a grant and a stored procedure to set the client user parameter for the active runtime user via the ALTER USER statement.
3. CAP service layer:
   The CAP service which will consume and expose the calculation view as an OData API needs to call the stored procedure with the target client value configured during deployment.
4. Deploy to BTP landscape:
   Build generically, but deploy the project to BTP with a specific client value.

Step 1: HANA Cloud configuration

The HANA Cloud database has to be configured to allow the granting of user altering privileges for configuring the runtime users. Runtime users are part of the HDISHARED User Group. In order to limit the use of the DBADMIN user for any operational tasks, we create a separate user (UG_GRANTOR_USER) with the privilege to assign the user altering privileges to HDI object owner users. This user altering privilege will be assigned via a new role: UG_GRANTOR_ROLE. Any user receiving this role will be able to alter HDI runtime users.

-- Run these statements with the DBADMIN user

-- Allow DBADMIN to grant Usergroup Operater privileges to a user:
CALL "SYSTEM".GRANT_USERGROUP_OPERATOR('BROKER_UG_HDISHARED');


-- Create a role which will contain the privilege to alter users:
CREATE ROLE UG_GRANTOR_ROLE NO GRANT TO CREATOR;
GRANT USERGROUP OPERATOR ON USERGROUP BROKER_UG_HDISHARED TO UG_GRANTOR_ROLE WITH GRANT OPTION;


-- Create user UG_GRANTOR_USER who will be allowed to grant the new role to other users:
CREATE USER UG_GRANTOR_USER PASSWORD "YourSecretPassword" SET USERGROUP DEFAULT;
ALTER USER UG_GRANTOR_USER disable PASSWORD lifetime;


-- Grant new role to UG_GRANTOR_USER with option to grant to others (which will be #OO users):
GRANT UG_GRANTOR_ROLE TO UG_GRANTOR_USER WITH ADMIN option;

Note: To remove this configuration again, you can simply revoke the Usergroup Operator privileges which will cascade down and disable any ALTER USER statements going forward.

-- Disable the granting and revoke privileges of the UG_GRANTOR_USER and all #OO users:
CALL "SYSTEM".REVOKE_USERGROUP_OPERATOR('BROKER_UG_HDISHARED');

-- Delete the role/user (clean-up)
DROP ROLE UG_GRANTOR_ROLE;
DROP USER UG_GRANTOR_USER;

Step 2. New HDI Artifacts

The HDI container contains your calculation view, but we need to add a few elements to make sure the owner of the HDI (#OO user) is able to set the correct client for the runtime user (RT user).

In the HDI container, we need the following new artifacts:

• HDI_GRANTS_Client.hdbgrants: A grants file to assign our role to the HDI object owner.
• SET_CLIENT.hdbprocedure: A stored procedure to look up the current session user and set its client value.
• LOG_TABLE.hdbtable (optional): A logging table to record changes in client value.

Grants

The grants file will assign the UG_GRANTOR_ROLE role to the HDI object owner (#OO user), as well as provide access to the SYS.DUMMY table which will be used in the stored procedure to determine the current session user.

HDI_GRANTS_Client.hdbgrants:

{
  "HDI_GRANTS_Client": {
    "object_owner": {
      "object_privileges": [
        {
          "schema": "SYS",
          "name": "DUMMY",
          "privileges": [ "SELECT" ]
        }
      ],
      "roles": [
        {
          "roles": [ "UG_GRANTOR_ROLE" ]
        }
      ]
    }
  }
}

Stored Procedure

The stored procedure is configured to execute statements using the DEFINER privileges, which is the HDI object owner (#OO user). It will retrieve the current SESSION_USER (the RT user) and set its 'CLIENT' user parameter. An entry is written to the log table as well for monitoring purposes (optional).

SET_CLIENT.hdbprocedure:

PROCEDURE "SET_CLIENT" (IN client NVARCHAR(3)) 
  LANGUAGE SQLSCRIPT 
  SQL SECURITY DEFINER 
AS BEGIN

  DECLARE result NVARCHAR(1000);
  DECLARE su NVARCHAR(255);

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SELECT ::SQL_ERROR_MESSAGE INTO result FROM SYS_DUMMY;
    INSERT INTO LOG_TABLE ("CLIENT", "RESULT") VALUES (:client, :result);
    COMMIT;
    RESIGNAL;
  END;
  
  SELECT session_user INTO su FROM SYS_DUMMY;
  EXEC 'ALTER USER ' || :su || ' SET PARAMETER "CLIENT"=''' || :client || '''';
  INSERT INTO LOG_TABLE ("CLIENT", "RESULT") VALUES (:client, 'Success');

END;

The stored procedure relies on the SYS.DUMMY table from the HANA database, which is accessed via the SYS_DUMMY synonym.

SYS_DUMMY.hdbsynonym:

{
  "SYS_DUMMY": {
    "target": {
      "object": "DUMMY",
      "schema": "SYS"
    }
  }
}

Logging Table

This is an optional table which will have a record inserted for every change in client value, just for logging/monitoring purposes.

LOG_TABLE.hdbtable:

COLUMN TABLE LOG_TABLE (
    TIME TIMESTAMP default CURRENT_TIMESTAMP,
    EXECUTER NVARCHAR(255) default CURRENT_USER,
    SUBJECT NVARCHAR(255) default SESSION_USER,
    CLIENT NVARCHAR(3),
    RESULT NVARCHAR(1000)
);

Step 3. CAP Consumption layer

The calculation view will be consumed by a CAP service layer to expose it via an OData API endpoint. To enable this, we need to:

• Schema and Service: Enable API access to the calculation view.
• Server: Call the stored procedure during application startup.
• MTA Deployment Descriptor: Create an environment variable to hold the target client value and a user provided service for the grants.
• MTA Deployment Extension Descriptor: The final configuration, used during deployment, specifying the client value.

Schema and Service

This is the standard approach to exposing a calculation view in CAP, and the above-mentioned tutorial goes into more depth on this topic. For now, it’s important to understand that we need a proxy entity in the schema, and a service pointing to the already-existing calculation view.

To generate the proxy entity, you can always use the inspectView HANA CLI command to retrieve the data model.

Schema.cds:

namespace SAP;

@cds.persistence.exists
@cds.persistence.calcview
entity DATA {
  key MANDT : String(3);
  key ID    : Integer;
      VALUE : Integer;
};

Service.cds:

using { SAP } from '../db/schema';

service CALCVIEWS {
  @cds.persistence.exists
  entity SAPDATA as projection on SAP.DATA;
};

With this configuration, the CAP service understands there is an existing view named “CALCVIEWS_SAPDATA”, providing an output corresponding to the schema entity.

Server

The CAP framework emits specific events during its application startup routine, which we can listen and react to (See Capire Bootstrapping Servers documentation). Once the database has been connected (together with some other things) it emits the served event, so that’s the signal for us that we can start to execute queries. It’s important to name this file “server.js” so it is picked up automatically.

This code snippet will verify that the application is connected to HANA Cloud, retrieve the current RT user from the bindings, and get the configured client value from an environment variable (defined later). With this information, it executes the stored procedure to store this client value for the user.

Server.js:

const cds = require('@sap/cds');

cds.once('served', () => {
  const client = process.env?.sap_client || null,
    user = cds.db?.options?.credentials?.user || null,
    dbkind = cds.db?.kind || null;
  if (user && client && dbkind == 'hana') {
    cds.db.run(`CALL SET_CLIENT(client => '${client}')`)
      .then(() => console.log(`Set client ${client} for user ${user} successfully.`))
      .catch(error => console.error(`ERROR: Could not set client ${client} for user ${user}. ${error}`));
  } else {
    console.log(`Project is not configured to set client [user: ${user}, client: ${client}, db: ${dbkind}].`);
  }
});

MTA Deployment Descriptor

In the Multi-Target Application (MTA) Deployment Descriptor (mta.yaml) we specify an empty placeholder for a sap_client environment variable for the “srv” application. This variable will be filled with the correct value from the MTA Deployment Extension Descriptor (.mtaext file).

We also need to define the User Provided Service that contains the credentials of the UG_GRANTOR_USER user which is used in the HDI_GRANTS_Client.hdbgrants file. This is relevant to the “db-deployer” application which will deploy the HDI artifacts.

mta.yaml (truncated):

modules:
  - name: calcview_client-srv
    type: nodejs
    path: gen/srv
    deployed-after: [ calcview_client-db-deployer ] # otherwise the app could possibly start before the db is ready
    properties:
      sap_client: '' # empty value when no mta-ext is used
    parameters:
      buildpack: nodejs_buildpack
    ...

  - name: calcview_client-db-deployer
    type: hdb
    path: gen/db
    parameters:
      buildpack: nodejs_buildpack
    requires:
      - name: calcview_client-db
      - name: HDI_GRANTS_Client
  ...

resources
  - name: HDI_GRANTS_Client
    type: org.cloudfoundry.user-provided-service
    parameters:
      path: ./HDI_GRANTS_Client.json
  ...

The user provided service refers to the below configuration file containing the UG_GRANTOR_USER credentials.

HDI_GRANTS_Client.json:

{
  "user": "UG_GRANTOR_USER",
  "password": "YourSecretPassword",
  "tags": [ "password" ]
}

With this configuration, the entire project can be compiled/built generically, leaving the client value as a parameter set during the deployment phase.

MTA Deployment Extension Descriptor

The extension descriptor is a simple file which defines the value of the environment variable. You can create as many extension files as you need different configurations.

As an example, the below extension defines value 200 for the sap_client.

mta_client200.mtaext:

_schema-version: '3.1' 
ID: calcview_client_client200
extends: calcview_client

modules: 
  - name: calcview_client-srv
    properties:
      sap_client: '200'

Step 4: Deploy to BTP Landscape

The first step is to build the project from source code and package it in a deployable artifact (MTAR), using the below CLI commands. This can be done independently of choosing a client value. This deployable artifact can be used in your CI-CD pipeline.

# create the deployable artifact
npm install
mbt build

The output will be a file like ./mta_archives/calcview_client_1.0.0.mtar

Once built, your project can now be deployed to different BTP landscapes (developmen, test, production) by specifying the corresponding extension file via the -e flag:

# deploy for client 100
cf deploy ./mta_archives/calcview_client_1.0.0.mtar -e mta_client100.mtaext

# deploy for client 200
cf deploy ./mta_archives/calcview_client_1.0.0.mtar -e mta_client200.mtaext

# deploy for client 300
cf deploy ./mta_archives/calcview_client_1.0.0.mtar -e mta_client300.mtaext

Outcome

Deployed using the mta_client200.mtaext file, you will see the following result:

• The environment variable of the “srv” application
• The entry written in the “srv” application startup log
• The entry in the logging table
• The output of the calculation view filtered for client 200

Environment Variable set from extension file

Application startup log showing a successful stored procedure call

Entry in the logging table showing the #OO and RT users

Query of the calculation view showing data filtered for client 200

Summary

This blog explored the scenario where a calculation view required a client value to respond correctly, where this client value has to remain dynamic up until the project deployment step. Staying within the HDI and CAP frameworks, we looked at an approach of using the HDI object owner changing the user parameter of the runtime user based on a value given by the CAP layer.

Please share your thoughts about this approach in the comments below. There are some potential alternative approaches (e.g. manipulating the HANA Cloud connection string from the database binding during CAP startup, or granting privileges via the OO_DEFAULTS or RT_DEFAULTS roles) so I’d love to continue the conversation below.

Git Repository: A sample project containing all artifacts can be found here: SAP BTP Global CoE Samples.