Understanding a Graphical Calculation View in SAP HANA
Hi everyone and welcome to the most important tutorial in this series where we learn how to create a graphical calculation view. Calculation views form the primary core reporting data model which is developed in SAP HANA. Some of you who have already done some kind of reading or training on SAP HANA might be wondering why I haven’t taught attribute views or analytical views yet before coming to this yet as other tutorials available online do. The answer is quite simple – creating them is no longer part of the HANA development best practices. There are still reasons to know them as some projects might have them from the older versions. But I will cover them after I finish the important stuff. Any development object that exists but is no longer recommended to be used will be covered in some legacy tutorials later on. For now, let’s focus on the important stuff.
Graphical Calculation View – What is it?
As mentioned earlier, this is the SAP recommended data model and it should cover most of your development needs. It offers most functionalities that you might need to create a meaningful data model for your reports to consume but in the rarer cases where the requirements are beyond its capabilities, you can switch to SQL based solution of Table functions (a newer version of Scripted Calculation Views) but those are separate tutorials covered in the SQL Script section.
The beauty of a graphical calculation view lies in the fact that it offers powerful calculations which can be used by a developer with no coding experience whatsoever and hence, learning curve is quite gentle.
Let’s learn with an example
There are lots of things you can do with a graphical calculation view and so I decided to split this tutorial into parts. This part creates a simple graphical calculation view by joining two tables. If you are new to the concept of a database JOIN, click here to read our tutorial on it
Let’s take a real business scenario using sales document data as an example. Sales documents have two parts – A header and an item. If you are new to this concept, you can visualize this in the form of any bill that you have received till date. Such a bill has a header/top part that always remains constant providing probably the company name, address and some more header level information. Thereafter, there is an Item section which contains individual items that you have ordered. In SAP, header and item details are often stored in separate header tables and item tables. Our example will utilize the sales document header table – VBAK and the sales document item table VBAP. These are two of the most commonly used tables for analysis in actual projects.
As mentioned, these are standard SAP tables and will be part of SAP ECC installations. From our data provisioning tutorial, you already know that the most popular way of provisioning data from SAP source systems is via SLT replication which would provide these tables to a schema under your catalog folder. Generally this task to replicate tables is not part of a developer’s task for many reasons like data security and due to the fact that it is a sensitive process. Developers would request the table they need for a data model and it would be then provided by the project system administrators responsible for the SLT replications.
In our example, ECC_DATA is the schema marked by the arrow below that houses all our SAP ECC tables. In your project, it might be something else. Check with your project administrator to find the correct schema. You can choose to use any table from any other schema to practice this if you don’t have an ECC connected.
To create a new graphical calculation view, right click on your package and select New-> Calculation view from the context menu as shown.
The below window pops up asking for a technical name and a label. The Name is important and should be a meaningful technical name as per your project naming convention whereas the label is just a description. The name gets copied to the label automatically but you can change it to whatever you find to be correct. The “Type” drop-down remains on Graphical by default and can be changed to “Scripted” in which case you have to code the entire view in SQL Script. The data category is CUBE by default and should be selected when your data is expected to have measures which means that your data model is built to analyze transaction data. If there is no measure involved, select DIMENSION as the data category in which case you tell SAP HANA that the data model will be purely based on master data. If you do not understand the difference between master and transaction data, click here to revisit that tutorial.
We name our graphical calculation view SALES_VIEW and keep the other settings as they are as they suit our requirement.
So our requirement is to take two tables, VBAK and VBAP from the schema ECC_DATA and join them to create a graphical calculation view. The below screen opens up which is a easy drag and drop interface to build these graphical calculation views. Firstly, we need some placeholder to hold these tables. Such placeholder are called “Projections”. Drag and drop two of these into the screen as shown here.
These empty projections will look as shown below.
If you click on them and hover your mouse over them, you would get a green plus symbol as shown below. This helps you add objects to this Projection. A projection can be a placeholder for tables and other views as well.
On clicking the green plus symbol, you will get a window where you can find the table you will add to this Projection. On the search area, write the name of your table which in our case is VBAK. As seen below, the system provides the list of all database objects which have VBAK in their name. We are looking for the table VBAK under ECC_DATA schema which will be represented by VBAK (ECC_DATA) as shown below. When you find the table you are looking for, select it and press OK.
As seen below, the projection is no longer empty and houses the table VBAK . Click on the projection to open up the Details pane on the right side. Here you will see all fields associated with this table. In front of each field is a grey circle which allows you to select it for output from this projection node. This means that if you require the field VBELN, from this projection, you will need to click on that grey circle and it becomes orange which means that it is now selected. Now, select a few fields from this table. Note that if you are using a table from an SAP source, always select the field MANDT if it is available. The field MANDT indicates that the table is cross client. I have explained what “client” means in terms of SAP tables in a tutorial for a different section. You can check it out here.
As explained above, the fields I selected have an orange circle in front of them. That’s all we need to do here in projection 1. There are other options on the right most side of the screen like Filters and more but we will come to those in the following parts of this tutorial.
This tutorial continues in next page describing JOIN nodes in graphical calculation view.