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.
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.
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.
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.
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.
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.
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 .
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.
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:
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.
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.
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.
b. For point 1 & 4, we have created an AMDP method and called this in the report program.
Advantages achieved through these changes:
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:
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.
A.CDS Views:
a. Reusability.
b. Single result set.
c. Advanced features to be used as a part of requirement (annotations & associations)
B. AMDPs:
a. Multiple result sets.
b. Complex calculations involving more than one step/process.
c. SQLScript features which are not available in Open Sql are required.
C. Advanced Open SQL:
a. Single result set.
b. Relatively simpler calculations.
c. Temporary storage (Internal memory. No reusability).
D. IDA Classes (for ALV display)
a. Use with reusable database artifacts (DB Tables, Views, and CDS Views).
b. Large amount of data to be displayed in ALV.
Summary
Open SQL, core data services, and ABAP-managed database procedures 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. Based on the requirement and taking the guidelines to choose an approach into consideration, it is advised to proceed with the ABAP development implementing the relevant feature of ABAP resulting in boosting the performance of an application and fully benefit from the enriched capabilities of HANA. Starting with simpler Open SQL enhancements in Version 7.4 SP02, SAP had very powerful offerings in SP05 in terms of CDS Views & AMDPs. Furthermore, these were made much more robust and efficient in SP08 with very useful enhancements to the existing frameworks. It can further be expected to introduce much advanced concepts, easy to use & highly efficient solutions in AS 7.5.
Related Content
http://sapinsider.wispubs.com/Assets/Articles/2014/October/SPI-where-ABAP-meets-SAP-HANA
http://scn.sap.com/docs/DOC-51612
http://scn.sap.com/community/abap/hana/blog/2014/09/26/code-push-down-for-hana-from-abap-starts-with...
https://help.sap.com/saphelp_nw74/helpdata/en/ef/eb734c8e6f41939c39fa15ce51eb4e/content.htm
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
5 | |
3 | |
2 | |
2 | |
2 | |
2 | |
2 | |
1 | |
1 |