ABAP on HANA - Use Cases

Introduction to ABAP on HANA

Through HANA, SAP has brought forth a high performing multi faceted appliance with rich analytic computational capabilities, in-memory hardware, enhanced compression technology, geospatial capabilities, Text analytics and predictive analytics, to name a few. With so powerful a back-end, the application layer too had to be revised to fully leverage the enriched capabilities of HANA. CDS Views, AMDPs, and enhancements to existing Open SQL are the various available solutions which help in achieving Code Push Down – transfer the data intensive logic to the database resulting in better performance. AS 7.4 or above is the required version of application layer on a HANA database for the features mentioned throughout the document to work.

Introduction to CDS Views:

CDS is one of the advanced features introduced by SAP in ABAP release 7.4 SP5. Through CDS, one can define views in the ABAP repository from ABAP Development Tools (ADT) using SQL DDL syntax. With an advanced feature set and domain specific annotations made available, CDS offers a simple solution for to realize code push down from ABAP Layer.

Introduction to AMDP:

ABAP managed database procedure is a simple ABAP class method containing database-specific procedure coding. The code within the method is pushed to the database layer and executed within the database. Thus significant performance improvements of data-intensive processes can be achieved by code push-down from the application server to the database server. This reduces the number of data transfers and the amount of transferred data between both servers.

Introduction to Open SQL Enhancements:

Open SQL has now been enhanced with certain features and made close to SQL-92 standard such that database level computations and code push down is achieved with ease. This is done through Group functions, arithmetic, string & conditional (CASE) expressions in Open-SQL Construct.

CDS Views in detail

To fully leverage the high performing capabilities of SAP HANA for application development, SAP has introduced a new mechanism to carry out data modeling, known as core data services (CDS). With CDS, data models are defined from the application server but are executed on the database resulting in an optimal code push down. CDS also offers capabilities beyond the traditional data modeling tools, including support for conceptual modeling and relationship definitions, built-in functions, and extensions. Originally, CDS was available only in the design-time and runtime environment of SAP HANA. Now, the CDS concept is also fully implemented in SAP NetWeaver AS ABAP, enabling developers to work in the ABAP layer with ABAP development tools while the code execution is pushed down to the database.

What is CDS?

CDS is an SAP framework for defining semantically rich data models, called CDS views. It is defined using SQL-based data definition language (DDL) which is based on standard SQL with advanced features like associations and annotations. CDS goes beyond the traditional querying capabilities by achieving complex calculations & manipulations with ease.
CDS views are quite similar to the ABAP repository views defined using ABAP layer. However, CDS allows defining basic views to achieve analytics and aggregations and later extend them to create much more complex artifacts making it more feasible with the complex business environment. While creating a CDS view from ABAP layer, a corresponding DB view is created in HANA with the name provided in the CDS editor. This DB view can be accessed just like any other information model in HANA database. Also, CDS offers combining result set of two or more queries into a single result set using UNION operator, ability to switch on automatic client handling using the annotation @ClientDependent:true. Also, it enables handling buffering of CDS entities with ease.

CDS was initially designed for native SAP HANA application development. With AS 7.4 SPS 05, the CDS concept was later made available in ABAP. While HANA based CDS runs only on SAP HANA database, ABAP based CDS runs on any database which is certified by SAP.

Advantages of CDS Views:

1. Reusability of database artifact.
2. Large Feature Set – Example: Associations.
3. Domain Specific Consumption of Data models – Example: Annotations.
4. Client handling can be achieved in CDS.

Limitations of CDS Views:

• Only 1 result set can be returned from a CDS View.
• Only 1 independent process can be carried out at 1 time. (1 query or a union/join of related queries)

AMDPs in Detail

