Technical Articles
Availability of Table Function node in CV modeling – Web IDE HANA
In Graphical CV modelling of SAP HANA, Table functions are widely used for SQL based approach in calculation view developments, Starting with HANA 2.0 SP 01 release, Table function has been available as graphical node in the CV modeling. This feature is only available on XS advanced server using Web IDE HANA tool. In XS classic using HANA Studio, we can have Table function used in either projection or Aggregation as a data source., but not as a direct node itself. It is explained in the official documentation here, SAP help
In this document, I am gong to describe how this feature can be useful in modeling a graphical CV. Before introduction node, table function can be inserted in a Projection or Aggregation, it can’t have any node which can be used as datasource to use in table function logic, the dataset will be derived in the table function explicitly by reading the other DB tables or CVs, and also more importantly, we can’t pass the input parameter with multiple values., these 2 are bigger challenges addressed in Web IDE based developments. However, we were able to pass the result set(return table) to above(further) nodes in the CV in older releases also, as shown in the below picture,
the above picture I have taken it from Web IDE, this behavior is same in HANA studio also., so you can’t have datasource mapped to a projection which has table function in it. Ofcourse, the result set of table function can be mapped to other node.
In the new feature, table function node, we can have a datasource to table function node, the possible datasources can be a projection or aggregation or direct table or CV which is inserted into the TF node ifself. You can see that from the below picture,
after the datasource has been added to the node, then it will be mapped to the columns of table function input parameters defined as table type. Finally output parameter(return table) columns will be mapped to further node in the CV. In this Way, Graphical modeling using table functions becomes more comfortable than before, because we do not have to switch to multiple CVs when a output of a node is used as datasource to Table function.
Notes:
- if the table function is having scalar parameters, then it will be passed through input parameters in the CV.
- A table function can be used as datasource to table function node, which means, In the above example, aggregation_1 node can have table function also.
- There can be multiple table function nodes in a graphical CV.
Thank you..,
I created a how to navigation tutorial on this topic, shown below.
Thank you Sreekanth Surampally
nice blog post about this useful feature
Great post! This is what is missing in XS Classic.
Thanks.
Great post !!! Thanks, Sreekanth Surampally
I have a question though since we have so much more functionality with Table Functions are there any reason sap recommends graphical calculation views?
Consider a scenario where all data sources are Table Functions and graphical CVs are used only to project the results for reporting tools. Are there any demerits to this strategy.
table ---> Table function ---> Calculation view ---> SAC/BO
Yes, it is more feasible to use Calculation views for modeling than Table functions. The reasons are, you have a graphical interface for CV development, which makes easy for developers to model. Example, I have a requirement to "apply the filter before Ranking", in getting this done in scripted table function, bit of coding involved, now SAP offered that feature with a little check box in a CV. so I would prefer to use that. Moreover, the calculation engine optimizer will take care of the performance considerations of CV, but in table function developers have to code it.
Generally, Table functions are used, if you can't achieve the logic with available nodes of CV. Lastly, if you wanted to use CV just as wrapper to functions, you can do so, but the above you are considering the above factors.
Sreekanth
I agree with your points. However, on the performance of TFs, I have observed that there is not much difference between CV and TF at least from the tests I have executed and no extra coding was required in the TFs.