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

With the SAP Cloud Application Programming Model and its implementation of Core Data Services (CDS), we don’t directly import a Calculation View or other native DB artifact. This import, however, is important if you want to expose these artifacts via OData V4 services, since that requires a CDS entity or view.

But CDS does have an annotation called @cds.persistence.exists. This annotation allows you to re-define an existing DB object and CDS won’t attempt to create or alter it. It will just assume it already exists in the matching state.

This feature does require you to completely redefine the DB artifact with exactly the same name, columns and column names.

In this exercise, lets see how we can create a calculation view and other artifacts and expose them to CDS.

For more details on this topic, including advanced features such as parameters and quoted names, please refer to the online documentation here: https://cap.cloud.sap/docs/advanced/hana#create-sap-hana-native-object

Calculation Views and other HANA native artifacts allow you to leverage HANA specific features and optimizations that might not otherwise be available at the abstraction layers within the SAP Cloud Application Programming Model. Calculation Views are especially good at aggregation and filtering of large datasets. In this exercise will create a simple join Calculation View based upon or small data set and data model. This is done so we focus on the mechanics of combining HANA native with CAP without needing the typical large data set where the technical advantages of Calculation Views become more apparent.

1. Create a new Calculation View via View > Command Pallette and then SAP HANA: Create SAP HANA Database Artifact command pallet entry.

   

2. Create a calculation view called V_INTERACTION of Data Category DIMENSION and Dimension Type of STANDARD. Press Create

   

3. The new artifact is created in the /db/src folder. This way you can have a single HANA database model that contains both HANA native content and CAP generated content.

   

4. Click on the V_INTERACTION.hdbcalculationview to load the graphical calculation view editor.

   

1. Drop a join node into the modeling space

   

2. Use the

   

   sign to add tables to the node.

   

3. Type in HEADER and then select the table you created earlier via CDS called APP_INTERACTIONS_HEADER and press Finish.

   

4. Repeat the process to add the APP_INTERACTIONS_ITEMS table to the same join node. You should see both artifacts in the join node.

   

5. Double-click on the join node. A panel will open on the right.

   

6. Drag and drop the ID field to the INTERACTION_ID field.

   

7. Set the cardinality to 1..n

   

8. In the Mapping tab, add all the columns except ID* Columns as output columns.

   

9. Connect the join node with the Projection node using the

   

   

   

10. Click on the Projection node and double-click on the join parent to add all the columns to the output

    

11. From the SAP HANA Projects view, press the Deploy button

    

12. Check the deployment log to make sure everything was successfully created in the database.

    

13. Open the HDI Container in the Database Explorer

    

14. Under Column Views you will find your Calculation View. Choose Open Data

    

15. Go to the Raw Data and you should see the header and item data joined together.

    

We now want to expose our Calculation View to the Cloud Application Programming model by creating a “proxy” entity for the view in the CDS data model.

1. Return to the Business Application Studio and open interactions.cds.

   

2. We need our proxy entity to be created without the namespace in our current interactions.cds. Therefore comment out the namespace line and add all the existing content except the using ... line in a new context for app.interactions.

   

3. We need to add a matching entity definition for the Calculation View. This means redefining all the column names and data types / lengths. Doing so manually would be error prone, but the hana-cli has a utility that will help. Open a terminal and change to the db folder with the command cd db. Now issue the command:

   Shell Copy

   hana-cli inspectView -v V_INTERACTION -o cds

   

   With this command you are looking up the definition of the view but asking for the output (-o) in the CDS format.

4. Copy this block from the terminal and paste it into the interactions.cds file at the end outside the context block.

   

5. CDS does have an annotation called @cds.persistence.exists. This annotation allows you to re-define an existing DB object and CDS won’t attempt to create or alter it. It will just assume it already exists in the matching state.There is also the annotation @cds.persistence.calcview. This will further tell the Cloud Application Programming Model that this target entity is also a Calculation View.

6. Now open the interactions_srv.cds file from the /srv folder. Add this new Calculation View based entity to the CAP service as read-only.

   

7. From the terminal return to the root of the project and issue the command: cds build --production

   Shell Copy

   cds build --production

   

8. Although we didn’t add any new database artifacts to the project, the addition of an entity to the service layer causes new views to be generated within SAP HANA. Therefore we need to deploy to the database using the SAP HANA Projects view before we can test.

   

9. From the console in the project root hopefully you still have the cds watch ... running. Otherwise start it again with cds watch --profile hybrid to start the CAP service layer for testing. If you have performed the tutorial SAP HANA Cloud, Add User Authentication to Your Application, remember you must also run the application router to test your service with authentication.

10. You can test your view via the service layer by adding /odata/v4/catalog/V_Interaction to the path.

    

Congratulations! You have now successfully combined HANA native artifacts with the SAP Cloud Application Programming Model and learned the modern HANA way to expose Calculation Views via OData.