With the dynamic in-memory capabilities of HANA built in with advanced hardware, calculations are extremely faster when carried out at DB level. Add to this, the parallel execution of multiple queries in one go. Using SQL and all of its possibilities is one example of this. But this has limitations. A SQL statement always has one result set and the calculation must be done in one single step. If you have complex calculations which you want to execute on the database you need other possibilities. AMDPs provide a framework for effectively taking advantage of both code push down and utilize parallel execution feature of HANA.

What are AMDPs?

ABAP Managed Database Procedures are a new feature in AS ABAP allowing developers to write database procedures directly in ABAP. The implementation language varies from one database system to another. In SAP HANA it is SQL Script. Using AMDP allows developers to create and execute those database procedures in the ABAP environment using ABAP methods and ABAP data types. AMDP method is a part of a general ABAP class just like any other class defined in the class builder. However, an AMDP method needs to be defined with the standard interface – IF_AMDP_MARKER_HDB to let the compiler know that it’s an AMDP method and therefore code execution must be pushed to the database.

AMDPs allow developers to create and manage stored procedures directly in ABAP, while they are executed on the database level. AMDPs need to be defined from ADT in HANA studio using the general class builder as done using GUI. Implementing methods within the interface implies the method being an AMDP and therefore be executed at the database. Any method defined within the same class but not within the above interface is treated as a normal ABAP method and is executed at the application layer. Developing and implementing of AMDPs is very much the same as any method belong to a global class.

AMDPs need to be implemented in SQLSCRIPT which is the native language for HANA DB. This helps in implementing various powerful features of SQLScript which are not yet available in ABAP. By strictly following the guidelines and golden rules suggested in SQLSCRIPT, the performance of an application can be optimized, taking full advantage of the advanced capabilities of HANA .

Advantages of AMDPs

1. Complex calculations involving several independent steps/processes can be performed in one AMDP method.
2. Ease of implementation in ABAP.
3. Powerful features of native SQL such as currency conversion and CE functions can be leveraged.

Disdvantages of AMDPs:

1. No automatic client handling
2. Only Exporting, importing and changing parameters are allowed
3. Methods with returning parameters cannot be implemented as AMDPs
4. Method parameters have to be tables or scalar types
5. Method parameters have to be passed as values

Open SQL Enhancements

In order to achieve code push down from application layer, enhancements have been made to existing SQL in terms of expressions & group functions. Along with these, the SQL syntax has been modified to enable dynamic data declaration, escaping of host variables etc thus making it more robust.

What are Open SQL Enhancements?

To understand the various Open SQL enhancements made to achieve code push down, it is important to understand the limitations of SQL before 7.4.
Below is a list of various limitations of SQL before:

1. Limited Join Types available
2. No Expressions in select
3. No Union & Union all

To address above the above shortcomings and also to further enhance the ABAP capabilities, Open SQL has been made more advanced and robust. Also these enhancements are made keeping HANA in mind such that all advantages offered by HANA are leveraged.
Following are the elements in the New Open SQL syntax:

  a.  Escaping of host variables with “@”.
  b.  Right Outer Join made available.
  c.  Comma separated element list in selects.
  d.  SQL expressions – Arithmetic expressions, CASE expressions, COALESCE etc.
  e.  Aggregate functions – SUM, MAX, MIN, COUNT etc.

Following are the various diverse expressions in SQL selects:
   i.   Arithmetical expressions: + | - | * | DIV | MOD | ABS | FLOOR |CEIL
   ii.  Concatenation of character columns with &&
   iii. Conditional expression with CASE
   iv. Casting with CAST
    v.  Coalesce

Using the Advanced Open SQL judiciously allows in achieving pushing down the complex calculative and manipulative logic to the database and thereby avoids unnecessary loops in ABAP code. Owing to speed performing capabilities of HANA in handling huge volumes of data and enriched aggregation abilities, this would result in boosting the performance by considerable amount of time.

Advantages of Open SQL Enhancements:

ABAP code will remain database agnostic and run on any database in a similar fashion.
You implicitly take advantage of all optimizations that have been achieved at the database level.
All default performance optimizations like use of buffer and client handling are automatically taken care of.

