Skip to Content

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.

Eclipse_DDL_Source.png

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 😎 .

To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

  1. Anupam Anand

    Hi Thorsten,

    A very good read and very informative too. I attended the sessions for tuning custom code for migration to HANA. I was actually looking for deeper details which I now have from your blog.

    Thanks,

    Anupam

    (0) 
  2. Abdul Hakim

    Hi Thorsten – Amazing stuff are coming out for ABAP Community 🙂 Thanks for sharing this info with us. Looking forward to it…

    Also i have a question in my mind for which i didnt get convincing answer for quite sometime. Appreciate if you can clarify…

    Why there is no possibility of building Native HANA applications using ABAP / JAVA..Why the native application is limited to certain programming languages like Javascript / RDL… Why cant ABAP AS / JAVA AS is brought within HANA itself to build native applications.. When SAP says HANA’s main strength is openess then it shouldnt  limit itself to specific programming languages for building native applications..I believe ABAP / JAVA should also be a part of native application development language for HANA…Appreciate your insight and thoughts in this subject…

    Thanks

    Hakim

    (0) 
    1. Thorsten Schneider Post author

      Hi Hakim,

      thanks for your feedback. I am not sure, if I can add much to what Eric has written in http://scn.sap.com/thread/3459978.

      As Eric has outlined during the discussion, you can very well combine ABAP or Java and SAP HANA. You are not limited to using JavaScript. And openess is not (only) a question of the number of programming languages SAP HANA supports ‘out of the box’, but of interfaces that can be used from other environments, e.g. JDBC, ODBC…

      You are right that this requires in contrast to using XS an ‘external’ application server, which currently runs on a separate host. While that means that the architecture is not as simple as with XS and that certain optimizations might not be possible, I still consider it a very valid approach (especially if an application you build is supposed to run tightly integrated into let’s say ERP and relies on re-use of existing ABAP-based frameworks).

      What your question teaches me is that we probably have to spend some more time next year to define what ‘native’ really means and how it is different from ‘SAP HANA based’ as well as what are guidelines to decide on the architecture of an application.

      Best regards

      Thorsten

      (0) 
      1. Abdul Hakim

        Hi Thorsten – Thanks for the response. Eric has clarified some of my questions and i very much appreciate that…i have posted my question in this blog for which i didnt get the detailed answer…Appreciate if you can clarify this in this forum or through a separate blog…. I have rephrased my question  below…

        “Why can’t ABAP AS / JAVA AS be brought within HANA itself (2 tier architecture) and provide multiple native application development options in addition to Javascript / RDL which exists today?”

        This would be similiar to what SAP offers in SAP Netweaver world ( AS JAVA / AS ABAP)..In the similar fashion HANA XS with Javascript / RDL development environment or HANA XS with ABAP development enviroment or HANA XS with Java development enviromenent following 2 tier architecture strategy of HANA XS..This would help the customers to leverage the inhouse knowledge what they have already (ABAP/JAVA) and still allowing them to simplify the landscape by leveraging HANA XS features for their new development using their familiar development environment (ABAP/JAVA)…This would be the real innovation without disruption in my opinion…

        Thanks

        Hakim

        (0) 
  3. Albrecht Gass

    Hi Thorsten,

    can you give a bit more detail on the reason for using association[*] to to mark the connection to another view. Why not use the join construct do so?

    Also it seems that in your examples the names of the views in the ABAP dictionary and in HANA are different (Z_DEMO_R and z_demo_revenues), isn’t that going to be confusing?


    Thanks

    Albrecht

    (0) 
    1. Jasmin Gruschke

      Hi Albrecht,
      let me give you the standard, non-convincing answer: that depends on the use case 🙂 .

      And in stating this, my explanation below will be an example, rather than a general information:

      In principle you could use a JOIN statement but an ASSOCIATION e.g. allows for a simplification of your data model. Assume you define a view  Vbased on a join between tables A and B with a “complicated” or let’s just say advanced ON condition. You can then use fields of A and B in the field list. The definition of the association ASSOC moreover allows you to expose the association itself by including it in the field list. Up to now you might argue, what’s the advantage of this? And that is, when you think about path expressions, respectively when building another view V2 on the initial view V. Having exposed the association, you can follow the association e.g. you can include an element ASSOC.field in your view V2. Instead of rewriging the complicated ON or WHERE condition again, you just use the path expression.

      Similar argument holds for filtering, e.g. assume you do the association between sales order invoice tables (header and item). You can then include the association without any filter requirement in the projection list of the view and additionally you add the association with a filter on “only unpaid invoices” in the projection list. A view V2 on this view can then either follow the path to unpaid invoices or to “all” invoices without defining any further filter requirements.

      Concerning your second question. There are 3 completely independent names in the game. The first one is the name of the DDL source. Within this DDL source you define the CDS view named Z_DEMO_REVENUES (in the above example). The naming is not restricted to the “16 characters” as is the case for ABAP dictionary views (created in SE11). In addition to the CDS view you must maintain the annotation @AbapCatalog.sqlViewName, which is the name of the SQL view on the database. Since the CDS view name and the SQL name share the same global namespace they both have to be unique.

      Maybe one remark: The CDS view name Z_DEMO_REVENUES is the “name of interest”, since it should be used e.g. in OpenSQL queries, in other CDS views based on the vie Z_DEMO_REVENUES, etc.

      Cheers,

        Jasmin

      (0) 
      1. Albrecht Gass

        Hi Jasmin,

        thanks for your detailed response, let me try to rephrase it in my own words.

        With the association in the above example I could add an additional filter (where clause) on view z_demo_customer that refers to field buyer_guid from view z_demo_revenue even though it is not part of the field list in z_demo_customer. Is that the benefit of associations?

        Can I create association only with views or also with regular tables (column and row tables)?

        Thanks

        Albrecht

        (0) 

Leave a Reply