AMDP vs. ABAP

ABAP or AMDP? This is a question many customers ask themselves when using HANA-based systems. In this blog, we highlight the advantages and disadvantages of the two approaches and provide decision support for future implementation efforts.

What is AMDP?

In our previous Blog on SQLScript, we discussed how HANA opens a gateway to new modeling. By developing close to the database, the full power of the in-memory database is exploited.

SAP has built an extension into ABAP for the use of SQLScript: ABAP Managed Database Procedures (AMDP). These are database procedures or functions that are created, managed and transported via ABAP. SAP thus provides a window into the world of SQLScript without having to descend directly to the database.

But many customers are now faced with a central question: ABAP or AMDP?

AMDP vs. ABAP: These are the differences

First things first: ABAP is not obsolete! It is (and will remain) an integral part of systems such as S/4HANA and BW/4HANA. The relevance is also made clear by ABAP Steampunk, the future of ABAP in the cloud.

However, the question now remains as to which programming language should be used for database processing. But what is actually the difference between the two technologies?

With ABAP, all data is first loaded from the database into the application server. It is always important to keep the data flow between the systems (traffic) low, as valuable time is lost here. Subsequently, the data is further processed in internal tables.

AMDP, on the other hand, doesn’t have this problem as it lives directly on the database and doesn’t cause any traffic. Thus, tables can be directly connected to each other without detours.

ABAP can call AMDP methods (see the Blog), but the reverse is not possible: SQLScript has no connection to ABAP because it sits natively in the HANA database. Especially for BW transformations or ABAP CDS views, it has to be decided at the beginning if AMDP should be used.

AMDP Examples

In the following, we will look at concrete examples of how AMDP works and can be implemented in comparison to ABAP.

AMDP Lookup

Let’s look at the two technologies using a simple example: We are in the

end routine of a BW transformation. The goal is to realize a lookup on master data.

With ABAP, the goal is to minimize the data flow between systems, which is why prefetch tables are often used. The procedure is as follows:

1. Creation of types for prefetch and lookup table
2. Filling the prefetch table
3. Selection of relevant records from the master data table
4. Loop over Result Package with Read-Statement on Hashed Table

The ABAP coding for the lookup has the following form:

Figure 1: ABAP Lookup with LOOP

ABAP 7.4 introduced the new VALUE command, which replaces loops in most cases. A corresponding code can be taken from the next picture (without type definition):

Figure 2: ABAP Lookup with VALUE

Lookups are also often implemented via class methods, where the constructor populates the master data into an internal table and further get class methods exist for the attributes. Whether classically with loop, modern with VALUE command or via a class method: ABAP needs, relatively speaking, many commands for the performance of a lookup.

With AMDP, the coding looks completely different:

Figure 3: AMDP Lookup

Why is the AMDP coding so much leaner? Since we are moving directly on the database, no prefetch or other internal tables are needed since there is no traffic between the database and the application server. The data can be connected directly.

AMDP IF Statement

ABAP programs often consist of many branches through IF statements. This approach allows many different cases to be intercepted and different responses to be made. Thus, data for a field can come from several tables that are processed in different orders. The following coding is intended to map the following logic:

1. Search for header data
   1. If header data is not found, set dummy values for customer number and city
2. Search by city via customer master data
   1. If data is not available, then look in alternative master data table, otherwise set dummy
3. Depending on the third letter of the city, determine the region

In the following coding, we have omitted the filling of the internal tables.

Figure 4: IF with ABAP

An AMDP implementation of the logic often tries to adopt the exact LOOP and IF structure of the ABAP coding. However, the use of imperative flow logic in SQLScript or AMDP is not optimal and should be avoided.

A better approach is to rewrite the used request so that it can be executed directly with sequential SELECT statements and temporary tables. Again, we have shortened the coding around the filling of the temporary tables.

Figure 5: “IF” with AMDP

By using CASE, COALESCE or IFNULL and multiple joins, we can implement the nested query directly in a SELECT statement. The further logic on the derived city field is outsourced to another temporary table.

This example shows very clearly that a 1:1 translation of the ABAP code is not a suitable approach and, therefore, a basic understanding of SQLScript is essential for the creation of AMDP scripts.

AMDP formatting

A major challenge in harmonizing data lies in manipulating it to clean up the contents of that data. This often involves modifying strings or strings so that they fit together from different source systems. Typical operations are:

• ALPHA conversion
• Capitalization
• Filtering out unauthorized characters
• Space removal

With ABAP, all these activities can be realized very conveniently using standard functions, since ABAP makes it easy to iterate over and check the individual characters of a string.

As in the previous example, it is not purposeful to translate this logic 1:1 with AMDP. At this point, it is essential to be familiar with the given SQLScript functions for string manipulations, as the following figure shows:

Figure 6: Formatting AMDP

The very powerful regular expressions are worthy of mention here. Using its own syntax, highly complex string manipulations can be realized and connected with SQLScript functions. For example, the list of allowed characters can be written to a declared variable via a table and then used for negation in the regular expression.

AMDP Analytical Functions

AMDP shows its true potential when applying analytical functions to big data. For this, let’s look at the following example: We have a table with invoice information (customer, date, item, number of items). Now, we are interested in two things:

1. What is the total number of materials per invoice?
2. Did the customer buy more or less with the next invoice?

The following figure shows a possible implementation with ABAP. It should be mentioned that there is a wide range of implementation possibilities with COLLECT or VALUE.

Figure 7: Analytical with ABAP

In this example, too, the ABAP coding consists of a large number of commands and IF queries. It is necessary to check whether the next row of the table exists and whether it belongs to the customer, as well as to react accordingly.

As with the other examples, an implementation with AMDP looks completely different.

Figure 8: Analytical with AMDP

First, the source data is aggregated directly via GROUP BY and thus the number of all invoice items per invoice is determined easily and efficiently. In the second part, the analytical function LEAD is used to determine the value on the next line. Here the PARTITION BY command determines how the window is defined: In this case, via the customer number.

AMDP or ABAP?

After these examples, however, the question remains as to which of the two technologies should be used and when. The various examples showed that a direct translation from ABAP to AMDP should be avoided.

To help you decide, we offer a series of questions below to help you find the ideal solution. The first and most important step is to evaluate and understand the exact requirements of the coding. Both ABAP and AMDP have their strengths, yet, as is often the case, their weaknesses, depending on the use case.

• How large is the data volume?
  • ABAP can even be faster for small amounts of data
• How is the support set up or the SQLScript knowhow?
  • ABAP is a very well understood language in the SAP environment
• Are there requirements that make AMDP not possible?
  • Use of standard ABAP classes or function modules
• Data processing complexity
  • AMDP provides powerful analytical functions with very high speed

Summary

There is no golden rule or derivation for deciding which technology to use. Each development requires an analysis of the requirements and consideration of whether ABAP or AMDP is better suited. AMDP enables a new way of modeling and can provide great performance improvements, however, especially for small amounts of data, ABAP can be faster and more support friendly.

This challenge should be seen as an opportunity to realize optimal and future-proof developments on the HANA-based systems.