AMDP Script

To enable the AMDP script in SAP/BW, pre required steps.

The technical perspective of the SAP HANA Expert Script is a SAP HANA database procedure.
From the BW developer perspective the SAP HANA Expert Script is a SAP HANA database
procedure implemented as a method in an AMDP (ABAP Managed Database Procedure) class.

The AMDP class is be generated by the BW framework and can only be modified within
the ABAP Development Tools for SAP NetWeaver (ADT),

Procedure and pre-requisite

https://tools.hana.ondemand.com/#abap.

The generated AMDP class cannot not be modified in the SAP GUI like Class editor (SE24)
or the ABAP Workbench (SE80). Therefore, it is recommended to implement the entire
dataflow in the Modeling Tools for SAP BW powered by SAP HANA,

Procedure and pre-requisite

https://tools.hana.ondemand.com/#bw.

The BW transformation itself must still be implemented in the Data Warehousing Workbench
(RSA1).

Next I’ll give a step by step introduction to create a BW transformation with a SAP HANA Expert
Script.

Step 1: Start a SAP HANA Studio with both installed tools:

 ABAP Development Tools for SAP NetWeaver (ADT) and

 Modeling Tools for SAP BW powered by SAP HANA

Now we must switch into the BW Modeling Perspective. To open the BW Modeling Perspective
go to Window => Other .. and select in the upcoming dialog the BW Modeling Perspective, see
Figure 1.3.
 AMDP Script

Figure 1.3: Open the BW Modeling Perspective

To open the embedded SAP GUI a BW Project is needed. It is necessary to create the BW Project
before calling the SAP GUI. To create a new BW Project open File => New => BW Project. To
create a BW Project a SAP Logon Connection is required, choose the SAP Logon connection and
use the Next button to enter your user logon data.

Recommendations: After entering your logon data it is possible to finalize the wizard and create
the BW Project. I recommend to use the Next wizard page to change the project name. The default
project name is:

<System ID>_<Client>_<User name>_<Language>

I normally add at the end a postfix for the project type such as _BW for the BW Project. For an
ABAP project later on I will use the postfix _ABAP. The reason I do that is both projects are using
the same symbol in the project viewer and the used postfix makes it easier to identify the right
project.

Once the BW Project is created we can open the embedded SAP GUI. The BW Modeling
perspective toolbar provides a button to open the embedded SAP GUI, see Figure 1.4.

Figure 1.4: Open the embedded SAP GUI in Eclipse

 AMDP Script

Choose the created BW Project in the upcoming dialog. Next start the BW Workbench
(RSA1) within the embedded SAP GUI and create the BW transformation or switch into the edit
mode for an existing one.

To create a SAP HANA Expert Script open Edit => Routines => SAP HANA Expert Script Create in
the menu of the BW transformation. Confirm the request to delete the existing transformation logic.
Keep in mind that all implemented stuff like Start- End- or Field-Routines and formulas will be deleted
if you confirm to create a SAP HANA Expert Script.

In the next step the BW framework opens the AMDP class by calling the ABAP Development Tools for
SAP NetWeaver (ADT). For this an ABAP project is needed. Select an existing ABAP Project or create
a new one in the dialog.

A new window with the AMD class will appear. Sometimes it is necessary to reload the AMDP class by
pressing F5. Enter your credentials if prompted.

The newly generated AMDP class, see Figure 1.5, cannot not directly be activated.

Figure 1.5: New generated AMDP Class

Before I explain the elements of the AMDP class and the method I will finalize the transformation with
a simple valid SQL statement. The used SQL statement, as shown in Figure 1.6, is a simple 1:1
transformation and is only used as an example to explain the technical behavior.
 AMDP Script

Figure 1.6: Simple valid AMDP Method

Now we can activate the AMDP class and go back to the BW transformation by closing the AMDP
class window. Now it is necessary to activate the BW transformation also. For a BW transformation
with a SAP HANA Expert Script the flag SAP HANA Execution possible is set, see Figure 1.7.

Figure 1.7: BW Transformation with SAP HANA Script Processing

As explained before, if you use a SAP HANA Expert Script the BW transformation can only been
processed in SAP HANA. It is not possible to execute the transformation on the ABAP stack. Therefore
the generated ABAP program (Extras => Display Generated Program) is not available for a BW
transformation with the processing type SAP HANA Expert Script.
 AMDP Script

1.2.2.1 Sorting after call of expert script

