Jonathan Andre is a lead ABAP developer at IT Partners. Jon is also the President of Andre Technical Consulting LLC located out of Centreville, Virginia. Jon has over 6 years experience with SAP, with a concentration on ECC SD, MM, and IS-OIL modules. Jon’s focus has been in the areas of ABAP OOP and ABAP Web Dynpro. He can be reached at [email protected]
As we described in a prior blog The ABAP Developer Road Map to SAP HANA, with the advent of SAP HANA, there has been a paradigm shift in the way business applications are developed. The rule-of-thumb is simple: Do as much as you can in the database to get the best performance. This was coined as “Code Pushdown” by SAP. So far we have looked at CDS Views as a way to achieve Code-Pushdown in the blog Don’t Try Coding ABAP Core Data Services Without Reading This First. In this blog, we will continue to examine Code-Pushdown Patterns, specifically ABAP Managed Data Procedures (AMDP).
An ABAP Managed Data Procedure (AMDP) is a tool that can be utilized to create HANA database procedures that are designed and coded on the ABAP server. In the hierarchy of code-pushdown techniques, AMDP ranks lowest on the scale of preference behind Open SQL and CDS Views. While AMDPs are not the preferred approach when coding your code-to-data approach, the technology does offer some unique, albeit seldom used, approaches to interacting with the HANA database. AMDPs also have the added benefit of being transportable and are easy to adapt to for ABAP coders who need functionality Open SQL and CDS Views do not offer. (Note: AMDP code MUST be created and maintained in the Eclipse editor. Attempting to access this code from SAP GUI will not even allow the code to be switch to change mode after it implements the marker interface – more on this later)
We will go over some of the capabilities AMDPs have to offer, as well as how they can be combined with CDS to create powerful, yet easy to use tools.
Introduction to ABAP Managed Data Procedures (AMDP)
AMDPs are implemented as an addition to traditional ABAP Object-Oriented programming. A user creates a class just like any other class, but to implement the AMDP they must execute two important steps:
Let’s look at the steps:
Step#1: First, they must ensure that the class implements the interface IF_AMDP_MARKER_HDB. Implementing this interface does not add any interface methods, but simply flags the code as an AMDP class.
Step#2: The Method that actually implements the AMDP procedure code must have some specialized method additions to identify itself as an AMDP. These additions also provide some information about the database and language the method should be implemented.
The Method Additions are described below:
Below is a very basic example of an AMDP method. This method simply selects the 200 records from database table SNWD_SO, with no selection conditions. There are a few features to pay particular attention to, which have been highlighted and numbered.
Creating Your First AMDP
- The INTERFACES IF_AMDP_MARKER_HDB statement comes within the PUBLIC SECTION. This may or may not be obvious to you, even if you have worked with OOP on ABAP before. Since this interfaces insertion was usually handled by the Class Development Tool in SE24, this is something that should be noted.
- When defining your exporting table types within your AMDP class, note that the TYPE TABLE OF statements are not allowed. You will therefore either have to use a DDIC table type or use a TYPES statement and declare you table type prior to your method declaration (as I did in 3). Also note that all IMPORTING and EXPORTING variables must be PASS BY VALUE! Considering that this code will be executed on the HANA DB, it makes sense you cannot pass by reference to another system.
- In addition to not allowing TYPE TABLE OF statements in the method declaration, the AMDP classes are fairly finicky in general. You can assume that HANA has no access to DDIC types in general, so you should attempt to operate with basic ABAP types (C,X,P, I, etc) when possible.
- This is the boilerplate text I described in the beginning of the blog when defining an AMDP method. It seems that SAP would like to expand the functionality of AMDPs in the future to support other database types, but as of now this is the only text you will for these methods (other than for CDS Table Functions, which will be covered later). This statement describes the database and the language.
- You can assume that any code within your AMDP method has NO KNOWLEDGE of any DDIC types. To specify the use of a table or view, you need to call out the tables that will be in used as part of the method header. USING <table_name> will allow you to SELECT, UPDATE, INSERT, or MODIFY <table_name> within your managed procedure call.
- As stated above, the body of the method must be written in SQLScript. SQLScript does share some similarities with ABAP at a glance, but it is a different language. Note that statements in SQLScript are terminated with “;” and not “.”
The above basic example would not be justification enough to use an AMDP, since this could obvious be easily accomplished using either Open SQL or CDS Views. So how do we know what is the best Code-to-Data or Code-Pushdown Technique?
Below is a cursory set of Guidelines. This is not meant to be a definitive guide, but rather a starting point.
Only ONE result set can be returned from a CDS View
CDS views amount to least amount of coding with the ability to be reused in multiple objects across developments. In another word, it’s a database artifact that can be consumed across objects/applications
It uses IDA class of SAP GUI hence much faster (kind of paging technique )
Interdependent SQL queries which will be used across applications (programs/object) are the best example for choosing CDS Views
Independent SQL Statement those are not often used in other objects
MULTIPLE result sets are needed
Powerful features of native SQL such as currency conversion and CE functions can be leveraged.
If the SQL queries are for the specific object and won’t be needed elsewhere (not reusable)
OK, The basic example would not be justification enough to use an AMDP. BUT a reason to use AMDP, however, is its ability to access Native SQL Script and internal HANA functions. I will replicate some code from the previous blog while correcting a cardinal coding sin: ignoring currency conversion. In the following code, I will attempt to address this issue, while also pointing out some of the other perks of using AMDPs.
- As a bit of an ABAP OOP refresher, recall that a class method can be called from a static context. This allows our AMDP method to be called without having to create an instance. If your AMDP class does not make user of instance attributes, this may be a better route to take, essentially turning any calls to this method into a function module call.
- A nice feature of AMDP classes is that you can define variables on the fly. SELC_TEMP_SUMMARY will automatically take on the types and structure returned by the SELECT.
- This conversion works for functions as well. The return value of CE_CONVERSION is variable depending on what value is passed, so TEMP_SUMMARY will take on the structure necessary to hold what is returned. Also, note that to reference an internal objects values, you must prefix the variable name with “:”. In this scenario we use the values from :SELC_TEMP_SUMMARY as input to CE_CONVERSION, and stored the generated output in TEMP_SUMMARY.
- Here we find another useful feature of SQLScript/AMDP. We can perform SELECTs on our generated “internal tables” as if they were database tables, and store the results. The select uses the TEMP_SUMMARY (with the required “:” in front) and pulls back only the fields required to populate our returning table ET_SUMMARY.
Integrating An AMDP into your ABAP Code
With our class built, we can now use a simple ABAP program to pull back pertinent information from SNWD_SO and display it. The sample program below will use the class method (using a static method call) to pull back the records from SNWD_SO with the gross amount converted into British Pounds (‘GBP’). These records will be displayed alongside the unconverted gross_amount values, so we can compare the two.
- As OOP refresher, recall that since we defined our TT_SO_SUMMARY type in the public section ZJON_AMDP_CURRCONV class, we can directly reference it from our program with the above syntax. This prevents type mismatches and also prevents you from having to create this TYPE again in your program.
- As mentioned above, since we defined this method as a “CLASS-METHODS” type method, we can reference our method without creating an instance. This allows us to call this method in the manner resembling a normal function call.
- Note that we are specifying that we want the currency of the GROSS_AMOUNT converted from its listed table currency (currently all in ‘USD’) into ‘GBP’. We can obviously reuse this method and pass in any target currency we desire.
Using An AMDP to Retrieve Multiple Results Sets
Some other abilities that set AMDPs apart from Open SQL and CDS Views is their ability to retrieve multiple result sets and provide a reusable way to mass insert or update records.
The next example will illustrate these capabilities in one AMDP method. This scenario will assume we wish to convert all sales orders from SAP within the SNWD_SO table to ‘EUR’. To do this, we want to convert the gross_amount, net_amount, and tax_amount to ‘EUR’, in addition to updating the record to the correct currency code. We will also return a before-conversion table and an after-conversion table.
- Notice that I needed to list both tables within the USING clause. Even though I am only using SNWD_BPA in a join condition to filter by company name, it still needs to be listed in the method header.
- The first returned table is ET_BEFORE. This one table would be the limit for either an Open SQL statement or a CDS View.
- After modifying the records pulled back from the SNWD_SO table, we are able to modify SNWD_SO database records based on a matching NODE_KEY.
- We now create our second return table, which is derived by performing another select on the now updated SNWD_SO table. This is a capability unique to AMDPs, and proves useful in this scenario and other scenarios where multiple sets of data would need to be retrieved in unison.
Let’s use this in an ABAP Report. The report source created to utilize this class is straightforward and closely resembles the original AMDP currency conversion program. I have highlighted the fact that this program is able to pull back to different sets of data from one statement. We could run this program for different companies, or different currencies without modifying the class.
The resulting output displays both the before and after for all ‘SAP’ sales orders in the SNWD_SO table
The examples above show that AMDPs have a lot to offer in terms of really taking advantage of HANAs features. As far simplicity goes however, AMDPs simply do not offer the ease of use offered by CDS or Open SQL. Thankfully, SAP introduced another technology called CDS table functions, which provide a familiar and reusable method for accessing the power AMDPs provide.
Using CDS Table Functions
CDS table functions are essentially wrappers for AMDPs that make them more user-friendly for developers. By using a CDS table function, users are able to access AMDPs with CDS View-like SQL syntax, and my not even realize that an AMDP is being used behind the scenes. The next example will illustrate how CDS table functions and AMDPs interact, in addition to demonstrating how to call a CDS table function.
Lets, build the CDS Table Function (see below)
- CDS Parameters are passed implicitly to the AMDPs methods parameters that implements the CDS table function. Therefore, if you want the table function to have importing parameters, this is the place to do it.
- The CDS table function is strictly bound to a specific method of a specific class. You can name that method here before you implement the class or method, but this CDS table function will not operate until this exact method is implemented.
With the CDS Table Function created, the method and class with matching name can be created.
- Note that the implementing method of the implementing class for the CDS table function MUST be a CLASS METHOD. In addition, the method declaration must include the syntax FOR TABLE FUNCTION <your_cds_table_function>. This is necessary to tie the method to the CDS table function, as well as providing the importing and exporting parameters, since the importing and exporting parameters and supplied implicitly from the CDS table function.
- The implementing method also requires a subtle syntax change. Instead of using DATABASE PROCEDURE as we have used up until this point, the syntax for this method is DATABASE FUNCTION. Leaving this change off will prevent your code from compiling.
- Even though the parameters CURRENCY and CONV_DATE are defined nowhere in the CLASS or METHOD, the method already knows and can use the parameters that were defined in the CDS table function.
- Lastly, a method that implements a CDS table function MUST return exactly one table as returning value. While all AMDPs can have a returning parameter (as of ABAP 7.50), it is required for this table function.
To call a CDS Table Function, simply perform a SELECT as you would for any ABAP CDS entity. Note that the code is using the AMDP, yet does not need to mention the class or method anywhere.
The output result is as expected, but was achieved using 10 lines of code
AMDPs provide powerful abstractions that enable ABAP developers to combine the benefits of the high-speed in-memory execution of SAP HANA with the well-established ABAP runtime and lifecycle model. Along with CDS Views, CDS Table Functions, and OPEN SQL, allow ABAP developers to renovate their existing assets and create new and innovative applications for SAP Business Suite on SAP HANA without losing the platform-independence of ABAP.