Custom transformations in SAP BW (as of SAP BW 7.4) are implemented using ABAP Routine as Start, End or Expert routine executing it on ABAP Application server. During execution of ABAP based transformation data’s are retrieved from database to application server for processing and transformed data are written back to database as shown in Figure 1. Data transfer happens twice in ABAP runtime, from database to application server and to database. This, along with row-by-row processing of field based calculations adds huge performance overheads to the data load process.

Figure_1_1.png

Figure 1: Execution of SAP BW Transformations Source: SAP

Transformation execution on HANA

BW/4 HANA has the option to push down data transformation logic to HANA database, to improve data acquisition performance. The calculation execution and parallelization while retrieving the data from the database will reduce the runtime of transformation in HANA.

In a BW/4HANA system, transformations will support both – ABAP and HANA runtime. Once the transformation has been activated, system checks whether the transformation logic can be pushed down to the SAP HANA DB.  There is an option in BW/4HANA to check whether execution can run in HANA runtime.

For custom transformations to be pushed down to SAP HANA database, start, end and expert routine type need to be created as AMDP script (ABAP Managed Database Procedure script).

In this blog, I will provide an overview on: creating AMDP based transformations, benchmark it against traditional ABAP routines and discuss its limitations.

How to create AMDP script based transformation?

For more details on creation of AMDP Script based transformation you can refer to “HANA based BW Transformation”  and HANA based Transformation deep dive by Torsten.Kessler

Step: 1 Create an aDSO (Advanced Data Store Object) with activate data and write change log as shown below in SAP BW Modelling tools (BWMT) in Eclipse.

Step: 2 Create transformation between aDSO and data source.

Step: 3 Create expert routine from Edit Menu -> Routine -> Expert Routine.

Step: 4 Pop-up will ask for confirmation to replace standard transformation with expert routine. Click on the “Yes” button.

Step: 5 An AMDP Class will be generated with default method – PROCEDURE and with default interface – IF_AMDP_MARKER_HDB

Step: 6 Open ABAP development tools in Eclipse with BW/4HANA system in ABAP perspective to change the HANA SQL script

 

Features – AMDP Script Vs ABAP routine

Following are list of features available in AMDP script (ABAP Managed Database Procedure script) comparing to ABAP based transformation.

Features AMDP script ABAP routine
Execution push down to HANA database Yes No (runs in ABAP Application Server)
Supported Data Targets are Info Object and advanced Data Store Object(aDSO) (in BW/4HANA) Yes Yes
Supports SAP HANA SQL Script Yes No
Enable Error Handling for Expert Script in Transformation Yes No
Package based data processing Yes No
Row-by-row data processing No Yes
Objects like function module and class are supported No Yes
Supports AMDP objects like AMDP Class and AMDP Method Yes No
Calculation Engine functions can be leveraged Yes No

Listed below are some of points that need to be taken into consideration during AMDP script based transformation.

  • Null values are allowed, so default initial values must be filled depend upon the data type in script
  • Allows all special characters will cause failure of data load and activation
  • Derivation of time characteristics with time distribution is not supported by system default, need to handle them in script
  • Fields must be filled using internal value representation
  • Data type of source and target should match, if not casting or conversion should be carried out

Limitations

All the BW transformations cannot be pushed down to SAP HANA using this approach. There are features that prevents transformation logic execution push down to SAP HANA database are shown below.

  • Queries as Info Providers are not supported as the source
  • Rule groups are not supported
  • Customer-Defined Functions in the Formula Editor
  • To read data from Data Store objects, the entire key must be provided
  • Near-line connections are not supported

Note:

Above blog is written based on features in SAP BW/4HANA support package level 01. It may change in upcoming releases and support packages of SAP BW/4HANA.

 

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply