Custom transformations in SAP BW (as of SAP BW 7.4) are implemented using ABAP routines – Start, End or Expert – which are executed on the ABAP Application server. During execution of ABAP based transformation, data is retrieved from database to the application server for processing and transformed data is written back into the database. Data transfer thus happens twice during ABAP runtime, once from database to application server and again back to the database. This, coupled with row-by-row processing of field-based calculations add huge performance overheads to the data load process.

Transformation Execution on HANA

BW4HANA 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 significantly reduces transformation runtime.

In a BW4HANA 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 BW4HANA to check whether execution can run in HANA runtime.

SAP BW4HANA Transformation HANA Pushdown - 1

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, how to benchmark it against traditional ABAP routines and discuss its limitations.

How to create an AMDP script based transformation?

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

SAP BW4HANA Transformation HANA Pushdown - 2

Step 2: Create transformation between aDSO and data source.

SAP BW4HANA Transformation HANA Pushdown - 3

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

SAP BW4HANA Transformation HANA Pushdown - 4

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

SAP BW4HANA Transformation HANA Pushdown - 5

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

SAP BW4HANA Transformation HANA Pushdown - 6

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

SAP BW4HANA Transformation HANA Pushdown - 7

Features – AMDP Script vs ABAP Routine

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

FeaturesAMDP scriptABAP routine
Execution push down to HANA databaseYesNo (runs in ABAP Application Server)
Supported Data Targets are Info Object and advanced Data Store Object(aDSO) (in BW/4HANA)YesYes
Supports SAP HANA SQL ScriptYesNo
Enable Error Handling for Expert Script in TransformationYesNo
Package based data processingYesNo
Row-by-row data processingNoYes
Objects like function module and class are supportedNoYes
Supports AMDP objects like AMDP Class and AMDP MethodYesNo
Calculation Engine functions can be leveragedYesNo

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
  • Allow 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. Certain situations prevent transformation logic execution pushdown to SAP HANA database. For example:

  • Queries as InfoProviders 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:

This Blog was written based on features in SAP BW4HANA support package level 01. It may change in upcoming releases and support packages of SAP BW4HANA.

 

5600 Tennyson Pkwy
Suite 120
Plano TX 75024.

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com

mobile-bi-strategy-and-options-for-sap-customers-visual-bi-webinar-popup-cover-image

Join us for a discussion on the latest Mobile BI options and strategies available to SAP customers

You have Successfully Subscribed!