SAP HANA Scripted Calculation View – Part 1
At this point starts the most important parts of the HANA SQL Tutorial series. The SAP HANA scripted calculation view is used to code the logic required in SQL. Usually, this is done whenever the logic was too complicated to be achieved by the nodes available in Graphical views or if the SQL view would be a better performant in terms of time taken in execution.
Some projects however, create all data models with SAP HANA SQL scripts. This allows flexibility and often simpler data models. However, starting from SAP HANA Service Pack 11 (SP11), SAP recommends that you create graphical views for your requirements and only choose scripted data models when a graphical one can’t handle the requirement.
SAP HANA Scripted Calculation View would be the most common SQL based data model you would find in existing projects worldwide. But, with SAP HANA SP11 which is still quite new, SAP recommends to now use Table Functions whenever Graphical views aren’t enough for your modeling needs. Therefore, the Scripted Calculation view is also on its way to become something of the past.
However, this move to table functions might take a while since most consultants aren’t aware of table functions or even the updates from SAP on this matter. Also, changing something that is working fine on a system isn’t recommended unless there is a risk foreseen. Hence, SAP HANA scripted calculation view is a really important topic to be properly understood.
Creating our first SAP HANA Scripted Calculation view
In our SAP HANA Tutorial section, we had created a SAP HANA graphical calculation view called SALES_VIEW as shown below. This view combined sales order header table VBAK and Sales order item table VBAP with each other. Take a look at the design before we proceed further.
Let’s re-create the same logic in a SAP HANA Scripted calculation view. To do this, right click on your package to select New Calculation view.
A familiar window would open up asking for some information.
Fill in some name. We have used SALES_SCRIPT as both the “Name” and “Label” of the SAP HANA Scripted Calculation View. The default type of Calculation view is a graphical one and hence, you would have to use the drop down as shown below to switch the view type to SQL Script.
After selecting the Calculation view type, the overall settings should look as below. Once done, press the Finish button.
The below window opens up. As you can see, the SAP HANA Scripted Calculation view is a two node structure. The bottom node is where you write the HANA SQL Script and the semantics node, as always is the output node. There cannot be any additional nodes in this data flow. All the necessary logic must be written into the Script_View node.
Click on the Script_View node to bring up the Details Pane. This is where you would proceed to write the HANA SQL Script code for your logic that needs to be implemented. In addition to this, the Output pane on the right provides the list of output columns as well as input parameters associated with this view.
Firstly, we need to define the output fields for this view along with their data types and length. Click on the Green plus symbol to add the output fields.
This will open the below window. Here, you can manually type in the name of the output field and the other details or if the fields come from an existing table, you can import the name and datatypes by clicking on the blue button marked below.
1
This brings up the find window where you need to specify the name of the object you wish to search for. Firstly, let’s search for the VBAK table. Write VBAK in the search bar and wait for the search to finish. As seen below, there are two VBAK tables in different schemas. The one we use in our tutorials is ECC_DATA. Pick this one and click on Next.
This now brings up the below window where all the fields of table VBAK are listed on the left side and the right side represents the fields added to our target (Scripted view node).
Select the fields you need to move to the target and press the “Add” button highlighted below. Alternatively, you can do them one by one too to test your patience. We select a few fields from this table as shown below.
Once the Add button is pressed, the selected fields move to the right side as illustrated. Press Finish.
This brings us back to the below window which now says “Edit Columns” instead of “Create columns” when we started. As seen below, the field names, datatypes and lengths of these fields have been successfully copied from the VBAK table to the node output.
Please note that the view currently has no relation or link to the table VBAK. This was just a quick method to copy the field information. You could have chosen to do it manually as well but this is a safer method since you copy this information directly from the table which you will use later on in your code. This ensures that there won’t be any datatype mismatches due to human error.
As per the original requirement, we require to JOIN tables VBAK and VBAP. But for the sake of simplicity, let’s start out by creating a view which only displays the output of table VBAK for the fields we selected.
Press OK to confirm these fields.
Once done, you would now notice the field names are displayed under the “Columns” list.
Now, it’s time to write the code for this logic.
The code needs to be written between the BEGIN and END block shown below.
The output of the logic must have the same names and the same order as the fields we added to the “Columns” folder. This final output must feed into the var_out table variable as seen in the code.
What this means is that the structure of this var_out is what we defined by defining the “Columns” list a while back.
To start, remove the two dots in front of the var_out (marked by the green arrow). They are of no use.
Once you have the code looking as below, we can start adding the logic.
1
In this case, a simple SELECT statement which we have already learnt is added. We select the fields we need from table VBAK in schema ECC_DATA whilst maintaining the same order of fields as in the output we defined for this node.
Make sure you add a semicolon at the end of the statement. This may not sound like much of an advice but trust me, beginners make smaller mistakes like these more often than you might imagine.
Activate the view and do a data preview. As seen below, the view output is as per our expectations.
Congratulations! You just made your first SAP HANA Scripted Calculation View.
In the next part of this tutorial, we will work on introducing the VBAP table using SQL script JOIN in our Calculation view.
If you liked the tutorial, please share it on social media using the share buttons below and make sure you subscribe to our newsletter to get the latest updates when new tutorials are added.
<<Previous Tutorial Next Page>>