In the last issue, we talked about the hardware and software innovations which led to a new product being released by the famous company based in Walldorf, namely SAP HANA . Now let us talk about how all these innovations, in addition to SAP HANA Studio, an IDE (Integrated Development Environment ) from SAP, help us reduce the implementation time of the projects and, of course, help us reduce the execution time of HANA-based applications.
To take advantage of the processing power offered by HANA , applications and at least some of the application logic (the entire logic, ideally) have to be executed or performed at the database level. This is important for complex calculations (SUM , AVG , COUNT , etc. ) that use a large data volume and which usually take a long time to be completed. In this way, we are not required to transfer large amounts of data, which would reduce performance considerably. The process of code "transfer" from the application layer to the database layer is called Code Pushdown or the Code- to- Date paradigm. In other words, we do not transfer data from the database to the application, in order to be processed. Instead, we transfer the application logic to the database.
Until now, SAP applications used the Data-to-Code principle, but the applications which are optimized or developed specifically for HANA use Code- to- Data . With this new paradigm aboard, the amount of transferred data is kept to a minimum . See Fig. 1.
Fig. 1 Code-to-Data and Data-to-Code
SAP ERP Systems (Enterprise Resource Planning) can run on multiple databases, from different manufacturers, such as IBM DB2 , Oracle , Sybase ASE, etc. Due to the system architecture, developers do not have to take into consideration the underlying database because there is a software component called Database Interface that " translates" the CRUD instructions ( Create, Read , Update and Delete ) of the applications into specific instructions for each database . This means that the database is seen as a black box, database details are not known by programmers, and all applications are running on SAP systems regardless of the database.
However, if we want some of the application logic to be executed at the level of the database layer, HANA in our case, such details must be known. If we want to benefit from what SAP HANA offers and achieve high performance, the database should become a White Box. Therefore, we would need to know what happens inside.
In the past, ABAP programmers used the ABAP Workbench, which is based on SAP GUI, for developing, modifying and testing various programs. The need to integrate multiple programming languages like ABAP, SQLScript (used HANA), HTML , CSS , JavaScript (the last three are used for SAP UI5 ), SAP, led to the creation of a new IDE, namely SAP HANA Studio, which is based on Eclipse 3.8. HANA Studio is also used for database administration purposes.
In HANA Studio, administrators and developers can connect to multiple servers simultaneously, with different users. This is beneficial when working on different projects for different customers. In the figure below we can see how we can connect from HANA Studio to several HANA systems (SID - System ID ) using different Users (SYSTEM , SAP_SUPPORT , HANADB , etc. ).
Fig. 2 Different systems with different users in HANA Studio
ABAP Managed Database Procedure - is a new feature in AS ABAP (Application Server ABAP) that allows us to write stored procedures directly in ABAP. These procedures can be viewed as functions stored and executed on the database. The language used for them is SQL Script. SQL Script is written in the class methods and performed in HANA. In order to implement AMDPs in a class, that class must contain the IF_AMDP_MARKER_HDB interface . See Fig. 3 .
Fig. 3 Class definition
Below, we created a SAP HANA stored procedure (FOR HDB - HANA Database) using the SQLScript language.
Fig. 4. Class Implementation
AMDPs were introduced to help programmers observe the Code Pushdown or Code-to-Data principles.
Core Data Services is a collection of domain- specific languages (DSLs) and services for the definition and consumption of the data models. See Fig. 5.
These languages are:
• Data Definition Language (DDL) - for data model definition
• Query Language (QL) - for reading data
• Data Manipulation Language (DML) - for CRUD operations
• Data Control Language (DCL) - for authorization and consistency checks
Fig. 5. Core Data Services
In other words, Core Data Services are an improvement brought to SQL for defining, handling and using data models in HANA applications .
The CDS Views are defined in the DDL source files (R3TR DDLS ) using the "DEFINE VIEW" instruction.
• View Entity: zcdsv_open_sap_w4u2_01
• The associated SQL View: ZCDSV_W4U2_01
The Views are representations of the SQL View Entities in the database. They are generated / updated / deleted automatically ABAP Dictionary.
These CDS Views can be created only by using ABAP Development Tools in SAP HANA Studio.
Suppose Siemens AG has a daughter company, Siemens Logistics, which is engaged in the delivery of parcels between the Siemens companies. Therefore, in the database of the ERP system, there would be a DB table with information about the parcels that were shipped in the fiscal year 2015. For simplicity, we created a table that contains only five fields. See Fig. 6.
The data for the fields were generated randomly. For the Package Weight (PACK_WEIGHT) field we generated values in the range of 1 - 100. The Unit Weight (WEIGHT_UNIT) field takes only 3 values, namely Kilogram (KG), Ounce (OZ) and Pound (LB). For the Delivery Price (DELIVERY_PRICE) field we generated values between 1 and 10,000, and for the Price Currency field we have the following values: USD (US Dollar), EUR (Euro), JPY (Japanese Yen) , GBP (British Pound), RON (Romanian Leu), CAD (Canadian Dollar), AUD (Australian dollar) and CHF (Swiss Franc). We generated 2 million records, in total.
Fig. 6 Table – Delivered parcels
At the end of the fiscal year, we, the developers, must design and implement a program that returns the total amount for the delivery price in USD, for all the parcels. This means that we have to select all data from the database, make the USD conversion and calculate the total amount. Using an SAP system with an Oracle database, the program execution took 14.42 seconds.
In a SAP system with a HANA database, we can define a CDS View and, through this View, we can observe the View Code- to- Date principle, thereby greatly reducing execution time.
The source code of the CDS View looks like this:
@AbapCatalog.sqlViewName: 'ZCDS_USD_V'
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Package delivery costs in USD'
define view Zcds_Pack_Usd as select
from zcj_siemens_pack
{
key pack_oid,
@Semantics.currencyCode
price_curr,
@Semantics.amount.currencyCode: 'price_curr'
delivery_price,
case price_curr
when 'EUR' then cast(delivery_price as abap.fltp ) * 1.1375
when 'JPY' then cast(delivery_price as abap.fltp ) * 0.009
when 'RON' then cast(delivery_price as abap.fltp ) * 0.25
when 'CAD' then cast(delivery_price as abap.fltp ) * 0.77
when 'GBP' then cast(delivery_price as abap.fltp ) * 1.44
when 'AUD' then cast(delivery_price as abap.fltp ) * 0.76
when 'CHF' then cast(delivery_price as abap.fltp ) * 1.03
else delivery_price
end as delivery_price_usd
}
group by
price_curr,
delivery_price,
pack_oidAfter activating the source code, we obtain a View, with the following fields:
Fig. 7 The new View with the converted prices
Having a field with the USD price, we can write the following query to calculate the total amount: "SELECT SUM (DELIVERY_PRICE_USD) ...".
When using an SAP system with a HANA database, the program execution took 0.04 seconds. By calculating the ratio of the 2 execution times we get a 360.5 factor. We achieved this performance because the calculation of prices in USD was performed at the database level. We did not have to transfer the 2 million records in the application layer. Instead, we transferred the conversion logic to the database, observing the Code-to-Date principle.
As we see, simply changing the database with a HANA database does not necessarily lead to high performance. What is also required is the conversion of the applications which have problems related to execution times. In addition, the price for migrating an old system to a HANA database must also be considered, because, as we know, all new technologies have attached costs.
by Ovidiu Mățan
by Vasile Boris
by Vlad Haiduc