Within the BW transformation the flag Sorting after call of expert script, see Figure 1.8, (Edit => Sorting
after call of expert script) can be used to ensure that the data is written in the correct order to the target.

Figure 1.8: Sorting after call of expert script

If the data is extracted by delta processing the sort order of the data could be important (depending
on the type of the used delta process).

By default, the flag is always set for all new transformations and it’s recommended to leave it
unchanged.

For older transformations, created with a release before 7.40 SP12, the flag is not set by default.
So the customer can set the flag if they need the data in a specific sort order.

Keep in mind that the flag has impact at two points:

 The input/output structure of the SAP HANA Expert Script is enhanced / reduced by the
field RECORD
 The result data from the SAP HANA Expert Script will be sorted by the new field RECORD,
if the flag is set, after calling the SAP HANA Expert Script

The inTab and the outTab structure of a SAP HANA Expert Script will be enhanced by the
field RECORD if the flag is set. The added field RECORD is a combination of the
fields REQUESTSID, DATAPAKID and RECORD from the source object of the transformation, see Figure
1.9.

Figure 1.9: Concatenated field RECORD

The RECORD field from the outTab structure is mapped to the internal field #SOURCE#.1.RECORD.
Later on in a rownum node of the CalculationScenario the result data will be sorted by the new
internal field #SOURCE#.1.RECORD, see Figure 1.10.
 AMDP Script

Figure 1.10: CalculationScenario note rownum

1.2.2.2 The AMDP Class

The BW transformation framework generates an ABAP class with a method called PROCEDURE. The
class implements the ABAP Managed Database Procedure (AMDP) marker
interface IF_AMDP_MARKER_HDB. The interface marks the ABAP class as an AMDP class. A method
of an AMDP class can be written as a database procedure. Therefore the BW transformation framework
creates a HANA specific database procedure declaration for the method PROCEDURE, see Figure 1.11:

Figure 1.11: Method PROCEDURE declaration

This declaration specifies the method to the HANA database (HDB), the language to SQLSCRIPT and
further on defines that the database procedure is READ ONLY. The read only option means that the
method / procedure must be side-effect free. Side-effect free means that only SQL elements (DML)
could be used to read data. Elements like DELETE, UPDATE, INSERT used on persistent database
objects are not allowed. These data modification statements can also not be encapsulated in a further
procedure.

You cannot directly read data from a database object managed by ABAP like a table, view or procedure
inside an AMDP procedure, see (1) in Figure 1.12. A database object managed by ABAP has to be
declared before they can used inside an AMDP procedure, see (2). For more information about
the USING option see AMDP – Methods in the ABAP documentation.

Modification of the method declaration

In case of reading from further tables inside the SQL Script it could be necessary to change
(enhance) the method declaration by adding the USING option. It is important to ensure that the
first part of the method declaration is stable and will not be changed. Do not change the following
part of the declaration:

METHOD PROCEDURE BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-
ONLY

The USING option must be added at the end of the declaration part, see Figure 1.12
 AMDP Script

Figure 1.12: Declaration of DDIC objects

The AMDP framework generates wrapper objects for the declared database object managed by
ABAP . The view /BIC/5MDEH7I6TAI98T0GHIE3P69D1=>/BIC/ATK_RAWMAT2#covw in (3) was
generated for the declared table /BIC/ATK_RAWMAT2 in (2). The blog Under the HANA hood of an
ABAP Managed Database Procedure provides some further background information about AMDP
processing and which objects are generated.

AMDP Class modification

Only the method implementation belongs to the BW transformation Meta data and only this
part of the AMDP class would been stored, see table RSTRANSCRIPT.

Currently the ABAP Development Tools for SAP NetWeaver (ADT) does not protect the source
code which should not been modified, like in an ABAP routine. That means all modifications
in the AMDP class outside the method implementation will not be transported to the next
system and will be overwritten by the next activation process. The BW transformation
framework regenerates the AMDP class during the activation process.

Reading from local table without DDIC information

All tables and views from the local SAP schema must be declared in the USING clause and must be
defined in the ABAP DDIC. It is possible to read data from other schema without a declaration in
the USING clause.
If a table or view in the local SAP schema starts with the prefix /1BCAMDP/ it is not necessary to
declare the table/view in the USING clause.
 AMDP Script

