SAP HANA ABAP: CDS/AMDP – The Real Picture

CDS and AMDP have been talk of the town since long time. SAP HANA Developers are trying their level best to try their hands on both of these artifacts. Not to mention they face a lot of challenges while achieving all their reporting requirements using these, which were being done at ABAP layer( SE38 ) previously

Challenges/Solutions:

1. In understanding what’s the difference between the performance of a simple select query which is written at the ABAP Layer and the same select query written at the DB layer using CDS or AMDP using SQL Script Code.

• If we consider the ease of programming, for the ABAP developers its always an easy task       to write the code at SE38 SAP GUI using Open SQL. And after ABAP New Syntax ( NW 7.4 Onwards ) ease of programming at ABAP layer has taken a drastic up-gradation.
• We can perform most of the magic which SQL Script code provides at the ABAP layer itself using ABAP New Syntax.
• Ultimately both the select queries( ABAP Layer query, SQL Script query) get executed at the DB Layer only. The only difference which I could perceive is that we are writing the code at DB Layer using SQLScript and in case of Open SQL its at ABAP Layer.

2. If I want to use CDS for reporting, how I am going to handle the select option which we declare at ABAP Layer. Although SAP has provided this feature to handle the parameters which we declare at (Selection Screen ) in CDS.

Most of us who are acquainted with CDS knows , whenever we create a CDS, two types views are created:

• DDL SQL View ( Which is a Data Dictionary Object, we can view is using SE11 transaction at SAP GUI just like other traditional Views of Data Dictionary ). However, in a non-editable form.
• CDS View Entity (We cannot open it using SE11 Transaction, can only be viewed at   ADT ABAP perspective at eclipse environment. This is the main CDSview entity.

This question is always asked that “How to handle Select-Options in CDS? “There are two ways, one of which is 99% correct:

• CDS views are just like another Data Dictionary Object in ABAP repository, so you can directly write a select query on the CDS view with where clause using in operator in case you want to filter the records from the CDS view based on a select-option. This way is   same as selecting from a table with where condition in SE38 programming.
• Define a CDS view with parameters, having two parameters of the same type.For eg: if we need to handle a select option of type matnr. Then CDS view should have two parameter of type matnr. And in the where condition in the SQL script code of CDS write:    

• Pass the low value of select option declared at the selection screen  to one of the CDS parameter and the high value to another parameter. There is just one issue with this method, that it will not handle the Select option if the user enters multiple values in the select option using single value entry tab at selection-screen.

3. In case of AMDP, you can find many blogs sharing a way out to handle select-options using dynamic where clause. We can create dynamic where clause using the class Cl_SHDB_SELTAB, however SAP Note 2124672 has to be applied to make this class available. Even if this class is not available you can use CL_LIB_SELTAB to create a dynamic where clause to be passed to the AMDP method. Although the latter is an obsolete class.

                        

ex_data =  APPLY_FILTER ( :ex_data, :so_werks );

• where so_werks is the dynamic where clause created on SE38 using the above mentioned   class. And ex_data is the final output table.
• We write SQLScript code inside the AMDP method. There, if we compare the select on a          single table and APPLY_FILTER clause. The question arises, which works faster. If we have    to select from a single table on the basis of a single where clause. We can use both:

4. If I want to develop a complex report, where 6-7 tables and a couple of business logic is involved, considering the newly introduced features of New ABAP Syntax, should we use CDS or AMDP. Or, should we follow the traditional approach SE38, Open SQL Programming.

• If I am well acquainted with SQL Script programming paradigm and can leverage all the rich   operators and feature of SQL Script I would go for AMDP or CDS. And if I am well versed with  the usage of Annotations / Semantics and SQL Script I would prefer CDS. If I am a beginner I  would definitely prefer Open SQL traditional approach if the performance is not the top level priority.