SAP HANA Calculation View API – Introduction

Overview

To execute analytical workload on SAP HANA calculation views can be used. They are most of the time graphically modeled in the SAP HANA Studio (for repository-based calculation views in SAP HAN A on-premise) or the SAP Web IDE for SAP HANA (for HANA Deployment Infrastructure [HDI] based ones in SAP HANA on-premise and in SAP HANA Cloud ).

These tools are great for graphical modelling but have limitations when it comes to automation and dynamic changes. For example, to change a formula of a calculated column in a view, a user first needs to open the modelling tool and change the formula manually. With this API, a change can also be done programmatically and therefore dynamically.  Doing changes programmatically is especially helpful when the change should be applied to multiple views  at once.

Requirements and Installation

To use this API the SAP HANA Client 2.5 or higher is required. In the installed folder you will find another folder called “calcviewapi” which contains four jar files:

• HANACalculationViewAPI-Cloud-XXXX.jar
• HANACalculationViewAPI-Cloud-XXXX-javadoc.jar
• HANACalculationViewAPI-OnPrem-XXXX.jar
• HANACalculationViewAPI-OnPrem-XXXX-javadoc.jar

The API has two editions: one for SAP HANA Cloud and one for SAP HANA on-prem. In SAP HANA Cloud some features are not supported anymore (see SAP Note 2868742), therefore two editions exist.

To use the API, at least (JRE or JDK) is required. As the API has no other dependencies, you can just add the appropriate jar file into your class/build/module path. If your IDE supports Javadoc, you can also link the related Javadoc jar.

The API itself requires no database connection and is therefore independent from HANA. The generated calculation view itself depends on the database version and the API edition:

• Cloud: The generated Calculation View can be deployed on the latest version offered by SAP HANA Cloud
• On-Premise: Please refer to SAP Note 2869216

Scope and Limitations

Calculation views are stored during design time in an XML format, so this API generates and consumes this XML. Only the HDI-based calculation view XML is supported, not the repository-based one.

Additionally, the API is only capable to consume, modify and generate the calculation view XML. Any HANA side actions can be done with the HDI Administration SQL API or the HDI Deployment SQL API.

Use Cases

The API for example can be used for the following use cases:

• Change or create calculation views dynamically
• Mass-changes of calculation views
• Mass-creation of calculation views (can be based on a “template” calculation view)

Especially, the API can be used when no UI access is possible. As an example,  the calculation view definition needs to be changed based on the user input, but this should not be done by the end-user in a UI.

Code Examples

To create a new calculation view from scratch, you can start with the following code. A full example can be found here.

ScenarioRoot root = new ScenarioRoot();
//add all needed data
String xml = root.toXMLWithValidation();

The API also offers to load an existing XML for modification or analysis:

ScenarioRoot root = new ScenarioRoot();
root.loadWithXSDValidation(<xml>);

With HDI properly setup, to write and make (deploy) the calculation view to SAP HANA you can use the following SQL:

CREATE LOCAL TEMPORARY COLUMN TABLE #FILESFOLDERS_WRITE LIKE _SYS_DI.TT_FILESFOLDERS_CONTENT;
CREATE LOCAL TEMPORARY COLUMN TABLE #FILESFOLDERS_PARAMETERS LIKE _SYS_DI.TT_FILESFOLDERS_PARAMETERS;
CREATE LOCAL TEMPORARY COLUMN TABLE #FILESFOLDERS LIKE _SYS_DI.TT_FILESFOLDERS;
CREATE LOCAL TEMPORARY COLUMN TABLE #FILESFOLDERS_MAKE LIKE _SYS_DI.TT_FILESFOLDERS;
INSERT INTO #FILESFOLDERS_WRITE (PATH, CONTENT) VALUES ('<name>.hdbcalculationview','<xml>');

CALL <container>#DI.WRITE(#FILESFOLDERS_WRITE, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);

INSERT INTO #FILESFOLDERS_MAKE (PATH) VALUES ('<name>.hdbcalculationview');

CALL <container>#DI.MAKE(#FILESFOLDERS_MAKE, #FILESFOLDERS, #FILESFOLDERS_PARAMETERS, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);

DROP TABLE #FILESFOLDERS_WRITE;
DROP TABLE #FILESFOLDERS_PARAMETERS;
DROP TABLE #FILESFOLDERS;
DROP TABLE #FILESFOLDERS_MAKE;

You can learn more about the HDI SQL API in this blog.

References

SAP HANA Calculation View API Documentation

SAP HANA Client Installation and Update Guide