Creating and Using Functions

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Describe how functions can be consumed by calculation views

Introducing Functions

Functions, or User Defined Functions (UDF), as they are more formally known, allow developers to define a reusable block of data processing using the powerful SQLScript language to generate either a single value or a complete data set. For a modeler, functions are interesting because they can be used in calculation views to provide custom logic when the standard features of graphical calculation view editor are insufficient.

Functions can consume input parameters defined in calculation views. This means the same function can be reused in different calculation views.

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. This means it is not possible to use any data definition language such as create table or alter table. It is not possible to use data manipulation statements such as update or insert into. Functions never change the database.

Functions can call other functions which encourages a modular design approach to maximize reuse.

Scalar Function

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

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.

In a calculation view, it is possible to choose a table function as a data source to any node, just as if you were choosing a table. But in the calculation view editor there is also a dedicated node; Table Function. This node is specially dedicated to the handling of the different types of mapping of the input parameters of a table function.

  • Data Source Column

    First, add a data source to the Table Function node. Then, map one of the data source columns to one or more table function input parameters.

  • Input Parameter

    Choose an input parameter from the calculation view and map it to one or more table function input parameters. This could be useful when you want a user to manually enter a value at run-time.

  • Constant

    Manually define a single, fixed value and map it to one or more table function input parameters. This is really only useful when the input value rarely/never changes.

Note
You should also be aware of Procedures which have many similarities with functions. Just like functions, procedures provide a way to define re-useable, custom SQLScript code for complex data processing. A key advantage of procedures is that they support data definition and data manipulation language that alter the database. But a modeler rarely needs to alter the database and simply needs to read data. Unlike functions, you cannot use a procedure as a data source in a calculation view. So why should a modeler learn to define procedures? It is because you can define a flexible analytic privilege using SQLScript logic defined in a procedure. See the SAP Help documentation for more details on procedures.

Consume a Table Function in a Calculation View

SQL Function

Watch this video to learn how to consume a Table Function in a Calculation View.

Save progress to your learning plan by logging in or creating an account