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:
* Our class for ordering our spacesuit
CLASS zcl_jsp_space_clothes DEFINITION FINAL.
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.
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!)
VALUE(r_result) TYPE REF TO zcl_jsp_space_clothes.
* This attribute when filled will contain the spacesuit that we can then order
mt_spacesuits TYPE tp_spacesuits_tty.
* Here we define the method that will read our spacesuits from the underlying tables
et_spacesuits TYPE tp_spacesuits_tty,
CLASS zcl_jsp_space_clothes IMPLEMENTATION.
* read the space suits
* display the results
CREATE OBJECT r_result.
* 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.
SELECT * FROM zlo_mar_cdsvw_2
INTO TABLE @et_spacesuits.
mt_spacesuits = et_spacesuits.
* 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)…
r_salv_table = DATA(lv_alvtable)
t_table = mt_spacesuits
* CATCH cx_salv_msg. “
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.
Uh-huh, 😕 .
Always thought, the DDL of CDS is instanciated as native SQL on the DB ...
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.
None of the guys I know can have said this 😉
Does that mean you're quarentining Julian because he's bought an alien disease back from Enceladas ?? 😉
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 😉
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...
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. 😉
How to view(identify) standard CDS views delivered by SAP ECC6 or S/4 etc..
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.
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)
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.
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:
Thank you Peter.
Got to know, Table TADIR in SE16; PGMID = R3TR, OBJECT = DDLS; will list CDS views
Thanks for the info, didn't know it.
There's also DDLDEPENDENCY ....