How to use table functions in datasphere

The utilization of the datasphere provides significant advantages, by facilitating a cohesive business data framework that aligns mission-critical data throughout the entire organization. However within datasphere, incorporating table functions into the data flow may be difficult to grasp. Especially for new or intermediate developers. This blog aims to help users do that, with information on how to create, use and consume table functions. Also included are some tips & tricks on how to make your life a little bit easier while doing so.

First step - generating output columns

While using datasphere a developer will have 2 script options available: the SQL standard query, and the SQLscript. The SQL standard query supports SELECT, FROM and WHERE statements. The Table function (or SQLScript) is used for more advanced function such as loops, IF statements and many others.

To start out in a script view you can drag a data source from the “repository” or “sources” tab into the script. This will automatically insert the correct technical name of the data source.

The easiest way to create the output columns is by using the following code:

“ return

select *

<h2>FROM "YOUR_DATASOURCE" ;”

Select the validate button in the top right.

Now the “ *” should be replaced by all columns that exist in the selected data source.

Additionally the columns can now be found in the “Columns” section under the model properties on the right hand side.

Using input parameters

Once you have understood how to generate the output columns, you can start using SQL functions or clauses when necessary for inputs. For example, we can add a ‘where’ clause with an input parameter. But first, you will have to create the input parameter under model properties.

Next, create an input parameter, for example, based on date, like shown in the image.

Now the input parameter can be used to filter the dataset. Additionally, in the views that will consume the table function the input parameter has to be used. Using an input parameter can easily be done by typing the “:” and inserting the input parameter name. Hereby an example:

If you are consuming an input parameter in a table function that has been created in the underlying data source you will need to define the input parameter between brackets as can be seen below.

If you would like to add any additional logic you can, for example, use the following code to subtract 1 year from the keydate: “ADD_YEARS(:KEY_DATE,-1)” and insert it after the “=>”

Using advanced functions

A lot of functions can be used in Datasphere. If you are searching for a comprehensive list of functions, the best list that is available is the HANA reference guide here. Most functions available on the list can also be used in Datasphere but some of them are only available in HANA. Additionally, you can type any word or letter and Datasphere will search for any functions or column that contains those letters.

Consuming the Table function

Once the table function is successfully deployed by clicking on the (very old school) floppy in the top left, the script will be available as a data source and can be used in any graphical or script view by simply searching for the respective business/technical name in the repository.

In case you want to consume the table function in SAC or any other BI tool directly the “Expose for Consumption” can be toggled.

Conclusion

In case there is a requirement that is not possible in a graphical view, Table functions can be used for any advanced logic/looping/window functions. With datasphere it is relatively easy to create output and to consume this in a graphical view afterwards.

In case you have any question regarding Datasphere feel free to reach out to Joran de Vries.