HANA SQLScript Transformation Routines in BW/4HANA - an example

Veröffentlicht am 25. Januar 2022 von

Jörg Brandeis

In this blog post, I would like to share a small example of an AMDP [aka. HANA Script or AMDP Script ((Actually there is no HANA Script or AMDP Script. These terms can only be found in the SAP BW environment in the Transformations. This neologism is a mixture of the terms HANA or AMDP and SQLScript. Correct should be AMDP Transformation Routines or SQLScript Transformation Routines)) ] Show Transformation Routine in SAP BW/4HANA. In doing so, I start with the concepts of AMDP and SQLScript, then show what code the system generates as a wrapper for our implementation, and then fill it in with some simple SQLScript logic for calculating running totals.

In the Source-ADSO I have one amount (AMOUNT) per value type (VALTYP) , account (ACCOUNT) and tag (BUDAT). Now I want to add up these amounts in the TOTAL field over time.

Field Mapping in the Transformation

SQLScript (not HANAScript or AMDPScript!)

The HANA Database understands the standard SQL query language, just like any other Relational Database, And like most other database vendors, SAP has added some nice features to the SQL standard. Primarily, these extensions are able to write longer programs on the DB server side, which are then fully executed there. These programs are stored in so-called Procedures.

AMDP

AMDP stands for ABAP Managed Database Procedures. This means: Procedures that are managed by ABAP. Meanwhile, the AMDP framework goes far beyond this description, because it can be used to create more than just procedures. It can be also used to creating CDS Table Functions, Scalar Functions or Table Functions.

We don't need to worry about the details of AMDP for now. What you need to know: SAP BW generates the frame for us as an ABAP Class, in which we then have to implement a SQLScript-coded method.

AMDP Transformation Routines & SQLScript Transformation Routines

If we want to create a routine in BW/4HANA, we first have to answer the following question: in which language this routine should be implemented? This depends on the current runtime environment. Here, the SAP HANA Runtime is the standard. If we don't change that, then our Routines will have to be implemented in SQLScript:

Selection of the runtime environment in Transformations

In order to create a Routine click the corresponding button (see figure). With the End Routines you have the possibility to change the runtime to ABAP at the same time ((It remains a mystery to me, why this is only possible with the End Routines. )). Example: I click on the "Create SAP HANA script"-button for the End Routines .

Buttons for creating Routines

Then the popup for selecting the fields appears (see figure). By default, all fields assigned in the Transformation are checked. I want to calculate the field TOTAL in this example and therefore I check it as well before I click on the button Routine bearbeiten.

Selection of fields for the End Routine

This opens a new tab in Eclipse with a generated ABAP class in the /BIC/ namespace. The class name contains the end of the transformation-ID (TRANID). In this ABAP class there is a method GLOBAL_END, which we can implement in SQLScript((In my case the background for Embedded Languages is colored in yellow. In this free trial lesson from my e-learning courses you can learn, how to do this. )):

Screenshot of the GLOBAL_END method

We cannot simply activate these classes as usual. We need to insert some code first, because the parameter OUTTAB has to be assigned.

INTAB and OUTTAB

What used to be the RESULT_PACKAGE parameter informer days splits into the two parameters INTAB and OUTTAB in AMDP transformation routines. Both of them have the same structure, in Start-Routines as well as in End-Routines. I.e. we get by with the following minimal implementation, which is also suggested in the commentary:

outTab = SELECT * FROM :inTab;

Anyway, this implementation does nothing but a 1:1 assignment. The simplest sensible implementation is always based on a complete field list. So it looks like this in our case:

  outtab = SELECT budat,
                  valtype,
                  account,
                  recordmode,
                  amount,
                  total,
                  curr,
                  record,
                  sql__procedure__source__record
             FROM :intab;

This source code also makes only a 1:1 assignment. But here we can specifically edit the distinct fields. In our case, we want to calculate the field TOTAL . In ABAP, we would sort the data by ACCOUNT, VALTYPE and BUDAT and add the values in a loop. This is much easier in SQLScript - with Window Functions((SAP documentation on Window Functions)). With this, the calculation of a running total is very easy:

SUM(amount) OVER (PARTITION BY account, valtype ORDER BY budat ASC) AS total

The whole method looks like this then:

METHOD GLOBAL_END BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY.
-- *** Begin of routine - insert your code only below this line ***

-- Note the _M class are not considered for DTP execution.
-- AMDP Breakpoints must be set in the _A class instead.

  outtab = SELECT budat,
                  valtype,
                  account,
                  recordmode,
                  amount,
                  SUM(amount) OVER (PARTITION BY account, valtype ORDER BY budat ASC) AS total,
                  curr,
                  record,
                  sql__procedure__source__record
             FROM :intab;

-- *** End of routine - insert your code only before this line ***
ENDMETHOD.

ERRORTAB

The parameter ERRORTAB does not play any role in the beginning. As the name suggests, it is responsible for error processing. In BW/4HANA systems, it is only present if the "Allow error recovery for HANA Routines" checkbox is checked in the Transformation. If it is there, however, it must be assigned, that's why it makes trouble frequently. If we get an error message because of the ERRORTAB, we can simply add the following meaningless code to the end of the Routine and the system will be happy:

errorTab = SELECT * FROM :errorTab;

Conclusion

The coding effort for an AMDP Transformation Routine is not bigger than for an ABAP routine. If you have some experience on the subject, then it might become considerably faster and even more elegant. The key to success is a good knowledge of SQLScript. At leasst you have to know the basics of SQLScript, otherwise it's hard to find good solutions. That's why I recommend Brandeis Consulting trainings to everyone. Either as live training, in-house or as open training. Or the e-learning courses SQLScript Basics and AMDP Transformation Routines. There are also a few free trial lessons where you can pick up some helpful tips.