Hi guys, in this article I want to share How to create Calculation View in SAP HANA Cloud Database. This is one of articles about SAP BTP with
Create and Working with SAP HANA CLOUD Database
Create development project in SAP Business Application Studio
After finish two articles above, next we will create Calculation View in SAP Business Application Studio and build it. Here we go
Step 1 : Create calculation view
- Log on to SAP Business Application Studio, go to SAP HANA Project which created before.
- Go to View => Find Command
- Choose SAP HANA Database Artifact.
- Fill information
- If Database connection unbind, we have to bind it again to connect database. Remember check HANA Database running or NOT.
Step 2 : Create join node
- Click on view which created in SRC folder and open it
- Click create join on left menu
- Click on Join Node
- Click Add Sign to choose data source
- In screen search, type table which add into join node. In this article, We choose table SBOOK and SCARR of scheme SFLIGHT for example. If do not see SFLIGHT TABLE, kindly ref this here to import data into SAP HANA Database.
Click Create Synonym
Click Finish. We have this screen
- Double click on Join node, we see on right pane two table. On tab Join Definition, we drag field CARRID of two table together
- On tab Mapping, we will choose field which display on output. Double click on field to select
Step 3 : Add Rank node (Optional)
If we want display top <number> data of result, we use RANK NODE. Drag Rank Note into after result of join node and configure it.
If we want to sort column, on this rank node, we configure it in section SORT COLUMN
Step 4 : Configure Aggregation to display column
Next, choose aggregation section and choose filed/(s) to display on view
Step 5 : Deploy Calculation View
- After configure done, we have to deploy this view
- In this step, we receive one error as
This issue is : No measures defined in a cube
When design Calculation View, we have to create at least one measures. For example Count(Column)…. If no, we will take issue above.
Step 4 ( Add more) : Create measures in CUBE
In table output have to exist filed which can calculated. So we must be created one if no field exist.
In this time, in Aggregation output Column we see that
Step 5 (Cont) : Deploy View
In this time, we deploy calculation view again and Issue fixed.
Right click on Aggregation and select Data Preview.
We have data
STEP 6 : GRANT ACCESS FOR USER TO CALCULATION VIEW
After create calculation view, it have one schema like this
Right click on view, choose Select Statement we have SQL like this.
Current, we will run this command on database with user DBADMIN we will receiver error
Next, we have to config to allow user DBADMIN access this view.
- Create hdbrole file in SAP Business Application Studio. Go to SAP HANA Artifact
- Configure for this hdbRole
- Deploy Artifact
- Go to Database Explorer
- Run script add user to hdbRole
GRANT "<SCHEMA_NAME>"."<hdbRoleName>" to <User>; Ex : GRANT "MYFIRSTHANADATABASE_HDI_DB_1"."publicaccessrole" to report
- Connect to database with user and call calculation view
In this article, I shared step by step how to create calculation view and create role to select this. Thanks for your reading and any question kindly leave your comment on below this.