Skip to Content
Author's profile photo Julian Phillips

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:

/wp-content/uploads/2016/01/saturn_rise_861376.jpg

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.

/wp-content/uploads/2016/01/cds_view_noparams_861394.jpg

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 maramatnr,

        maktx TYPE maktmaktx,

        ernam TYPE maraernam,

        mtart TYPE maramtart,

        matkl TYPE maramatkl,

        size1 TYPE marasize1,

        werks TYPE marcwerks,

      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:

/wp-content/uploads/2016/01/spacesuitselection_861479.jpg

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!

/wp-content/uploads/2016/01/ship_rescue_861481.jpg

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.

Assigned Tags

      15 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Horst Keller
      Horst Keller

      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

      Uh-huh, πŸ˜• .

      Always thought, the DDL of CDS is instanciated as native SQL on the DB ...

      Author's profile photo Julian Phillips
      Julian Phillips
      Blog Post Author

      Yeah I think the Teched advice was a bit iffy - not to give away too much - but that's a part of the next blog.

      Author's profile photo Horst Keller
      Horst Keller

      None of the guys I know can have said this πŸ˜‰

      Author's profile photo Richard Harper
      Richard Harper

      Does that mean you're quarentining Julian because he's bought an alien disease back from Enceladas ?? πŸ˜‰

      Author's profile photo Former Member
      Former Member

      Hi Julian. Great write up! I really liked your story.

      The only thing I would not agree with you is an ultimate need of HANA to make your CDS working faster. Have you ever thought to place your Oracle Database on Exadata with In-Memory option ON? Exadata can accelerate performance of your CDB views even more HANA avoiding the risk of changing RDBMS πŸ˜‰

      Author's profile photo Julian Phillips
      Julian Phillips
      Blog Post Author

      Hi Evgeny,

      well that is an interesting idea, that had not really occurred to me, and definitely something to look into and consider, thanks for the suggestion. That said if/when the overall decision of the project I'm working on is to move to HANA, I can guarantee that CDS views performance will not be top of the criteria for that decision, just a small part of it, but as a customer we definitely need to evaluate all of the options, I believe that IBM also has a competing product here...

      Author's profile photo Serdar Simsekler
      Serdar Simsekler

      Hi Evgeny

      What a shame they forgot to default the in-memory option to true! Now, we need to take the hassle of setting it to true. But I am sure it's worth it. πŸ˜‰

      Cheers...

      *-- Serdar

      Author's profile photo Former Member
      Former Member

      Hi Phillips,

      How to view(identify) standard CDS views delivered by SAP ECC6 or S/4 etc..

      Thank you

      regards,

      Rv

      Author's profile photo Julian Phillips
      Julian Phillips
      Blog Post Author

      Hi Raghuveer,

        I can only answer for ABAP CDS views, which were introduced in SAP ECC 7.40 SP5. If you have this version or higher, then its just a matter of using the ABAP development toolkit in Eclipse and browsing the package you are interested in, choose dictionary and then DDL sources - and you should see the available DDL source (CDS Views) in Eclipse.

      //Julian

      Author's profile photo Former Member
      Former Member

      Hi Julian,

      Thanks for your response.

      Yes, I did navigated and all I see is below structure, there are 100's of packages & I couldn't find DDL source so far. I am not a developer can you advice what is missing here.

      below system is S/4 HANA (Simple Finance) 

      Capture.JPG

      Author's profile photo Julian Phillips
      Julian Phillips
      Blog Post Author

      Hi,

        unfortunately/fortunately (depends on your perspective)  I don't work for SAP - and I don't have access to an S/4 Hana simple finance system, so sorry but I'm not able to assist here.

      //julian

      Author's profile photo Peter Inotai
      Peter Inotai

      In our SAP_BASIS 740 SP 7 / SAP ERP Ehp 7 SP 5 (non-HANA) system there are CDS views in package MMPUR_HDB / MMPUR_HDB_SFWS_OPT_POH for example. Looks like this:

      ERP_CDS.JPG

      Author's profile photo Former Member
      Former Member

      Thank you Peter.

      Got to know, Table TADIR in SE16; PGMID = R3TR, OBJECT = DDLS; will list CDS views 

      Author's profile photo Peter Inotai
      Peter Inotai

      Thanks for the info, didn't know it.

      Peter

      Author's profile photo Horst Keller
      Horst Keller

      There's also DDLDEPENDENCY ....