Don’t Try Coding ABAP Core Data Services Without Reading This First

Jonathan Andre is a lead ABAP developer at IT Partners. Jon is also the President of Andre Technical Consulting LLC located out of Centreville, Virginia. Jon has over 5 years experience with SAP, with a concentration on ECC SD, MM, and IS-OIL modules. Jon’s focus has been in the areas of ABAP OOP and ABAP Web Dynpro. He can be reached at [email protected]

As we described in the prior blog The ABAP Developer Road Map to SAP HANA, with the advent of SAP HANA, there has been a paradigm shift in the way business applications are developed. The rule-of-thumb is simple: Do as much as you can in the database to get the best performance. This was coined as “Code Pushdown” by SAP. Well, this is also true for the underlying data models of the business applications.

Data modeling in ABAP typically involves organizing your data in database tables/views and often providing some additional high-level services for the applications using the appropriate ABAP frameworks. It is logical to conclude, from the Paradigm-shift of Code Pushdown, that to enable real-time businesses in HANA, we need some of these services ideally also brought closer to the database as well.

For SAP this presented several challenges. High-quality data models should provide a single definition and format for the data. They should be clear and unambiguous, reusable and flexible, even extensible. So how can you capture the semantics of the data model in the database so that the model can be easily reused by different consumers, e.g. by OData clients and by OLAP tools? How can you extend the meta-model to service your applications? What is the solution…?

Introduction to Cored Data Services (CDS)

 

Core Data Services, or CDS, is a “semantically rich” Data Definition Language (or DDL) created by SAP. It provides an easy to understand and reusable tool that ABAP developers can utilize to execute the “code pushdown” paradigm. CDS has evolved into different variants, but the ABAP developer should chiefly be concerned with two specific ones…The lesser used option is HANA CDS, the database language that can be used to create tables, views, and structures on the HANA database itself. Views created in HANA can be consumed from the Netweaver AS using Native SQL. The second and most important variant of CDS that should concern ABAPers is the ABAP CDS. While significant differences have evolved between the two variants — for example, SAP HANA-based CDS obviously operates on SAP HANA, while ABAP-based CDS operates on most major database platforms as well as SAP HANA, and each has a different type of repository for development objects — both variants pursue the same goal: to represent central data definitions as a common basis for application development of all kinds.

Let’s look at each variant:

HANA CDS: the database language that can be used to create tables, views, and structures on the HANA database itself. Views created in HANA can be consumed from the Netweaver AS using Native SQL.

ABAP CDS: made available with SAP Netweaver 7.40 SP5, is a valuable tool to have when programming for HANA. However, ABAP CDS can be used even if the underlying database is not a HANA database, as it is an open DDL that is supported by many traditional databases as well. ABAP CDS is usually the best choice when designing and creating database views that will need to access the HANA database, and this will be the prime focus of this blog.

ABAP CDS uses an SQL-like syntax, enhanced with some useful additional features. Like any typical  ABAP object, ABAP CDS files are also transportable between Netweaver AS systems, which is an advantage ABAP CDS has over its HANA CDS counterpart. Once transported, an ABAP CDS View will create and deploy the corresponding database view on the target database automatically (requiring no additional steps for the developer or transport manager).

Unlike classical SE11 views, ABAP CDS views can only be created in Eclipse (by utilizing the ABAP Development Tools for SAP Netweaver add-on for Eclipse). The tools can be found at https://tools.hana.ondemand.com/.

Next, we will go through a brief step-by-step guide on how to create a CDS view, as well as features of the DDL source file.

Creating Your First CDS View

Prerequisites: You should have downloaded and installed the ABAP Development Tools extension for Eclipse already, as well as being within the ABAP perspective with desired package selected.

Step:1 Right-click on package the CDS View will be placed in

Step 2: Select New-> Other ABAP Repository Object

Step 3: Search for Core Data Services Folder and select Data Definition (depending on ABAP development tools version, this may say DDL Source)

Step 4: Select the transport to which you want the DDL source file attached. Once this has been done you can either select “Finish”, which will automatically select the basic CDS view or you can select “Next”, which will bring you to a list of different DDL source file templates to choose from. We will choose NEXT, so we can expore the differnet Templates.

