Graphical Calculation Views

A calculation view is used to define more advanced slices on the data in SAP HANA Cloud database. Calculation views can be simple and mirror the functionality found in both attribute views and analytic views. However, they are typically used when the business use case requires advanced logic that is not covered in the previous types of information views.

For example, calculation views can have layers of calculation logic, can include measures sourced from multiple source tables, can include advanced SQL logic, and so on. The data foundation of the calculation view can include any combination of tables, column views, attribute views and analytic views. Allows the creation of:

Joins
Unions
Projections
Aggregation levels on the sources

It’s possible to model the following elements within a calculation view:

Attributes
Measures
Calculated measures
Counters
Hierarchies (created outside of the attribute view)
Variables
Input parameters

Calculation views can include measures and be used for multi-dimensional reporting or can contain no measures and used for list-type reporting. Calculation views can either be created using a graphical editor or using a SQL Console. These various options provide maximum flexibility for the most complex and comprehensive business requirements.

Try it out!

Create DB Connection

To access the objects in the user’s local schema in HANA Cloud, a database connection must be created.

In the Explorer panel, expand SAP HANA PROJECTS. It is possible to drag the project up in the explorer panel if necessary.

Hover the mouse pointer over Database Connections until the Add database connection symbol is visible.

Select the + symbol to open the Add Database Connection wizard.

In the Select connection type box, choose the drop-down arrow and select Create user-provided service instance.

For the Service Instance Name, enter USER_CONN_.

In the Connect to database box, select Provide database information.

Enter the following details for the connection:
- host name:
- host port: 443
- user name:
- password: as provided via registration

Leave the rest of the fields as default settings (schema name should be automatically populated with the local user schema) and select Add.

The DB connection is now visible in the Explorer Panel underneath Database Connections.

Grant access to local user schema

After configuring the connection to the user schema in HANA Cloud, it is important to grant privileges to the object owner and application users so they can access the data in the database. A convenient option to assign a set of privileges to users directly from a project is with an .hdbgrants file.

In the menu bar, choose View and then select Command Palette.

Start typing, or select SAP HANA: Create SAP HANA Database Artifact.

In the artifact creation wizard, choose Grants (hdbgrants) as the artifact type.

Enter SchemaGrants as the artifact name and select Create.

SchemaGrants.hdbgrants will appear under Workspace -> src. This file provides the privileges to access objects in a schema. The graphical UI will appear showing the current privileges.

Select the toggle switch in the top right hand corner of the screen to switch from the UI to the text editor:

The exact syntax of the file will be displayed.

In the text editor, replace the contents of the file with the text below:

Code Snippet

12345678910111213141516171819202122
{
    "USER_CONN_{placeholder|userid}":
    {
    "object_owner":
    {
    "schema_privileges":[
    {
    "schema":"{placeholder|userid}",
    "type": "TABLE",
    "privileges_with_grant_option":["SELECT","EXECUTE","UPDATE","INSERT","DELETE"]
    }]
    },
    "application_user": {
    "schema_privileges":[
    {
    "schema":"{placeholder|userid}",
    "type": "TABLE",
    "privileges_with_grant_option":["SELECT","EXECUTE","UPDATE","INSERT","DELETE"]
    }]
    }
    }
  }

Select the deploy button to deploy the SchemaGrants.hdbgrants file. Deployment should complete in a few seconds and deployment related messages can be observed in the terminal window.

As the schemagrants.hdbgrants file has been deployed successfully, the application user and object owner now have relevant privileges to create synonyms of tables from the local schema in HANA Cloud.

Create a Database Synonym

Synonyms are aliases for database objects such as tables, views, schemas etc. but are not real database objects themselves. This exercise creates database synonyms to link Business Application Studio database artifacts to the user schema in SAP HANA Cloud.

Open the Command Palette and choose SAP HANA: Create SAP HANA Database Artifact.

Select Synonym (hdbsynonym) as artifact type in the drop down.

Specify the name of the new artifact as _Synonym and choose Create.

The synonym file is created and a configuration screen appears.

Change the name to CV_CUSTOMERS and select the dots to associate it with an object in the user schema.

Type GX and the list of GX_* tables should appear.
Select GX_CUSTOMERS and choose Finish.