Disadvantages of Open SQL Enhancements:

1. Statements are compiled in strict mode making it necessary that entire query be abided by the new syntax.
2. Only simpler calculations could be performed in Open SQL. For complex aggregations, CDS or AMDPs needs to be used.
3. Only single result set could be returned. 

IDA Classes in detail:

What are IDA Classes?

The implementation of in-memory features of a database can lead to significant improvements in processing of large quantities of data. Also, less wait time for data display and much faster interactive operations and aggregations enables smooth processing of business data with ease. To make the great advantages usable for business applications in the ALV environment as well, SAP offers a special version of the List Viewer, the SAP List Viewer with Integrated Data Access. SAP has used the concept of persistence storage and paging to reduce the wait time for the list display. Also, additional operations on the output such as aggregations, filtering, sorting etc is made more reflexive and easy to implement.

Advantages of IDA Classes

1. Leverage in-memory capabilities of HANA, without having to write complex code to realize this.
2. Minimal amount of data transfer – No explicit select query is required
3. End users can continue to work on the familiar ALV interface. The standard functions (also ALV services) and toolbar options are still available in ALV with IDA.
4. Advanced UI features like Sorting, filtering, aggregations etc. are available.

Use Cases

Use Case 1:

The Equity Group Changes for JOA is an existing program which gets the Equity Group Change History based on the selection screen entries and displays the changes log in an ALV. The changes are primarily related to change in partners (Addition/Modification/Deletion), their equity shares and changes in non-operated share. The program was reported to have performance issues for huge volumes of data (around 10 Million records and (8+ hours) on execution, eventually timing out even in background run and had also dump reported due to memory issues as the user was giving a big date range.

Selection Screen:

Analysis:

1. Most of the high data sensitive queries were done at the application layer.
2. The program uses “select *” queries to pick up the changed documents from CDHDR & CDPOS.
3. A standard Function Module CHANGEDOCUMENT_PREPARE_DISPLAY was used for formatting.
4. There were many other series of “select *” queries on several tables based on changed data saving the data in to respective internal tables that were used to fulfill the requirement.

Levaraging HANA:

1. For points 1, 2 & 3 above, we have developed a CDS View and consume the same in the report program.
2. For point 1 & 4, we have created an AMDP method and called this in the report program. 

Relevant Code Snippets:

Existing Code:

Changed Code:

CDS Definition:

Consuming CDS in ABAP:

Existing Code:

Changed Code:

AMDP Definition:

Advantages achieved through these changes:

• Using CDS Views and AMDPs ensured that there was a huge boost from performance point of view. The report would now execute in approx. 8 to 10 minutes after applying these changes.
• Since the processing was moved from Application layer into the Database layer, the memory issue was no longer reported for this. 

Use Case 2:

Requirement:  to get the sum of revenues (Outgoing Wire Transfers/Revenue/AP) and count of activities transaction wise based on Account id.

Leveraging HANA: This was achieved using Advanced Open SQL.

Advantages achieved through these changes:

By clever use of Advance SQL, this has clearly eliminated loops at the Application layer.

Logic explanation:

In the above example:

1. SUM(PB~KWBTR) calculates the total amount.
2. COUNT(*) gives the number of entries satisfying the WHERE condition in SELECT.
3. SUM(CASE) is used to calculate the conditional total. In the above select, the sum of amounts having Accounting ID – 01576 or 01659 is returned. 

Use Case 3:

Requirement: Get the JV details on a given date for a partcicular venture and location.

CDS View Definition -

Consuming CDS View in ABAP using IDA Class -

Advantages achieved through these changes:

1. By using IDA, the Code Push Down model is optimally supported using the in-memory database and considerable      performance can be achieved for vast amount of data.

2. By using CDS, there is no select query required to get the data into application layer. IDA being a Persistence class, & effective     usage of the concept of paging, transfer of large amount of data is avoided.  

Source: scn.sap.com