Step 5: Choose the template you would like to begin with. For the first example, we will be starting with the basic view, which creates a CDS Entity beginning with the “Define View” keywords. Table functions are an option as well, but that will be covered in the future AMDP blog.

First ABAP CDS Example: View on SNWD_SO table

This first CDS View example will be a simple SELECT of some fields from the SNWD_SO table, in addition to a generated field that will illustrate the CASE capabilities of CDS. This view will also illustrate the basic structure used for DDL source files. Take a look at the screen-shot below. We will go into each of the red boxed subsections in detail.

Opening Annotations – Subsection

CDS Annotations contain metadata about the DDL source file in which they are contained. When Eclipse generates the CDS View template to work with, it also creates a skeleton for the initial annotations you see in this example. There are more Annotations available to CDS beyond the ones listed here, and fully covering all the Annotations would require an entire blog itself. For this introductory blog on CDS, I will just point out the two most important ones listed here: @AbapCatalog.sqlViewName and @AbapControl.authorizationCheck.

@AbapCatalog.sqlViewName is the only mandatory annotation for a non-extending CDS View source file (extending view will be explained in the next section). It precedes the SQL View name that will be attached to the current CDS View (also known as a CDS Entity). In the above example, the SQL View Name is ‘zjontestview1’. While this SQL View name should NOT be used in your ABAP programs, it can be used in SE11 to display details about the current view (similar to how one would view a classical view).

@AbapControl.authorizationCheck specifies whether an authorization check should be performed for the current CDS view. Security and authorization is beyond the scope of this blog, but this annotation can be left off or set to “#NOT REQUIRED” for the time being.

Datasource Declarations – Subsection

This section will usually come after the opening Annotations but before the first curly bracket. Within this section, the developer specifies:

(1)    The type of view (define or extend view)

(2)    The CDS view name (in this case ZJON_CDS_VIEW_EXAMPLE),

(3)    The source table or view (SNWD_SO)

(4)    Any parameters (more on this later)

(5)    Any joins/associations (more on this later)

Field Declarations and Specifications – Subsection

After the first curly bracket comes the desired fields from the table, as well as any fields that are to be computed. This is the section that a developer will primarily utilize to take advantage of code-pushdown to allow the database to perform calculations. Our example illustrates the ease of using aliases for fields, as well as the New ABAP CASE Construct. The CASE Construct allows a particular value to be returned based on the value of a table field. In our example, we are converting the billing status indicator into its real-world English meaning. If the billing status is ‘P’ for the current record, our calculated field payment_status will have the value ‘Paid’. If the field is blank, the field will be ‘Unpaid’. Finally, if the field is some unexpected value, the payment_status field will be a question mark.

Select Conditions and Restrictions – Subsection

The final section of the DDL source file is where you would add any selection restrictions (such as a where clause) in addition to any aggregation instructions (such as GROUP BY). We will be covering both of these options in later examples.

Checking your Work

Ok now that we have created a basic CDS view for the table snwd_so table, let’s check our work by a SQL preview with Eclipse.

CDS Views can be previewed right within the Eclipse editor. To do this, right-click on the created view, select “Open With” and then select “Data Preview” from the submenu. (Note that in other versions of the ABAP Development Tools, the “Data Preview” option may appear immediately in the menu when you right click.)

Below is the data preview for the example CDS View above, ZJON_CDS_VIEW_EXAMPLE:

Notice that, along with the data we have selected from the snwd_so table, we also have the calculated field payment_status that was created using the CASE function.

Using CDS Views in ABAP Programs – without  Parameters

Next, we will look at an example of how to use the example CDS view within an ABAP Program. This simply requires using an Open SQL statement that selects from our CDS Entity (ZJON_CDS_VIEW_EXAMPLE). As an added benefit of ABAP CDS, a generated CDS Entity name can also be used in DATA declarations to create structures of a compatible TYPE.

(ZJON_CDS_EXAMPLE_CONSUME – Program)

The output of this program:

