This page is a hand written note from (and probably to) an SAP HANA novice who is trying to remember and understand the new terminologies.
Please check SAP ABAP for HANA Tutorial for multiple articles in detail on ABAP for SAP HANA.
Wiki says: SAP HANA, short for “High Performance Analytic Appliance” is an in-memory, column-oriented, relational database management system developed and marketed by SAP SE. HANA’s architecture is designed to handle both high transaction rates and complex query processing on the same platform.
Multicore architecture for CPUs and 64-bit address space innovation in hardware enabled SAP to design SAP HANA. Similarly, columnar data storage, improved data compression algorithms and insert only approach innovation in software have contributed to the SAP HANA evolution.
Combinations of Online Transactional Processing (OLTP) and Online Analytical Processing (OLAP) using the same database instance application approaches are supported by SAP HANA.
Enhancements in SAP NetWeaver 7.4:
Release 7.4 offers state-of-the-art support for application development optimized for SAP HANA, therefore SAP HANA favors Release 7.4. Open SQL is a DB abstraction layer that defines a common semantics for all SAP-supported databases. Usage of CDS views in the FROM clause of query statements and Character-like literals in the SELECT list of query statements are provided as recent Open SQL enhancements in SAP NetWeaver Application Server ABAP 7.4. CASE statements in the SELECT list of query statements and COALESCE functions in the SELECT list of query statements are new kind of conditional expressions featured by the recent Open SQL enhancements.
Read more: New Age SQL ABAP 7.4
Good thing is all databases certified by SAP support the recent Open SQL enhancements (eg CDS). So if we really would like or really would have to consume native HANA artifacts or features that are not accessible with Open SQL or with CDS, we have to dig deeper and you have to consume them natively. The other good thing is if you need to make change in existing Open SQL statements to new Open SQL syntax and there are other Open SQL statements in your object, you need not change all. They are still valid.
Also Read: SAP HANA at Ground Zero
SAP NetWeaver 7.4, generally available since May 2013, is the version that is currently fully optimized for SAP HANA. It also facilitates the development of modern browser-based and mobile applications because of its integrated UI development toolkit for HTML5 (SAP’s adaptation of the HTML5 standard, known as SAPUI5) and SAP NetWeaver Gateway capabilities.
At the core of SAP HANA is the high-performance, in-memory SAP HANA database. It can manage structured and unstructured data, and supports both transactional and analytical use cases. As a traditional relational database, the SAP HANA database can function either as the data provider for classic transactional applications (OLTP) and/or as a data source for analytical requests (OLAP).
SAP HANA provides standard database interfaces such as JDBC and ODBC and supports standard SQL with SAP HANA-specific extensions. In short, SAP HANA is an RDBMS offering SQL interface and transactional isolation.
The latest release of SAP NetWeaver is optimized for SAP HANA and adds new capabilities to the developer’s workbench for cloud, mobile and social networking.
Columnar Data Storage:
A database table is conceptually a two-dimensional data structure organized in rows and columns. Computer memory, in contrast, is organized as a linear structure. A table can be represented in row-order or column-order. A row-oriented organization stores a table as a sequence of records. Conversely, in column storage the entries of a column are stored in contiguous memory locations. Only the relevant column is fetched for the query, thus reducing the amount of data to be processed.
With columnar data, operations on single columns, such as searching or aggregations, can be implemented as loops over an array stored in contiguous memory locations. Such an operation has high spatial locality and can efficiently be executed in the CPU cache. With row-oriented storage, the same operation would be much slower because data of the same column is distributed across memory and the CPU is slowed down by cache misses.
SAP HANA supports both, but is particularly optimized for column-order storage.
Columnar data storage allows highly efficient compression. If a column is sorted, often there are repeated adjacent values. SAP HANA employs highly efficient compression methods, such as run-length encoding, cluster coding and dictionary coding. With dictionary encoding, columns are stored as sequences of bit-coded integers. That means that a check for equality can be executed on the integers; for example, during scans or join operations. This is much faster than comparing, for example, string values.
Columnar storage, in many cases, eliminates the need for additional index structures. Storing data in columns is functionally similar to having a built-in index for each column. The column scanning speed of the in-memory column store and the compression mechanisms – especially dictionary compression – allow read operations with very high performance. In many cases, it is not required to have additional indexes. Eliminating additional indexes reduces complexity and eliminates the effort of defining and maintaining metadata.
SAP HANA dictionary compression is realized for column store tables by sorting dictionary with the distinct values of the column and each column uses an array of integer values that represent the positions of the actual values in the dictionary. When the table contains a huge amount of data that is frequently searched or aggregated and when the table contains many columns and typical queries access only a few of them then column store for table is recommended.
Read more SAP HANA from space level.
SAP HANA Transport Container (HTC):
With SAP NetWeaver 7.4, applications containing ABAP and HANA development entities can now be easily developed, updated, corrected and enhanced. As usually done for reasons of quality assurance, the different ABAP and HANA development entities have to be transported through the system landscape; typically from the development system to the testing, quality system, and then to the productive system. Here comes the SAP HANA Transport Container (HTC) into picture.
HTC is an ABAP development object which is required to integrate HANA repository content into the standard Change and Transport System (CTS). As of AS ABAP 7.4, HTC is seamlessly integrated into the Transport Organizer of AS ABAP and so integrating the HANA repository content into CTS. It ensures an efficient delivery process of applications built out of ABAP (say a method) and HANA content (say AMDP) or simply, ABAP for SAP HANA applications between SAP systems by means of the proven ABAP transport mechanism.
ABAP in Eclipse:
The ABAP Development Tools for SAP NetWeaver tie in perfectly with SAP HANA Studio and SAP’s in-memory technology by allowing highly productive application development on top of SAP HANA. The ABAP Development Tools significantly increases ABAP developer productivity through rich Eclipse user experience and flexibility, new capabilities for sophisticated source code implementation, task-oriented and test-driven business development. The ABAP Development Tools enables cross-platform application development by integrating ABAP and non-ABAP development tools into one Eclipse-based IDE. Built-in extensibility of the IDE through the established Eclipse plug-in technology enables you to benefit from the huge Eclipse ecosystem, develop on open platform and integrate new custom ABAP and non-ABAP tools. ABAP Project in ABAP Perspective in ADT for SAP NetWeaver serves as a central interface for communication between the Eclipse based development environment and the ABAP back end system.
Read more: Get accustomed to SAP HANA Studio/Eclipse.
Open and Native SQL:
Open SQL allows us to access database tables declared in the ABAP Dictionary regardless of the database platform that the R/3 System is using. Native SQL allows us to use database-specific SQL statements in an ABAP program. This means that we can use database tables that are not administered by the ABAP Dictionary, and therefore integrate data that is not part of the R/3 System.
As a rule, an ABAP program containing database-specific SQL statements will not run under different database systems. If your program will be used on more than one database platform, only use Open SQL statements.
Please note, all ABAP custom code would not show drastic performance improvement automatically. In order to take maximum advantage of SAP HANA, our custom code should be in compliance with the enhanced SQL performance guidelines.
Also Read: New Age Open SQL in ABAP 7.4
ABAP Test Cockpit (ATC):
ATC can be used to check the ABAP coding for potential functional regressions/issues and correct them (if necessary) before migrating to SAP HANA. ADBC (ABAP Database Connectivity) interface check in the Code Inspector should be done before migrating to SAP HANA to avoid functional gaps. Runtime Check Monitor (SRTCM) can be used to get additional runtime information for potential functional regressions check. PERFORMANCE_DB check can be used to check performance optimization potential before migrating to HANA.
The SQL Monitor (SQLM) can be used to capture the SQL profile of the ABAP system. SQL Monitor data can be exchanged between two systems by creating a snapshot of the SQL Monitor data, exporting it to the file system, and then importing it to the target system. SQL profile of the business processes in the production system can be captured because of the fact that SQL Monitor introduces only a minimal performance overhead.
SQL Performance Tuning Worklist (SWLT) allows correlating the results of an ABAP source code analysis with SQL runtime data. The report gives a list which might have potential issues. We should start investigating the ones which are at the top. We need to set a goal as to how much we want to optimize. Work on those set and check if the corrections/optimizations meet our self-defined or business defined requirements.
SAP List Viewer with Integrated Data Access (ALV with IDA) is based on the principle to select only the data to be displayed from the database and perform calculations, aggregations, and grouping on the database layer.
Code to Database Paradigm Shift:
Code-to-Data paradigm helps to improve the performance of data intensive ABAP coding because the in-memory capabilities of SAP HANA allow calculations to be performed on the database layer, which helps to avoid unnecessary movement of data. We have to consider the fact that SAP HANA and AS ABAP use different type systems when we follow the Code-to-Data paradigm using Native SQL. Performance can also be increased by using the new features in Open SQL, using AMDP and using view entities provided by the advanced view definition capabilities.
Core Data Services (CDS) views and AMDP (ABAP Managed Database Procedures) ABAP artifacts can be created and maintained via ABAP Development Tools for SAP NetWeaver.
Check this: SAP ABAP on HANA. Part II.
Core Data Services (CDS):
CDS is a mechanism to push down logic to database. We outsource this effective innovation to put the code and execute the logic in the database itself. So, in simple words, CDS helps us to run our logic in database.
Now let us look at the formal definition and explanations. “Core Data Services,” are a higher-order SQL that relieves application developers from low-level SQL coding for adding referential navigation by generating the required code automatically, and also forms the basis for unified data models in the SAP HANA context. The intention is for SAP HANA to be able to consume various data sources on the same semantic level regardless of whether they are delivered by an ABAP program or SAP BusinessObjects model. Core data services are included as part of SAP HANA extended application services, an application server that is shipped with SAP NetWeaver 7.4 SPS 05 and SAP HANA as of SPS 06.
More details: Q&A on Core Data Services.
CDS is a collection of domain-specific languages and services for definition and consumption of semantically rich data models. Data Definition Language, Data Control Language and Data Manipulation Language are included in CDS. Core Data Services (CDS) view can be consumed by using the Data Preview in ABAP Development Tools for SAP NetWeaver and using it as a data source in the FROM clause of an Open SQL query. Conditional expressions like COALESCE functions and CASE statement in the projection list can be used in Core Data Services (CDS) views. A field from the projection list of another CDS view used in the FROM clause; String constants and literal values and Aggregation functions over fields of ABAP Dictionary tables used in the FROM clause can be included in the projection list of a Core Data Services (CDS) view. We can use the static method use_features of class cl_abap_dbfeatures to check if Core Data Services (CDS) views with scalar input parameters can be used in Open SQL queries in the system. CDS now supports LEFT OUTER JOIN and RIGHT OUTER JOIN. The target entity of the association is mandatory in the definition of an association in a Core Data Services (CDS) view.
“Map to Data source” option in SAP NetWeaver Gateway Service Builder (transaction SEGW) can be used to implement the consumption of a Core Data Services view. The DDL source (DDLS) in which the CDS view is defined is included in a transport request when we transport a Core Data Services (CDS) view. CDS views can also be extended using EXTEND VIEW statement programatically.
The enhancements included in CDS are:
Associations on a conceptual level, replacing joins with simple path expressions in queries
Annotations to enrich the data models with additional (domain specific) metadata
Expressions used for calculations and queries in the data model
Also, check: Deep Dive to CDS Views.
In layman’s words, annotations and associations are extensions of CDS to SQL.
Associations in a Core Data Services (CDS) view can be consumed in the FROM clause; in the WHERE and HAVING clauses and in the projection list. The main purpose of associations in Core Data Services is to define relationships between entities. Dictionary tables, CDS views and Dictionary views can be used as a data source in a Core Data Services (CDS) view. In simpl words, ABAP Dictionary tables, CDS views, Dictionary views can be queried in the Open SQL SELECT statement.
While consuming the Association in the CDS using a path expression, a JOIN is constituted in the underlying database.
Benefits of replacing JOIN statement with Association in CDS view is that it can be consumed using simple path expression and ON conditions for association are generated automatically and association can also be exposed themselves.
Annotation “AbapCatalog.sqlViewName” is mandatory for the definition of a Core Data Services (CDS) view. AbapCatalog.Buffering annotation has scope in the entire CDS view in a Core Data Services (CDS). In other words, annotations are domain-specific metadata. Symbol @ (at) is used to mask annotations in Core Data Services (CDS) views.
The main advantage of using Core Data Services (CDS) in SAP NetWeaver Application Server ABAP is, we can define complex data models that can be consumed in a simple Open SQL SELECT statement and we can use the extended view-definition functionality to push down code to the database layer. It is important to note that scalar input parameters are database dependent. So, if we plan to consume a CDS view using FROM clause of Open SQL query with scalar input then we need to keep in mind that the query cannot be executed on all SAP-certified databases for the database dependent scalar inputs.
Also Read : ‘View the HANA Views from different angles’
ABAP Managed Database Procedure (AMDP):
ABAP Managed Database Procedure is a new feature in AS ABAP allowing developers to write database procedures directly in ABAP. AMDP can be considered as a function stored and executed in the database. The implementation language varies from one database system to another. In SAP HANA it is SQL. Using AMDP allows developers to create and execute those database procedures in the ABAP environment using ABAP methods and ABAP data types.
Check how we can create AMDP Class/Method and consume it in ABAP Program.
In direct words, AMDP is nothing else but a container of methods. And the procedure itself is deployed and executed on the HANA database. ABAP Managed Database Procedure follows the Top-Down approach. In order to implement an ABAP Managed Database Procedure method we need to implement (mandatory) the class interface IF_AMDP_MARKER_HDB. ABAP language elements LANGUAGE db_lang (db_lang = SQLSCRIPT…), FOR db (db = HDB…) and BY DATABASE PROCEDURE are mandatory for the implementation of a method as an ABAP Managed Database Procedure. AMDP can also improve the performance of data transformation in the Extract, Transform and Load (ETL) process in BW. We can consume an ABAP Managed Database Procedure in your ABAP coding by calling the corresponding ABAP class method. Source code management using the ABAP transportation infrastructure, Static syntax checks of database-specific coding and Database independency are the advantages of using AMDP.
AMDP has some limitations.
Exporting, importing and changing parameters are allowed
Methods with returning parameters cannot be implemented as AMDPs
Method parameters have to be tables or scalar types
Method parameters have to be passed as values
ABAP Database Connectivity (ADBC):
ABAP Database Connectivity (ADBC) is an API for the Native SQL interface of the AS ABAP that is based on ABAP Objects. The methods of ADBC make it possible to; send database specific SQL commands to a database system and process the result; to establish and administer database connections. While the statements of Native SQL offer exclusively static access to the Native SQL interface, ADBC makes an object orientated and dynamic access possible.
Please go through the full article on ADBC – ABAP Database Connectivity.
ADBC API uses CL_SQL_STATEMENT and CL_SQL_RESULT_SET classes. We need to bind a reference to the internal table as an output parameter to the CL_SQL_RESULT_SET instance and fetch the result using the next_package method of class CL_SQL_RESULT_SET to retrieve the result set into an internal table after executing a Native SQL query statement to retrieve a list of information using the corresponding ABAP Database Connectivity (ADBC) API method.
Please stay tuned for more on HANA. I would show you how you can get free access to SAP HANA Cloud Platform. Yes, it is free!! I have already logged into it and making my hands dirty on it. I am playing it using Eclipse Studio. I will show in details how you can easily set it up.
If you want to get practical issues and resolutions straight to your inbox, please SUBSCRIBE. We respect your privacy and take protecting it seriously.
If you liked this post, please hit the share button at the left side of your screen.
Thank you very much for your time!!
Series on SAP HANA
You migh like to check the popular and effective Tutorial in SAPYard.
Image source: www.asugnews.com