Later on I’ll provide some general recommendations in a separate blog which are based on
experiences we collected in customer implementations and customer incidents. The general
recommendation will cover the following topics:

 Avoid preventing filter push down

 Keep internal table small
 Initial values
 Column type definition
 Avoid implicit casting
 Use of DISTINCT
 Potential pitfall at UNION / UNION ALL
 Input Parameter inside underlying HANA objects
 Internal vs. external format
 …
AMDP Class name
Until version BW 7.50 SP04 the AMDP class name is generated based on the field RSTRAN-
TRANPROG. From BW 7.50 SP04 the field RSTRAN-EXPERT is used to generate the AMDP
class name. The metadata storage for the AMDP routines was adjusted to align the AMDP
metadata storage analogous to the ABAP metadata.

AMDP database procedure

Sometime, in BW 7.40, it could be happen that the corresponding database procedure is not
generated. The report RSDBGEN_AMDP can be used to generate the database procedures
for a given AMDP class.
1.2.3 Dataflow with more than one BW transformation
The push down option is not restricted on data flows with one BW transformation. It is also possible to
push down a complete data flow with several included BW transformations (called stacked data flow).
To get the best performance benefits from the push down it is recommended to stack a data flow by a
maximum of three BW transformations. More are possible but not recommended.

The used InfoSources (see SAP Help: InfoSource and Recommendations for Using InfoSources)
in a stacked data flow can be used to aggregate data within the data flow if the processing mode
is set to ABAP. If the processing mode set to SAP HANA the data will not be aggregated as set in
the InfoSource settings. The transformation itself does not know the processing mode, therefore
you will not get a message about the InfoSource aggregation behavior. The used processing mode
is set in the used DTP.

That means, the BW transformation framework prepares the BW transformation for both
processing modes (ABAP and HANA). During the preparation the framework will not throw a
warning regarding the lack of aggregation in the processing mode HANA.

By using the check button for the HANA processing mode, within the BW transformation, you will get
the corresponding message (warning) regarding the InfoSource aggregation, see Figure 1.13
 AMDP Script

Figure 1.13: HANA processing and InfoSources

CalculationScenario in a stacked data flow

The corresponding CalculationScenario for a BW transformation is not available if the source object
is an InfoSource. That means the tab CalculationScenario is not available in the export mode of
the SAP HANA transformation, see Extras => Display Generated HANA Transformation. The
source object for this CalculationScenario is an InfoSource and an InfoSource cannot be used as
data source object in a CalculationScenario. The related CalculationScenario can only be obtain
by using the SAP HANA Transformation from the corresponding DTP. I’ll explain this behavior later
on in the blog »HANA based Transformation (deep dive)«.
1.2.4 Create a Data Transfer Process (DTP) to execute the BW
transformation in SAP HANA
The Data Transfer Process (DTP) to execute a BW transformation provides a flag to control the HANA
push-down of the transformation. The DTP flag SAP HANA Execution, see (1) in Figure 1.14, can be
checked or unchecked by the user. However, the flag in the DTP can only be checked if the
transformation is marked as SAP HANA Execution Possible, see (1) in Figure 1.2. By default the
flag SAP HANA Execution will be set for each new DTP if

 the BW transformation is marked as SAP HANA execution possible and

 the DTP does not use any options which prevent a push down.

Up to BW 7.50 SP04 the following DTP options prevent a push down:

 Semantic Groups
 Error Handling – Track Records after Failed Request

The DTP UI provides a check button, like the BW transformation UI, to validate a DTP for HANA push
down. In case a DTP is not able to push down the data flow (all involved BW transformations) logic, the
check button will provide the reason.
 AMDP Script

Figure 1.14: DTP for the first simple SAP HANA based Transformation

In the simple transformation sample above I’m using one BW transformation to connect a persistent
source object (DataSource (RSDS)) with a persistent target object (Standard DataStore Object
(ODSO)). We also call this type a non-stacked dataflow – I’ll provide more information about non-
stacked and stacked data flows later. The related SAP HANA Transformation for a DTP can be found
in menu Extras => Display Generated HANA Transformation, see (2) in Figure 1.14. In case of a non-
stacked data flow the DTP uses the SAP HANA Transformation of the BW transformation,
see (3) in Figure 1.14.

The usage of a filter in the DTP does not prevent the HANA push down. ABAP Routines or BEx
Variables can be used as well. The filter value(s) is calculated in a pre-step and added to the SQL
SELECT statement which reads the data from the source object. We will look into this later in