There you have it! Your first CDS View and an example ABAP Report that consumes it. What? Can we extend the view if new z-fields are needed? Funny you should ask…

Extending CDS Views

Another nice feature of CDS views is the ability to enhance them. This feature is beneficial when you want to augment an existing view, such as those that come with the standard SAP solution. We often need to modify SAP objects with custom Z fields, and view extension present a simple and transportable way to do this. To extend a view, follow the same as above when creating a basic view. However, when you arrive at the “New Data Definition” screen, select “Extend View” instead of “Define View”.

This template will allow you to select an existing CDS View to extend, and specify the new name for the view being created:

@AbapCatalog.sqlViewAppendName: 'sql_view_append_name'
@EndUserText.label: 'EXTENDO'
extend view view_name with Zextedn_Exmpl {
    data_source_name.element_name
}

In addition, view extension must include the following Annotation before an extend view definition:

@AbapCatalog.sqlViewAppendName: ‘${sql_view_append_name}’

For this next example, we will extend the ZJON_CDS_VIEW_EXAMPLE with an additional field. To mimic a real-world example, let’s pretend we are modifying this view in order to achieve a particular business requirement.

Our requirement is to flag “High Impact” sales orders from the SNWD_SO table as part of the existing ZJON_CDS_VIEW_EXAMPLE. High Impact sales are defined as those sales orders with Gross Amounts over $100,000 and that have not yet been paid. For sake of simplicity, we will also assume all Sales Orders in this table are in US dollar amounts, to avoid needing to do currency conversions. To achieve this, the following view extension was created:

This view extension will append our new field to the view it is extending (ZJON_CDS_VIEW_EXAMPLE) and append the new field SO_STATUS. The new field SO_STATUS is calculated based on two qualifying criteria: when the gross_amount for the sales order is greater than $100000  and the billing status is not ‘P’. This extended view displays exactly like the original view example, with the addition of the required “High Impact” indicator. Below is the output of this view, executed by running the “Data Preview” in Eclipse.

 Note that this does NOT create a new view, but modifies the original view ZJON_CDS_VIEW EXAMPLE itself! This is indicated by the familiar enhancement symbol that we now see in the ZJON_CDS_VIEW_EXAMPLE source file.

Once enhanced, our extending field will be available during all SELECTS against the ZJON_CDS_VIEW_EXMAPLE table.

Aggregate Functions, Associations, and Parameters

As a last ABAP CDS example, let’s explore a slightly more advanced scenario using a few aggregate functions, an association, and a parameter. We will explore each one of these features one at a time to illustrate the capabilities they provide.

To build on our real-world example, let’s now imagine a scenario where we are trying to nail down not only the high impact sales orders, but also the companies responsible for them. Our end goal is to identify the companies with “High Impact” unpaid sales orders, the total count of these sales orders for each company, and the total gross amount of all “High Impact” sales orders for each company.

In addition, we will allow the definition of “High Impact” to be variable. While a sales order matching the criteria will remain an unpaid one, the threshold value that classifies it as “High Impact” won’t be hard coded at $100,000. The threshold value will instead be left up to the user to determine at runtime.

The view used to accomplish this will again utilize our initial example ZJON_CDS_VIEW_EXAMPLE. It performs a SELECT from this view while utilizing an “Association” to map in the corresponding company from the snwp_bpa table with the matching buyer ID field.

Using ABAP CDS Associations

Associations are essentially reusable JOINS that relate two CDS Entities (tables and views) to each other. CDS Associations have the added benefit of being able to specify cardinality ([1..1], [0..1], [*], etc.). Many of the features of ASSOCIATIONS are available as JOINS, however, ASSOCIATIONS are the preferred Best Practice and more elegant option when merging two CDS entities.

Using CDS Aggregate Functions

The aggregate functions available in CDS Views are the same ones that are available in the new Open SQL. Although they are readily available during a regular Open SQL Select (NO Core Data Services), it is still an extremely useful tool to have in CDS Views. Using the CDS approach, developers can make certain summary data is uniform across projects and systems without having to maintain entirely separate summary level tables.

