Analyzing the SAP HANA Calculation View Execution Part – 2
This is the continuation of Part-1 of the SAP HANA Calculation view execution analysis tutorial. If you have reached this tutorial and haven’t read Part-1 – click here to go back.
Note: If you have reached this page instead to learn how to create a SAP HANA Calculation view – Click here for the Graphical view tutorial and here for the Scripted View tutorial.
SAP HANA Calculation View Execution Analysis Continued…
Now, the next part highlighted below shows how measures master data vs transaction data are handled by the system. Whenever a field is a measure, it has a default aggregation as SUM. That means that rows would get added up when aggregated. In the semantic node of the view, you can change this to COUNT to count the number of values, or to MAX or MIN to get the maximum or minimum of values instead of having the standard SUM.
As we already learnt in a prior tutorial GROUP BY, an aggregation function requires a GROUP BY statement at the end. Thus, every attribute field would have to be specified in the group by as we used a SUM function.
The next part looks fairly obvious but is interesting nevertheless.
We know that the FROM keyword specifies where the data is being picked up from. In HANA, a table or view cannot be called by just its name. It always has its schema name in front of it as you must have noticed by now. If you look carefully, you would realize that this view resides under the _SYS_BIC schema. This is a standard SAP HANA system schema where all the views that are created are stored.
The package 0TEACHMEHANA under which this view was created is also part of the name.
The name of the view would then essentially be _SYS_BIC.<package_path>/<view_technical_name>.
The below image shows where this view is saved by the system. It’s under the _SYS_BIC schema in the folder “Column Views”.
1
Coming back to the code, the last part highlighted below is to pass the input parameter value to this view while calling it. We had marked the input parameter P_DOC_TYPE as mandatory and hence, it has to be passed to the view while running it to avoid an error.
To do this, use the below syntax:
(‘PLACEHOLDER’ = (‘$$<Input_parameter_name>$$’), <Value_to_be_passed> )
If you proceed to press F8 or the execute button, the query would get executed and would provide the exact same results as the data preview button on the view.
I hope you enjoyed this in-depth analysis of a calculation view. You can perform the same for a graphical view as well since all of your views execute as SQL scripts at the end anyways.
Make sure to share this content on social media using the share buttons below to show your support to this website and to keep it alive.
Nice and informative
Thanks for reading. Please share with others as well.
How to use input parameter as a data source for manipulating the column , for instance in graphical calculation view we will be using it in calculated columns ..how can we achieve that in scripted calculation view ?
Let me explain this with a simple requirement. For example, there is a calculated field needed called Discounted Cost. The formula for value of this new field should be (Cost multiplied by a “Factor”). This factor will be input by the client whenever he/she calls this view.
So let’s see the steps now:
1. Create Input parameter P_FACTOR (Datatype type Decimal length of 2 and 3 decimal places)
2. Write select statement without the calculated field.
SELECT FIELD1, FIELD 2, FIELD 3, COST FROM SOURCE_VIEW
3. Now we add the calculated field.
SELECT FIELD1, FIELD 2, FIELD 3, COST, (COST * :P_FACTOR) AS DISC_COST FROM SOURCE_VIEW
If you have read all of my tutorials, you would know that we write a colon before the input parameter when used in code.
Hope this clarifies your question.
Hi Shyam ,
If we need to perform join on 3 tables how we can perform ? Currently I am using CE_JOIN key words for performing join between 2 tables and storing it into a variable and the output of this variable will be performed join with the third variable .How we can perform this action here ?
Example :I have 3 tables Product (Id , Text), Region(Id ,Text) ,Sales data(Emp ID , Product ID , Region ID , Amount)
Regards,
Suraj Grewal
Hi shyam ,
how to declare a variable in hana for example in sql we will do like
Declare @i as integer ..