SAP HANA System performance optimization with AMDP
SAP has introduced the concept for “Code pushdown”, which means delegating data intense calculations to the database layer. In this way, the complex data calculations are done in SAP HANA Database - “Code to Data” instead of using the ABAP Server for the calculations and transferring huge amounts of data between the server and database. (see figure 1)
Figure 1 Code pushdown
SAP NetWeaver 7.4 SPS 05 launched a new feature in AS ABAP called ABAP Managed Database Procedure (AMDP). The new feature allows ABAP developers to create and manage database procedures in ABAP framework. AMDP uses “Top-down” approach, in other words, the “Code pushdown”. It is fully managed in ABAP stack from creation, editing, activating to transportation, similar to any other ABAP artifact. The first call of AMDP creates corresponding database procedure as shown in figure 2.
Figure 2 Top-down Approach
In order to create an AMDP, we only need an Eclipse-based development environment (ABAP Development Tools). The ABAP developer doesn't necessarily have to have access to a HANA Database and/or to use SAP HANA studio. AMDPs are defined in regular ABAP classes. A regular ABAP class allows AMDP when the interface
“IF_AMDP_MARKER_HDB” is included. The interface is not adding any methods, just marks it as AMDP class. The AMDP method has to use the following keywords after its name “BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT” in the class implementation section. The HDB (SAP HANA) defines what kind of database is going to be used. Currently, only the SAP HANA Database is supported. SQLSCRIPT defines the used Database language. For SAP HANA this is SQLSCRIPT. In future other languages could be supported, for example, R.
When it's required to create a side-effect free procedure, the keywords “OPTIONS READ-ONLY” have to be used. Then the AMDP method doesn't allow any DML statements. If ABAP DDIC entities or other procedures/methods are used in the AMDP method, they should be declared with the keyword “USING”. For example, the table “RSPMREQUEST”, which keeps Request Status information (RSPM), is used in the sample class in Figure 3.
Figure 3 Example of Class with AMDP
AMDP has restrictions on parameters that can be used. The parameters have to be passed by VALUE and not copied and passed by Reference. Also, the parameters should be Tables or Scalar types. This means that variables, structures, and simple internal tables can be used, whereas deep structures, complex tables (nested tables), and objects are not allowed.
In case of errors, SAP provides exception handling in AMDP classes. In Figure 4. we can see how the exception-based class is used. It is declared explicitly using the keyword "RAISING" in the definition of AMDP method. A naming convention for exception-based classes exists. All the names start with prefix “CX_AMDP”. The full list of exception-based classes can be found here.
Figure 4 Definition of AMDP method with exception handling
AMDP provides native access to HANA and avoids unnecessary data transfers between database and application server. Also, it can improve the performance of data transformation in the Extract, Transform and Load (ETL) process in BW. AMDP could be used in Characteristics relationship and Data Slices with Planning Application Kit (PAK). AMDP’s lifecycle management is completely supported by ABAP system.
At DAHLBEER, we have achieved significant performance improvement for our client, when used AMDP in data transformation in the Extract, Transform and Load (ETL) process in BW. Now, the ETL process is 10 times faster than before, which allows our client to focus on data analyses and achieving results, rather than waiting for the data being processed.