As you can see in the above example, any time you use these aggregate functions in the field declaration section, you are required to include the GROUP BY addition in the Selection Restriction and Condition section. Removing the GROUP BY clause while having either of these functions remaining will raise a syntax error.

Using Parameters in CDS Views

CDS Parameters is another very useful feature that allows the parameterization of CDS Views. In our specification example, we wanted to restrict the sales orders that were defined as “High Impact” to those sales orders that have a gross value of our choosing. By using a parameter THRESHOLD in our DDL source file, we are able to pass in this parameter at runtime and allow the results to be customized to that specific value.

Note that there are no optional CDS parameters! If a parameter is specified within the DDL source file, it must be provided or a syntax error will occur. Also, a data type must be specified for the parameter in use. ABAP types can be used for this purpose, but take note of the syntax <ABAP.dtype> when using these types. You can also use DDIC Data Elements such as SNWD_SO_ID, SNWD_City…etc. Our example used the ABAP.Init4 data type.

Now, we will bring this all together with a sample ABAP program that utilizes our new CDS View. This program will also illustrate how to call a CDS View with the parameter we have specified. take a look at the code below…

*&---------------------------------------------------------------------*
*& Report zjon_cds_example_para_assoc
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zjon_cds_example_para_assoc.

parameters p_thresh type int4.

SELECT * FROM zjon_view_exmpl_cust_hghimpcT( THRESHOLD = @p_thresh )
INTO TABLE @DATA(lt_data).

DATA: ls_data like line of lt_data.

Loop at lt_data into ls_data.
    write:/ ls_data-company, ls_data-so_count, ls_data-total_gross.
endloop.

Notice, to perform a SELECT from a CDS View with a parameter, the CDS Entity name must be immediately followed by an open parenthesis (no spaces) and a closed parenthesis. Within the parenthesis, the parameter must be assigned a compatible value. This value can be hard coded or a variable preceded by the ‘@’ Escape symbol. This is similar to executing a method in ABAP OO with importing parameters.

Checking your Work

OK, let’s run the program. We get the expected SELECTION screen. We will $10,000.00 as our threshold.

Executing this report produces the summarized list of companies with unpaid sales orders above $10,000.

  Using Native HANA CDS

Generally speaking, ABAP developers should make very little use of HANA CDS when compared to ABAP CDS or Open SQL. ABAP CDS provides many of the features available in HANA CDS in a much simpler fashion, with the added benefit of being transportable. There are very few scenarios where HANA CDS would be a better option. Two scenarios where HANA CDS may have to be necessary used are:

You are using HANA as a sidecar/ secondary database.

If you are using HANA as a secondary database, then ABAP CDS will simply not work. That is because ABAP CDS works with the data dictionary and assumes that all views are coming from the primary DB and not a secondary DB. As of yet, there is no modifier to specify a secondary DB instead

There is some Native SQL function not available in ABAP CDS you require.

AS ABAP on HANA becomes the standard, this scenario will become the more likely reason someone may opt to use HANA CDS vs ABAP CDS. While ABAP CDS and HANA CDS are being closely developed, there may be some branching in their functionalities. This is because ABAP CDS’ chief purpose is to serve as an Open Source DDL for all databases, while HANA CDS is designed solely to model data on the HANA side.

Fortunately, if a developer does eventually need to venture into the HANA realm to utilize some feature not available on ABAP CDS, the syntax and structure of HANA CDS views are very similar to the ones in ABAP CDS.

Creating and Consuming HANA CDS Views

Prerequisites: You have a SAP HANA System up and running. You are connected to the HANA system through Eclipse (with SAP Development Perspective). You have permissions to create views and run SQL on the SYSTEM schema. In addition, your SAP Netweaver AS system is connected to this HANA DB (as either a primary DB or a secondary DB).

Step 1: Open the SAP HANA Development Perspective

Step 2: Navigate to the Project Explorer tab (should be on the top left of the screen, unless you have rearranged your layout)

Step 3: Navigate to the SAP HANA Folder and hit the drop down. Select XS Project and hit next

Step 4: Enter the desired project name and hit next

