SAP TechEd 2013 – applications based on ABAP and SAP HANA, part 2

In my last blog I wrote about ‘bottom-up’ and ‘top-down’ approaches to leverage SAP HANA capabilities from ABAP. In this blog I want to give you a glimpse on new features planned for the next support package of ABAP 7.4.

Advanced Open SQL

As you probably know Open SQL is our database independent interface to connect the ABAP application server to the underlying database. The big advantage is that code making use of Open SQL runs on all database platforms supported by SAP NetWeaver AS ABAP.

The big disadvantage is that the feature set of Open SQL is quite restricted. Take a look at the following examples:

• You want to sum up the costs of 12 periods which are stored in 12 different attributes of a record? Read all 12 attributes and sum them up in ABAP.
• You want to concatenate the product ID and name? Read both and concatenate them in ABAP.
• You want to calculate freight costs based on the maximum of the weight and the volume weight of a material? Read weight and volume weight and use an IF-clause in ABAP.

Is there really no better way? In the future there will be! 🙂

We plan to enrich the feature set of Open SQL. This will allow you to push down calculations to the database layer that could not be pushed down by means of Open SQL in the past. With the next support package of ABAP 7.4 we, for example, plan to support:

• string expressions (concatenation of attributes)
  
• usage of ABAP constants and variables in the projection list
• CASE expressions (‘simple CASE’)
• certain arithmetic expressions for integral, decimal and floating point calculations
• certain built-in SQL functions (e.g. CAST, COALESCE)

The following example shows how advanced Open SQL can look like:

“product ID and product category are concatenated using a string expression

SELECT product_id && ‘,’ && @space && category AS product,

       “the price including the VAT is calculated in the database by means of

       “a CASE statement

       CASE tax_tarif_code

         WHEN 1 THEN price * @lc_factor_1

         WHEN 2 THEN price * @lc_factor_2

         WHEN 3 THEN price * @lc_factor_3

       END AS price_vat, “projection list needs to be separated by comma

       currency_code AS currency

       FROM snwd_pd

       INTO CORRESPONDING FIELDS OF @ls_result. “variables have to be escaped by @

  WRITE: / ls_result-product,

           ls_result-price_vat CURRENCY ls_result-currency,

           ls_result-currency.

ENDSELECT.

Advanced view building

What I have written about Open SQL is basically also true for the view building capabilities of the ABAP Dictionary. In line with Advanced Open SQL we also plan to introduce features for advanced view building. These features will ease code pushdown and simplify the consumption of relational data models.

In the future we plan to allow you to create views by means of a new Eclipse-based editor (integrated into the Eclipse-based ABAP development environment). The following screenshot shows how this editor will look like.

And the following two snippets illustrate how you will define views in the new editor. Views can be nested (i.e. a view consumes another view) and they can be linked with associations.

• In the given example the view Z_DEMO_REVENUES reads certain attributes from table SNWD_SO. It summarizes and groups the data.

@AbapCatalog.sqlViewName: ‘Z_DEMO_R’

define view z_demo_revenues as select from snwd_so

{

  snwd_so.buyer_guid,

  sum(snwd_so.gross_amount) as gross_amount,

  sum(snwd_so.net_amount) as net_amount,

  sum(snwd_so.tax_amount) as tax_amount,

  snwd_so.currency_code as currency

} group by snwd_so.buyer_guid, snwd_so.currency_code

• The view Z_DEMO_CUSTOMER reads data from tables SNWD_BPA and SNWD_AD. It also defines an association to the first view.

@AbapCatalog.sqlViewName: ‘Z_DEMO_C’

define view z_demo_customer as select from snwd_bpa

  inner join snwd_ad on

    snwd_ad.node_key = snwd_bpa.address_guid

  association[*] to z_demo_revenues as revenues on

    revenues.buyer_guid = snwd_bpa.node_key

{

  snwd_bpa.node_key, snwd_bpa.bp_id,

  snwd_bpa.company_name,

  snwd_ad.country,

  snwd_ad.postal_code,

  snwd_ad.city,

  revenues.gross_amount,

  revenues.currency

}

ABAP-managed database procedures

The last planned feature are ABAP-managed database procedures. You might have heard about database procedures already. They can be used to implement complex calculations by means of SQLScript (including Calculation Engine Functions). With the next support package of AS ABAP 7.4 we plan to support database procedures which are managed by the ABAP application server.

The following example shows how ABAP methods can be used as container for database procedures (you might notice that the code inside the method body is not ABAP, but SQLScript).

CLASS zcl_demo_amdp DEFINITION

  …

  “marker interface (e.g. for where-used list)

  INTERFACES: if_amdp_marker_hdb.

  METHODS: determine_sales_volume

             IMPORTING VALUE(iv_client) TYPE mandt

             EXPORTING VALUE(et_sales_volume) TYPE tt_sales_volume.

  …

ENDCLASS.

CLASS zcl_demo_amdp IMPLEMENTATION.

                                “additions for implementation

  METHOD determine_sales_volume BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT

                                “forward declaration of used artifacts

                                USING snwd_so_i snwd_so_sl snwd_pd.

    lt_sales_volume = SELECT product_guid, SUM(quantity) AS quantity,

                             quantity_unit

                             FROM snwd_so_i AS i

                             INNER JOIN snwd_so_sl AS sl

                                ON sl.client = i.client

                             AND sl.parent_key = i.node_key

                             WHERE i.client = :iv_client

                             GROUP BY product_guid, quantity_unit;

    et_sales_volume = SELECT product_id, quantity, quantity_unit

                             FROM snwd_pd AS pd

                             LEFT OUTER JOIN :lt_sales_volume AS sv

                               ON sv.product_guid = pd.node_key

                             WHERE pd.client = :iv_client

                             ORDER BY product_id;

  ENDMETHOD.

ENDCLASS.

Now you know which new features are planned to ease code pushdown and to simplify consumption of relational data models.

The remaining question for my last blog will be: how can optimized infrastructure components help you to benefit from SAP HANA. If you like to learn about fuzzy-enabled value helps or evaluation of business rules in SAP HANA, you will soon be able to read more… but most likely only after Christmas 😎 .