The configuration screen now shows that the object GX_CUSTOMERS is associated with the synonym CV_CUSTOMERS.

Select Click to add to create another synonym.

Change the name of the synonym to CV_SALES and then select the three dots to associate the object.

Start typing GX and in the list of tables that appear, select GX_SALES and choose Finish.

The configuration screen now shows two synonyms have been created for two different tables in the local user schema. Select the Deploy icon to deploy these in your project.

Once the deployment completes successfully (check the terminal console for any issues), the synonyms can now be used to create a Calculation View.

Create a Calculation View

In the menu bar at the top, open View -> Command Palette and select Create SAP HANA Database Artifact as before.

In the Choose Artifact dropdown, select Calculation View (hdbcalculationview).

Enter CV_ as the name and select Create.

The Calculation View editor panel appears.

Select the two arrows in the top left corner of the panel to expand the list of operators.

Select the Join operator, then move the cursor (not click and drag) underneath the Aggregation node, and click to drop the Join operator on the panel.

Add the Synonyms created earlier as data source objects in the Join. Choose the Add data source icon to add a data source.

Type CV into the search box, then select the two synonyms created earlier - CV_CUSTOMER and CV_SALES - then select Finish.

The two synonyms are added to the Join node as data sources. Select the properties icon to see more options for the Join.

The Join Definition appears where properties of the join such as join columns, join type and cardinality can be configured.

Select the CUSTOMER_ID column from CV_CUSTOMERS and drag it across to join with the CUSTOMER_ID column from CV_SALES.

In the Join Properties panel, select Left Outer as Join Type, and Cardinality as n…m

Choose Mapping to see the list of source columns and to select which ones to add to the output.

Right-click on the CV_CUSTOMERS data source and select Add To Output.

Check the list of Output Columns to verify all columns from the CV_CUSTOMERS source have been mapped to the output list.

In the Data Source list on the left, scroll down to see the list of columns in the CV_SALES source.
Holding down the CTRL key, select the columns QUANTITY, PRICE and PRODUCT_ID from CV_SALES, then right-click and choose Add To Output.

Verify that the columns have been mapped to output list.

Connect the Join node to the aggregation node above it. Select the Connect icon of the join node and drag it to the aggregation node.

Select the Aggregation node to open the Mapping panel and display the columns from the Join node.

Right-click on Join_1 and select Add To Output.

Verify that the list of columns are now added to the output.

Select the Semantics node and verify that all columns have been propagated to the output of the Calculation View.

Select the checkbox next to the QUANTITY attribute, and choose the Mark as Measure icon along the top to change it to a measure.

Choose Yes on the warning message that pops up.

QUANTITY is now changed to a measure with COUNT as aggregation type.

The Calculation View is now configured, but must be deployed to the HDI container before it can be used.

Choose the Deploy icon to deploy the calculation view.

After successful deployment, select the highlighted icon beside the HANA Project name to open the HDI container

Database Explorer will open in another tab, with the original HANA Cloud database instance and an additional one which starts with SharedDevKey@…. This is the HDI container with the objects created in Business Application Studio.

In the HDI instance, select Column Views to see the deployed Calculation View, right-click on the CV_ view, and select Open Data.

The Analysis tab will open showing the list of attributes and measures in the view. Select the CUSTOMER_REGIONNAME attribute and drag it to the Label Axis, then select and drag the QUANTITY measure to the Value Axis.

In the list of display options along the top, select Vertical Bar chart type to display the values.

The result is a vertical bar chart showing how many products have been sold to customers in the different regions.

It’s possible to play around with the different attributes and values to derive further information from a dataset. More measures and columns can be added in Business Application Studio and then redeployed to update the Calculation View in Database Explorer.

In this example, it seems that Wien is an outlier given the quantity of sales in that region. It’s possible to apply a filter to remove this and aid with analysis of the other regions.

Select the Filter drop down to apply a filter to the chart, then select CUSTOMER_REGIONNAME as the column to filter on.

In the expression editor, select Not Equal as operator.

As filter value, select Wien from the list of input options, and choose OK.

The graph is now updated with the new filter applied:

This concludes the lesson on Calculation Views. Feel free to play around with the different attributes and measures to modify the resulting charts.

Further information

Go to Course