Step 5: Select the desired workspace that utilizes your SAP HANA system. If a workspace doesn’t exist that meets this criterion, hit “Add Workspace” and then select your HANA system on the next screen

Step 6: For the purposes of this example, you can deselect both Access Objects on the following screen and hit Finish

Step 7: You should see your new project appear in the Project Explorer tab. Right-click this tab and select New->Other again.

Step 8: Navigate to SAP HANA->Database Development->DDL Source File

Step 9: Name your file, select your package, and hit Finish

From here you should see a very basic “Context” layout for you to begin with. You should ensure your @SCHEMA annotation says ‘SYSTEM’ after it, or take note of the current schema to ensure your ABAP code is SELECTING from the correct one.

For our example, we will recreate our original ZJON_CDS_VIEW_EXAMPLE CDS View, but this time directly on the HANA DB. As you will see below, the CDS syntax is so similar that we can literally copy and paste our original ABAP CDS source into the HANA CDS source after the annotations, add a semicolon, and we are finished.

 

There are instances that would require many more changes to transfer an ABAP CDS to HANA CDS, however, it should be reassuring to developers that knowing ABAP CDS will make learning HANA CDS much easier.

The difficulty in using HANA CDS does not lie in the HANA CDS view creation itself, but within the ABAP Program source that needs to use some form of Native SQL to access it. As you will see below, Native SQL is much less elegant than Open SQL, much harder to read, and generally a headache one would want to avoid if possible.

Some things to take note of:

– If you have a proper sidecar installation or proper data replication, you can just use the data dictionary type for that table. If not, you should create generic data types to hold the imported data (as I did with the “results’ type in the example)

– The view location will be “<SCHEMA>”.”<PROJECT_FOLDER>::<VIEW_NAME>”

– This was a very simple SELECT scenario (made simpler by the fact we’re using CDS Views). Even a slightly more complicated SELECT would make this Native SQL SELECT much worse!

*&---------------------------------------------------------------------*
*& Report zjon_native_sql_exec
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zjon_native_sql_exec.

TYPES: BEGIN of results,
     salesorder(10) TYPE c,
     buyer_id(32) TYPE c,
     payment_status(9) type c,
     gross_amount TYPE dec10_2,
     net_amount TYPE dec10_2,
     tax_amount TYPE dec10_2,
     END of results.

  DATA:  lo_sql_stmt TYPE REF TO cl_sql_statement,
         lo_conn     TYPE REF TO cl_sql_connection,
         lo_result   TYPE REF TO cl_sql_result_set,
         lv_sql      TYPE string,
         lr_data    TYPE REF TO data,
         lt_results TYPE TABLE OF results,
         ls_results TYPE results,
         lx_sql_exc TYPE REF TO cx_sql_exception.

TRY.

      CONCATENATE 'SELECT "Salesorder", "buyer_id","PAYMENT_STATUS", "GROSS_AMOUNT", "NET_AMOUNT", "TAX_AMOUNT"'
      'FROM "SYSTEM"."ZJON_CDS_XS_PROJ::ZJON_CDS"' INTO lv_sql.


      CREATE OBJECT lo_sql_stmt EXPORTING con_ref = cl_sql_connection=>get_connection('AB1').

      lo_result = lo_sql_stmt->execute_query( lv_sql ).

*     Step 4: Read the result into the internal table lt_partner
      GET REFERENCE OF lt_results INTO lr_data.
      lo_result->set_param_table( lr_data ).
      lo_result->next_package( ).

*     Step 5: close resources, i.e. the SQL statement and connection
      lo_result->close( ).

   CATCH cx_sql_exception INTO lx_sql_exc.

   ENDTRY.


  write:/ 'Order ID   Paid Status Gross Amount  Node'.
      Loop at lt_results INTO ls_results.
      Write:/ ls_results-salesorder, ls_results-payment_status, ls_results-gross_amount,
      ls_results-buyer_id.
      endloop.

And the output (which is very similar to our first examples output):

As you can see, HANA CDS requires much more effort to implement, then ABAP CDS. For this reason, HANA CDS should be avoided unless there is an absolute need to use it.