A room with an ABAP CDS view

In Björn Goerke’s keynote speech from Barcelona Teched 2015, you may recall that he had crash landed on Mars, and was attempting to escape mars with the help of all of the latest SAP tech available. One of the technologies that we took away from teched, and that Björn used in his keynote was that of the CDS view.

But what if Björn had crashed on Mars and was without a Hana DB? what if he hadn’t upgraded yet to full blown Hana, but he wanted to prep for such a future eventuality. That is a similar situation to what I find myself in with my current employer. We have crashed landed too, but we are even further out than Mars… and we have no Hana DB, but can we prep in advance for HANA? perhaps by using CDS Views on our current Oracle DB.

Well it turns out that the answer is a qualified yes – you can run ABAP CDS views outside of HANA (provided that your ABAP system components are at a high enough level (we are at 740 and past SP8), and that your underlying DB is also at a high enough level (ours is). From Horst Keller’s blog (views come in 2 flavours) we learned that CDS views come in 2 different implementations:

1) HANA CDS views (you’ll never guess but these ones don’t work so well on Oracle), can only be edited in Hana Studio.

2) ABAP CDS views – these can be defined and edited in Eclipse and do work on Oracle, and other non SAP DBs too!

So then its time to see if the ABAP CDS views can help me in my escape from my own predicament, remember we have crash landed on Enceladas which as you can see is a moon of Saturn, this is the view from my room on Enceladas of my broken buggy:

To be able to make it back to earth we must repair our rover, and for this we need to order a space suit from Earth, to be delivered by NASAFEDEX.

To place the order we must be able to construct a view of three tables in our ABAP system, these tables are MARA, MAKT and MARC, they contain the details of the spacesuit, that we need to get to and repair our rover, which can then call our spaceship to escape the dreaded Enceladas!

So without further ado lets jump straight into Eclipse and build the CDS views to get us the hell out of here!

To create an ABAP CDS view I simply login to Eclipse, choose File->new->other and then choose DDL Source from the pulldown list.

The ddl source is a new syntax that lets you define in detail your new Core data services view. It is important that the data dictionary name you provide for the view (in red ring below) is not the same name as the ABAP CDS view name (in Green ring below).

Here we select our material, the material description, and also some sizing and extra descriptive data. We are then specifying in the where clause that we want all of the descriptions in English (as we have lost our babel fish in the crash landing) and we are only interested in data from site 0114 (thats the closest site to the European space terminal) and that we want extra-large size 005 (as we ate too much Enceladas and Xmas pud!).

For the low down on CDS syntax and features please take a look at Chris’s great blog here.

So really the view is defined as a select statement, defining the join conditions (on matnr above), the fields you want returned (matnr, maktx, ernam, mtart, matkl, size1 and werks) and the where clause.

Now we need to get the data out of our CDS view and to do this we must write a few lines of ABAP:

REPORT zjsp_cdsview_basic.

* Our class for ordering our spacesuit

CLASS zcl_jsp_space_clothes DEFINITION FINAL.

  PUBLIC SECTION.

    TYPES:

      BEGIN OF tp_spacesuits,

        matnr TYPE mara–matnr,

        maktx TYPE makt–maktx,

        ernam TYPE mara–ernam,

        mtart TYPE mara–mtart,

        matkl TYPE mara–matkl,

        size1 TYPE mara–size1,

        werks TYPE marc–werks,

      END OF tp_spacesuits.

    TYPES:

      tp_spacesuits_tty TYPE STANDARD TABLE OF tp_spacesuits.

* its a factory class, for syntactical convenience (did you know you can create Factory classes easily

* with the CTRL-1 assist options in Eclipse!)

    CLASS-METHODS create

      RETURNING

        VALUE(r_result) TYPE REF TO zcl_jsp_space_clothes.

    METHODS:

      main.

  PRIVATE SECTION.

    DATA:

* This attribute when filled will contain the spacesuit that we can then order

      mt_spacesuits TYPE tp_spacesuits_tty.

    METHODS:

* Here we define the method that will read our spacesuits from the underlying tables

     cds_read_space_clothes_np

        EXPORTING

         et_spacesuits TYPE tp_spacesuits_tty,

      display.

ENDCLASS.

CLASS zcl_jsp_space_clothes IMPLEMENTATION.

  METHOD main.

* read the space suits

    cds_read_space_clothes_np( ).

* display the results

    display( ).

  ENDMETHOD.

  METHOD create.

   CREATE OBJECT r_result.

  ENDMETHOD.

* Here we are calling our ABAP CDS View note the view select is not so different from a regular

* open SQL select, one difference is that ABAP variables are passed with @ symbol, there are other syntactical

* differences for more complex selects, but the syntax is very easy to pickup if you are familiar with open SQL.

  METHOD cds_read_space_clothes_np.

    SELECT * FROM zlo_mar_cdsvw_2

              INTO TABLE @et_spacesuits.

    mt_spacesuits = et_spacesuits.

  ENDMETHOD.

* Here we can display our selected data, which will simultaneously be transmitted to NASAFEDEX,

* thanks to an additional ALV control that NASA have kindly added for us (not covered by this blog)…

  METHOD display.

    cl_salv_table=>factory(

       IMPORTING

         r_salv_table   =  DATA(lv_alvtable)

      CHANGING

        t_table        = mt_spacesuits

    ).

*      CATCH cx_salv_msg.    “

   lv_alvtable->display( ).

  ENDMETHOD.

ENDCLASS.

START-OF-SELECTION.

data gv_runmode type char1.

* Instantiate our class and run our main method

  zcl_jsp_space_clothes=>create( )->main(  ).

Now its just a matter of executing our CDS view in ABAP on an Oracle DB:

So as you see it is possible to run CDS views from ABAP with an Oracle DB backend – and thus to be able to escape Enceladus.

In fact I think I hear my spaceship landing to come and rescue me!

What we can take away from all of this is that CDS views can be used from a non HANA DB, just as long as your DB and ABAP version are at a high enough level.

But why would you do this unless you are marooned in space? well for one thing, you could create CDS views for heavy duty select statements, in preparation for a future migration to a Hana DB.

During Barcelona Teched 2015 I was assured that the CDS views should perform considerably faster than their equivalent SQL statements even on Non Hana DBs, but more about that in my next blog – A kill to an ABAP CDS View…

That’s all for today, if you have any comments or views about any of the above please get in touch with me in the comments section below.