How to create SAP BW Transformations with SQL Script

In this previous article you got to know the advantages of SQL Script and the decision criteria for implementation of SQL Script routines. In this article, I will show you how to create SAP BW transformation routines with SQL Script in detail.

Although SQL transformations benefit from code pushdown, you have to do without predefined ABAP functions and implement the desired logic in SQL Script. Using a concrete application case, I will show you how to convert fiscal periods into calendar months.

Create Transformation Routines

The fiscal periods are in version V3. In order to derive the corresponding calendar months, we have to calculate three months back and also take  the annual shift into account.

In the table below you will find our example with the fiscal periods and the corresponding calendar months that have to be determined.

We will implement this logic in form of a transformation routine. To do this, we create a transformation in the BW Modeling Tools. Please note that initially both ABAP runtime and HANA runtime are supported. You can read this information under Runtime Status.

To convert fiscal periods into calendar months, we create an expert routine in which we have access to source and target fields. Please follow the menu path Edit → Routines → Expert routine to create an expert routine.

You will be asked if you want to delete the existing transformation and replace it with an expert routine. Please confirm with Yes. Then you will be asked if you want to create an ABAP routine or an AMDP script. To benefit from the HANA Code Pushdown, select the option AMDP Script.

Afterwards you have to select an existing ABAP project or create a new project in BW Modeling Tools. Then you can implement your logic within the PROCEDURE method. The tables outTab and errorTab are used as transfer parameters. Please note that the order of the SQL statements must match the table definition. The table definition can be seen at the beginning of the class definition.

In our case the logic is:

outTab =
SELECT company, fiscper, fiscvarnt,
CASE
WHEN SUBSTRING(fiscper,5,3) = 001 then CONCAT(SUBSTRING(fiscper,1,4) - 1, SUBSTRING(fiscper,5,3) - 3 + 12)
WHEN SUBSTRING(fiscper,5,3) = 002 then CONCAT(SUBSTRING(fiscper,1,4) - 1, SUBSTRING(fiscper,5,3) - 3 + 12)
WHEN SUBSTRING(fiscper,5,3) = 003 then CONCAT(SUBSTRING(fiscper,1,4) - 1, SUBSTRING(fiscper,5,3) - 3 + 12)
ELSE CONCAT(SUBSTRING(fiscper,1,5), SUBSTRING(fiscper,5,3) - 3)
END AS calmonth,
currency, recordmode, amount, record, SQL__PROCEDURE__SOURCE__RECORD
FROM :inTab;

Increase the performance of your BW with SQLScript

If you don't want to implement error handling, you can transfer an empty errorTab table.

errorTab =

SELECT '' AS ERROR_TEXT,

'' AS SQL__PROCEDURE__SOURCE__RECORD

FROM DUMMY

WHERE DUMMY <> 'X';

After implementing the logic, please activate the AMDP class and the transformation. Note that after implementation of the AMDP routine, only the HANA runtime is supported.

You can review the HANA transformation via Extras → Display Generated HANA Transformation.

There you can switch to the expert mode via Extras → Expert mode to display the calculation scenario.

There you can see the script definition as well as the XML representation of the calculation scenario.

If you now create a DTP, you will see that it is running in HANA.

Please activate the DTP and execute it. The calendar months are filled according to the defined logic. You can see the result on the picture below.

Image source: Pexels, CC0 License