Functions, or User Defined Functions (UDF), as they are more formally known, allow developers to capture a reusable block of SQLScript code that provides a result after working through some data processing logic. Often, functions allow input parameters to pass key information to the processing logic so that the function can be reused in different scenarios.
Functions are relevant to SAP HANA Cloud modeling because they offer a way to extend the standard capabilities provided with graphical calculation views to reach more complex data processing possibilities. Functions are written in SQLScript language using source files. After a successful deployment, a runtime object is generated from the source file. When executed, functions always run in the in-memory SAP HANA Cloud database, and so performance is optimized.
SAP HANA Cloud supports scalar and table functions. Table and scalar functions are created as source files in a project folder. The source file has the extension
.hdbfunction The same extension is used for table and scalar functions.
Functions, both scalar and table, are always read-only. It is not possible to alter the database by using data definition statements such as create table, or data manipulation statements such as update.
Functions can call other functions which encourages a modular approach to ensure high reuse.
One of the common uses of a scalar function in a calculation view, is to derive values for input parameters.
A simple example of a scalar function could be to return the current date. However, scalar functions can be more complex and can read tables and call other database objects (including other user defined functions).
A scalar function returns one or more parameters but each parameter always has only a single value. Scalar functions often have one or more input values, but these are not mandatory. For example, returning the current date requires no input parameter, whereas if you wanted to return the date that was x days earlier than today, then you would need to provide x as the input parameter.
The following is a breakdown of a scalar function’s code:
Notice the use of the keyword RETURNS for scalar functions. The keyword RETURNS TABLE is used for table functions.
Table functions are used to return a tabular data set as opposed to a single value parameter. A tabular data set can consist of one or more columns and usually has multiple values (rows). Table functions are commonly used by modelers to define a customized data source in a calculation view. Refer to the function wherever a data source can be defined, such as in a calculation view Projection node or Join node just as you would include a table. In fact, it might help to think of them as dynamic tables that are created at run time.
As an example of a table function, consider that you may need to read through a table that contains sales orders so that you can find open orders that have been recently created. The date used for the determination of the required orders is based on an input parameter passed from the calculation view. The resulting open orders are passed to the calculation views as a data source.
The following is a breakdown table function’s code:
Notice the use of the keyword RETURNS TABLE for table functions. The keyword RETURNS is used for scalar functions.
All output parameters of the table function are offered as columns to the calculation view node. If your table function requires input parameters you can first define input parameters in the calculation view and then use the input parameter mapping function to map them.
Since SAP HANA Cloud 2.0 SPS01, a new calculation view node is available; Table Function. It is specially dedicated to the handling of the different types of mapping of the input parameters in a table function. It was possible to achieve the same outcome using other node types, but this new node makes things easier to define the parameter mapping.