ABAP lays the foundation for countless SAP applications, with a broad range of features and functionality for creating powerful business solutions. SAP HANA provides the opportunity to innovate with new and sophisticated technologies. With support package stack (SPS) 05 for SAP NetWeaver 7.4, SAP has brought ABAP and SAP HANA together with features that enable developers to leverage the best of both worlds, not only in new custom development projects, but also in their existing applications. Delivered as a globally available service release in December 2013, SPS 05 offers many new artifacts that ABAP developers can use to push down code to SAP HANA and bring innovation to their applications at various levels of abstraction.
This article explores in detail the major ABAP development features delivered with SPS 051 that take advantage of SAP HANA — in particular, it looks at new features for database-independent programming with Open SQL, unified data modeling and definition with core data services, and simplified control of stored procedures with ABAP-managed database procedures. It also provides a brief look at some of the features available with the next major functional support package stack for SAP NetWeaver 7.4, SPS 08, released in Q3 2014.
All the features discussed can be easily explored using the Eclipse-based ABAP development tools for SAP NetWeaver (known as ABAP in Eclipse), which provide many productivity features that can help developers in their daily development work.2
Database-Independent Programming with Open SQL
In ABAP-based environments, Open SQL has provided the proper level of abstraction for database processing for many years, enabling ABAP programs to perform operations on any SAP-supported database. The main advantages of Open SQL are its database-platform-independence; its integration into the ABAP language and runtime environment by means of internal tables; and strong support of SAP NetWeaver Application Server (SAP NetWeaver AS) ABAP with table buffering, SQL statement caching, and cursor caching. Many tools are available, such as the ABAP source editor and the SQL trace tool (transaction ST05), to facilitate Open SQL programming during application design mode and to monitor and trace SQL execution during execution mode.
Open SQL provides an optimal means for ABAP developers to abstract from the characteristics of the underlying database — in fact, developers are often never even aware of which database platform is actually used underneath.
While this provides a thorough foundation for SAP Business Suite, the traditional programming style used by developers includes certain behaviors that become challenging when it comes to big data and the vast amount of complex information that needs to be processed.
Many traditional ABAP programmers use a programming style that centers on the application server. Specifically, they often use large nested SELECT loops that are often quite natural for processing large data sets, but essentially copy all relevant data to the application server, where the data is managed in ABAP internal tables. When the processing is done and changes are made, the internal tables are written back to the database. There are many existing custom reports in customer landscapes that use a similar execution scheme, both in dialog and batch processing.
ABAP lays the foundation for countless SAP applications, and SAP HANA provides the opportunity to innovate with new and sophisticated technologies.
The Influence of SAP HANA
SPS 02 for SAP NetWeaver 7.4, which shipped in May 2013, provided customers with the option to use SAP HANA as the database for SAP ERP, SAP Customer Relationship Management (SAP CRM), and SAP Supply Chain Management (SAP SCM). Developers were able to use SAP HANA-specific artifacts, such as analytical views and calculation views, in their ABAP programs — both new and existing, with some reengineering in the existing programs to ensure that logic executes where it semantically belongs — to accelerate data processing dramatically.
In-memory technology powered by SAP HANA allows developers to delegate processing that is data-intensive to the database layer, rather than managing everything in the ABAP server and transferring huge amounts of data between the server and the database. This paradigm shift to “pushing down” data-intensive code enables more efficient execution and performance, as well as a separation between front-end UI logic and back-end application and database logic to support a modern application architecture.
With major SAP solutions such as SAP Business Warehouse (SAP BW) and SAP Business Suite now optimized for SAP HANA in addition to fully supporting traditional database platforms, developers needed a new approach that combines the processing power of SAP HANA with a semantic layer in ABAP that is not bound to a particular platform. To meet this need, the ABAP language team at SAP completely renovated the Open SQL architecture to provide a new Open SQL syntax that seamlessly integrates into the ABAP environment, like the traditional Open SQL, but provides enough hints to the underlying database layers for optimal execution.
The New Open SQL
SPS 05 for SAP NetWeaver 7.4 introduced the new syntax for Open SQL, which includes support for comma separated lists, enhanced string expressions, the definition of host variables in a more standard fashion, and fewer restrictions for joins. Figure 1 shows a listing, based on the well-known open invoice items example scenario, of modern Open SQL code in ABAP that showcases the philosophy of the new and extended Open SQL syntax provided with SPS 05, as well as an additional feature included in SPS 08.
In the TYPES section, a demo structure is defined as a concatenation of a sales order, a business partner, and an invoice header to keep results from a multiple join operation. This join is expressed in the SELECT statement, where the corresponding three tables (snwd_so for sales orders, snwd_bpa for business partners, and snwd_so_inv_head for invoice headers) are joined. The ~ selector is used to refer to a particular column, such as so~buyer_guid. SPS 08 adds the ability to refer to all columns of a row with, for instance, so~*. The host variable (in this case, the internal table) where the result set is stored is prefixed with @ (@lt_data in Figure 1).
The new syntax is syntactically closer to the SQL-92 standard3 than traditional Open SQL, which enables greater execution efficiency. It also serves as an important basis for the definition of core data services views, which is a SQL-based data modeling and definition feature included with SPS 05 (more on this in a moment). Moving away from the traditional loop processing with SELECT … ENDSELECT achieves a great deal of push-down code efficiency, since the join query is passed to the underlying SAP HANA database for full-speed in-memory computation.
SPS 08 Enhancements for Open SQL
In addition to the ability to refer to all columns of a row, enhancements for SPS 08 include new SQL expressions and inline declarations. It also includes consumption of parameterized core data services views, which we’ll look at in more detail next.
Unified Data Modeling and Definition with Core Data Services
In the past, data modeling in ABAP usually involved defining and managing views of your data on the ABAP server, using tools such as the ABAP dictionary in the ABAP Workbench. In keeping with the paradigm shift of moving data operations to the database — to take advantage of in-memory processing, for instance — SAP introduced core data services (CDS) with SPS 05 for SAP NetWeaver 7.4.
CDS is a data modeling infrastructure for defining and consuming semantic and reusable data models on the database, rather than on the ABAP server, regardless of the database system used. CDS offers a SQL-oriented abstraction layer that extends the SQL-92 standard with features that bridge and unify the traditional OLTP and OLAP models of data definition, data query, and data manipulation. Supported natively in both ABAP and SAP HANA, the data models are expressed in data definition language (DDL) and are defined as CDS views, which can be used in ABAP programs via Open SQL statements to enable access to the database.
CDS provides a range of advantages for ABAP developers, including:
- Semantically rich data models. CDS builds on the well-known entity relationship model and is declarative in nature, very close to conceptual thinking.
- Compatibility across any database platform. CDS is generated into managed Open SQL views and is natively integrated into the SAP HANA layer.
- Efficiency. CDS offers a variety of highly efficient built-in functions — such as SQL operators, aggregations, and expressions — for creating views.
- Support for annotations. The CDS syntax supports domain-specific annotations that can be easily evaluated by other components, such as the UI, analytics, and OData services.
- Support for conceptual associations. CDS helps you define associations that serve as relationships between different views. Path expressions can be used to navigate along relations.
- Extensibility. Customers can extend SAP-defined CDS views with fields that can be automatically added to the CDS view.
Defining a CDS View
CDS views are defined using the CDS editor in ABAP in Eclipse. Figure 2 shows the definition of a DDL source file for a CDS view (zcdsv_open_invoice_kk) derived from the open invoice items example scenario. The view contains two fields: one containing the key of the buyer, and another containing the classification of the buyer, which is determined based on the number of open invoices of that particular customer. The classification is obtained with the SQL operators group by and having count (distinct), which segment the buyers into three categories: less than or equal to 2,000 open invoices; between 2,000 and 4,000 open invoices; and above 4,000 open invoices. The complete CDS view is the union of these three different categories expressed with the SQL operator union all. The addition all does not eliminate potential duplicates, to avoid any additional processing time that would degrade performance, but in this case the categories are disjointed by definition, so they will have no records in common. Without any procedural logic in ABAP, we have defined a CDS view.
Figure 3 shows how easy it is to combine the results of the classification view with some more readable fields from the business partner master table, for instance, such as the customer number and customer address. This is achieved by a JOIN operation of the table snwd_bpa with the CDS view (zcdsv_open_invoice_kk) defined in Figure 2, which retrieves the classification field that is computed in the CDS view. The CDS editor offers semantically rich code completion and field completion capabilities by pressing Ctrl+Space, which launches the pop-up in Figure 3 that displays the fields that can be chosen from the business partner master table.
Figure 3 — Adding fields to the example CDS view
Without any need for ABAP coding, you can immediately evaluate the CDS definition with the help of the Open Data Preview dialog. Figure 4 shows the results of the example CDS view, with the buyer and classification defined. From this dialog, you can interact with the result set, and perform sorting and analytical ad hoc calculation as you would when working with low-level SAP HANA artifacts such as calculation views.
Consuming CDS Views in ABAP Programs
Since CDS views provide an abstraction layer that is optimally suited for in-memory SAP HANA execution, but portable to other database platforms, you can easily consume the CDS views from within new and existing ABAP programs. Figure 5 demonstrates how you can refer to CDS definitions in a custom ABAP report. All you have to do is define an internal table that is typed with the corresponding CDS view definition (in the example, the variable cds_view defined in the first line of the report). Retrieving data from the CDS view into the internal variable is done with a simple SELECT * from the CDS view (zcdsv_open_invoice) into the internal table, prefixed with the host variable indicator (@cds_view), as shown in the second SELECT statement.
The CDS abstraction layer not only provides a means for traditional OLTP processing, it can also be used equally well in an OLAP scenario, which is how SAP Business Suite on SAP HANA leverages CDS. CDS enables the use of one view or data definition layer, consistently and coherently, in different consumption scenarios (such as OLTP, OLAP, enterprise search, and UI definition).
SPS 08 Enhancements for CDS
SPS 08 includes advanced features for CDS, such as parameterized views that can be accessed via actual parameters, enabling CDS views to be more dynamic by making them dependent on the actual program execution — similar to the well-known logon language approach for retrieving language-dependent texts. In addition, with SPS 08, SAP-provided standard CDS views can be extended with custom fields, and authorizations can be declared, which is essential in a business context.
Simplified Control with ABAP-Managed Database Procedures
Database procedures are valuable tools for ABAP developers. They allow developers to organize their programs more clearly by consolidating logic in a central location, enabling it to be reused across multiple applications, and they also provide the ability to perform integrity checks and grant privileges. With SAP HANA, database procedures push down data-intensive logic to the database. Initially, these procedures were created and managed in the SAP HANA studio and consumed by the ABAP layer. SAP NetWeaver 7.4 SPS 05 introduces a new approach, known as ABAP-managed database procedures (AMDPs).
AMDPs allow developers to create and manage stored procedures directly in ABAP, while they are executed on the database level. The ability to create AMDPs with proper embedding in the ABAP execution context means that they are fully managed by the SAP system’s change and transport system, and behave like any other ABAP artifact from a lifecycle management perspective. The development work takes place completely in ABAP in Eclipse; there is no need to drill down into the SAP HANA studio to define AMDPs.
Keep in mind that although AMDPs are defined on the ABAP level, they are by nature dependent on the underlying database platform, so that they can fully optimize the database in use. For this reason, the implementation language will vary depending on the database in use. For SAP HANA, the implementation language is SQLScript, so creating an AMDP for SAP HANA is as simple as implementing SQLScript in your ABAP program, both in new and existing programs.
Creating an AMDP
Figure 6 shows an example AMDP (ZCL_CUSTOMER_OPEN_INVOICES_KK) in the class editor of ABAP in Eclipse, based on the open invoice items example scenario. AMDPs are wrapped into regular global ABAP classes. A method that contains a stored procedure is introduced by the special keyword by database procedure for hdb language sqlscript, which indicates that other database platforms can hook in their particular programming languages for stored procedure development.
In the open invoices example, the AMDP calls a helper AMDP of the same class (get_curr_conv_relevant_items) to compute an intermediate result, since invoices can be present in different currencies that need to be converted first into a unified currency (EUR, for instance). Actual parameters (the client and business partner ID) are passed to the helper AMDP, and an output variable yields the result of the AMDP call as a temporary table. This temporary table is in turn selected from and joined with regular tables to produce the desired results for the outer AMDP.
Debugging an AMDP
Since working with AMDPs is a rather low-level procedural approach, debugging support can be helpful. While breakpoint setting on the ABAP level is supported, it would invoke the ABAP debugger, which treats the SQLScript commands as an atomic execution unit, meaning that stepping into the procedure itself is not possible. Fortunately, you can easily set breakpoints in the source on the SAP HANA level by invoking the SQLScript debugger in the SAP HANA studio.
Figure 7 shows the procedure in the SQLScript editor in the SAP HANA studio. Single stepping is not yet supported, so you need to set several breakpoints up front to hop from breakpoint to breakpoint in a single-step fashion. In Figure 7, you can see a breakpoint set at et_bpinfo = select.
To start the SQLScript debugger, you need to maintain a debug configuration by pressing the debugger configuration icon in Eclipse, which launches the dialog shown in Figure 8. This dialog allows you to select from a dropdown list the SAP HANA database session to which the debugger should be attached. Since there might be other active database users, you specify the ABAP schema owner — the ABAP processor (SAPHANAABAP, in the example) — and optionally your ABAP developer user ID, so that you only capture your own sessions rather than those of your colleagues.
The SQLScript debugger then starts and waits for the execution of the procedure. When a corresponding breakpoint is hit, execution is halted, allowing you to examine the variables and then resume execution until the next breakpoint is hit. Figure 9 shows the debugger hitting the et_bpinfo = select breakpoint set in Figure 7.
SPS 08 Enhancements for AMDPs
SPS 08 provides several new features for AMDPs to further enhance the developer experience. These include an extensibility concept, improved exception handling, and access to secondary database connections.
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. The various tools included with ABAP in Eclipse help you to quickly prototype and test these artifacts with short turnaround cycles. This approach empowers 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 development. SPS 05 provides a solid starting point, with many additions and enhancements delivered by SPS 08 and follow-up functional support packages.
1 For a high-level overview of these and other features introduced with SPS 05, see my article “Take Your SAP Solutions to New Heights with the Latest Release of SAP NetWeaver 7.4” in the April-June 2014 issue of SAPinsider (SAPinsiderOnline.com). [back]
2 The ABAP in Eclipse tools are available at https://tools.hana.ondemand.com/#abap. For more on ABAP in Eclipse, see my articles “Turbocharge Your ABAP Development with Innovation from Eclipse” in the October-December 2012 issue of SAPinsider, and “End-to-End Development Scenarios from SAP: Bridging the On-Demand and On-Premise Divide with SAP Tools for Eclipse” in the October-December 2013 issue of SAPinsider (SAPinsiderOnline.com). [back]
3 For more on SQL-92, visit www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt. [back]