Skip to Content
Technical Articles
Author's profile photo Krishma Bhatia

Some useful tips while writing custom Core Data Service(CDS) and ABAP Managed Database Procedure(AMDP)

Hi Friends!

I am very excited to share with you all my very first blog post!!

I recently got the opportunity to work on custom CDS and AMDP in the SAP S/4HANA Implementation project which involved quite complex logic, but it gave me a lot of new learning which I felt like sharing with you all. This was my very first time I worked on CDS and AMDP so thought to share with you all thinking it might be helpful( especially for beginners like me ).I read a beautiful quote somewhere “Knowledge shared is knowledge squared”.

Below are a few of the learnings which I got while working on custom CDS and AMDP:-

  1. You must be familiarized with calling parameterized CDS with a left outer join but to call parameterized CDS with the association, the syntax is quite different. Also always prefer to use association over joins since associations are kind of Joins to fetch data from multiple tables on Join Conditions but these are ‘JOINS ON-DEMAND’ i.e. they will only be triggered when a user would access the required data which needs the Association of tables.  For example, your CDS view has 4 associations configured and the user is fetching data for only 2 tables, the association on the other 2 tables will not be triggered and the system would return the results quickly, so it enables a really high turn-around time as compared to regular SQL JOINS. Coming back to the syntax of calling parameterized CDS with association use the following syntax:-
  define view ZC_TEST_REPORT
  with parameters
      p_comdate :zcom_date
  as select from ZC_TEST_REPORT as _Rpt
     left outer join ZI_TEST_SUBREPORT(p_comdate:$parameters.p_comdate) as _Subrpt                            
                    on _Rpt.ProdHrId = _Subrpt.prod_hr_id
     association [0..1] to ZI_TEST_NONOFR as _NonOfr
                    on _Rpt.ProdHrId = _NonOfr.ProdHrId
                 {
                    key _Rpt.ProdHrId   as ProdHrId,
                        _Subrpt.comtype as Comtype,
                        _NonOfr(p_comdate:$parameters.p_comdate).destination as destination
                 }
  1. Always take care if any column of the internal table on which calculation is being performed( like arithmetic operation) has a null value then the result of the operation will always be null. Therefore take care to check for null value and fill the column values( like with ‘0’ if the value doesn’t exist for int column type)  before performing any calculation else the result will be always null for columns not having any value.

3. If you want to perform division operation in CDS which should have value after decimal point (like 123.45) use division numerical SQL function instead of div in CDS. Div always returns an integer value as a result(123.45 will be 123 only) whereas division allows you to specify the precision of the result so it will return output with a fractional/scale part( so123.45 will be 123.45 only).

Syntax of both these operations are as follows: –

DIV(arg1, arg2)

The integer part of the division of arg1 by arg2.The sign is assigned after the amounts are divided; positive if the arguments have the same sign, and negative if the arguments have different signs. Exception: arg2 has the value 0.

DIVISION(arg1, arg2, dec)

Division of arg1 by arg2.The result is rounded to dec decimal places.

  1. Performance improvement annotations to be used in CDS:-There are majorly 3 usageType annotations that help to improve CDS performance which are as follows:-
@ObjectModel.usageType.serviceQuality  ->Valid values for serviceQuality are: #A, #B, #C, #D, #X, #P.
@ObjectModel.usageType.sizeCategory    ->Valid values for sizeCategory are: #S, #M, #L, #XL, #XXL.
@ObjectModel.usageType.dataClass       ->Valid values for dataClass are:#TRANSACTIONAL, #MASTER, 
                                          #ORGANIZATIONAL, #CUSTOMIZING, #META, #MIXED.

To understand which values to use for each type you can refer to this blog post link:-https://blogs.sap.com/2020/02/06/cds-view-performance-annotation-for-cds-view-performance/

5.  Always mention the alias name of the column after you do any operations or using any other functions like cast else you will get runtime error “invalid column name” when you run CDS or AMDP(CDS Table function).

  1. It is very easy to use filter operation in AMDP to filter out values from an internal table. For example, in the below example if you have an internal table lt_comtype which has column ‘FIELD’ with values such as BROWN, BLACK, and BLUE you can easily filter out in case you want 1 internal table of each type as follows:-
declare lc_brown string := '( FIELD = ' || '''BROWN''' || ' )';
lt_browntype = apply_filter(  :lt_comtype , :lc_brown);
  1. You can call CDS in AMDP using CDS sqlViewName in AMDP and also defining it in USING clause of AMDP as follows:-

CDS:-

@AbapCatalog.sqlViewName: 'ZITESTRPT'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: ‘TESTING Report'

@ObjectModel: {

  usageType.serviceQuality: #D,

  usageType.sizeCategory: #XXL,

  usageType.dataClass: #TRANSACTIONAL

}

define view ZI_TEST_RPT {……….}

AMDP:-

METHOD zamdptest

        BY DATABASE FUNCTION

        FOR HDB

        LANGUAGE SQLSCRIPT

        OPTIONS READ-ONLY

        USING zitestrpt.

lt_table =  select comtype,comdate from zitestrpt as _testrpt where comtype is not null;
.
.
.
ENDMETHOD.

Conclusion:-

The above points will help you understand points to consider for performance improvement and to take care of few important notes while creating custom CDS and AMDP.

I hope you find this blog post useful and benefit from it. Thanks for reading it. I hope you would like it, support it and pardon any mistake in the blog post 😊. Also, please like, comment, share the blog post and let me know any constructive feedback which will help me to write my next blog post better( recently I shifted from ABAP to JAVA technology within SAP so thinking of sharing my journey in the next blog post) 😊. Request you to follow my profile and feel free to ask any questions you have in the comments section below.

Keep learning every day!!

Best Regards,

Krishma Bhatia

Assigned Tags

      26 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Somnath Paul
      Somnath Paul

      Thanks, good one!

      Author's profile photo Krishma Bhatia
      Krishma Bhatia
      Blog Post Author

      Thanks Somnath!!

      Author's profile photo Gaurav Karkara
      Gaurav Karkara

      Congratulations Krishma for your first blog post. Keep up the good work!

      Author's profile photo Krishma Bhatia
      Krishma Bhatia
      Blog Post Author

      Thanks Gaurav 🙂

      Author's profile photo Vigneswaran Mathivanan
      Vigneswaran Mathivanan

      Excellent Krishma..

      Author's profile photo Krishma Bhatia
      Krishma Bhatia
      Blog Post Author

      Thanks Vignesh!!  Also thank you for giving me the opportunity to work on CDS and AMDP  🙂

      Author's profile photo AjeethKumar R
      AjeethKumar R

      Thanks for this wonderful blog!!

      Author's profile photo Krishma Bhatia
      Krishma Bhatia
      Blog Post Author

      Thanks AjeethKumar !!

      Author's profile photo Hemendra Sabharwal
      Hemendra Sabharwal

      Nice blog...appreciate for sharing.

      Author's profile photo Krishma Bhatia
      Krishma Bhatia
      Blog Post Author

      My pleasure Hemendra 🙂

      Author's profile photo vishwanath vedula
      vishwanath vedula

      Enjoyed reading it.

      Author's profile photo Krishma Bhatia
      Krishma Bhatia
      Blog Post Author

      Thanks Vishwanath!!

      Author's profile photo Patrick Van Nierop
      Patrick Van Nierop

      A question though on the "Performance improvement annotations to be used in CDS" section.

      Have you tested/verified that these annotations have an actual influence on the performance? In the comments of the blog you refer to it is mentioned that these annotations aren't known by the database and as such shouldn't have any influence.. so there is some confusion there.

      And seen you have spend so much efforts in creating this blog, I would just like to offer the small suggestion to chose somewhat more descriptive names for your views (and maybe even columns). A view that contains 'report' in it's name is a tad strange, not?

      Author's profile photo Krishma Bhatia
      Krishma Bhatia
      Blog Post Author

      Hi Patrick,

      I used these annotations in the CDS view and in HANA studio when I tested the CDS view it took a lot less time for a huge dataset than when I was executing it without these annotations. Also, thanks for the suggestion, my actual CDS and their column names used in the project was different than this blog post mentioned names 🙂 I will be careful next time while naming them for the blog post.

       

      Regards,

      Krishma

      Author's profile photo Pankaj Yadav
      Pankaj Yadav

      Thanks krishma.. these basics are really important

      Author's profile photo Krishma Bhatia
      Krishma Bhatia
      Blog Post Author

      Thanks Pankaj!!

      Author's profile photo Anil reddy
      Anil reddy

      Good read...

      Author's profile photo Krishma Bhatia
      Krishma Bhatia
      Blog Post Author

      Thanks Anil !!

       

      Author's profile photo Aman Garg
      Aman Garg

      Hello Krishma,

      Thanks for sharing the tips. This was really helpful.

      I have a question. If I want to use a parameterized CDS as an association in my CDS view, does my CDS view also need to be parametrized mandaotrily? For the example which you shared, is it necessary for view ZC_TEST_REPORT to be parameterized?

      Also, could you please suggest is it mandatory to expose a filed from the parameterized CDS association? For your example, you exposed Destination field, can we skip this?

      _NonOfr(p_comdate:$parameters.p_comdate).destination as destination

       

      Thanks,

      Aman Garg

      Author's profile photo Krishma Bhatia
      Krishma Bhatia
      Blog Post Author

      Hi Aman,

      For your first question" is it necessary for view ZC_TEST_REPORT to be parameterized", the answer is yes( if parameters to be passed are not session variables like system_language,client,system_date and user) because it won't allow you to pass parameter value from the selection list. 

      For your second question, I am using the field of associated CDS but it is not mandatory to use the field of associated CDS in calling CDS. It will give a warning in case of association but no warning is given if left outer join or inner join is used.

      Thanks,

      Krishma

       

      Author's profile photo Aman Garg
      Aman Garg

      Thanks Krishma for answering my queries. For  2nd ques, so if I skip associated field in calling CDS, so my code would look something like this

      _NonOfr(p_comdate:$parameters.p_comdate)

      Is my understanding correct? Basically just want to confirm that we pass value to parameters of associated entity when we expose the association as per the syntax.

      -  Aman Garg

      Author's profile photo Krishma Bhatia
      Krishma Bhatia
      Blog Post Author

      Hi Aman, no this will give you a syntax error. In the above example, I am not trying to expose an entity but was trying to show you an example of how to access the field of associated parameterized CD S so either access it with its field(example 1 in below) or you can skip it totally(example 2 in below). Just to be more clear pls see the below examples (both are correct examples):-

       

      Example 1)

      @AbapCatalog.sqlViewName: 'ZIFLIGHTS'
      @AbapCatalog.compiler.compareFilter: true
      @AbapCatalog.preserveKey: true
      @AccessControl.authorizationCheck: #CHECK
      @EndUserText.label: 'testall'
      define view ZI_flights
      with parameters
      zconnid :char4
      as select from scarr as _scarr
      inner join ziflight as _sflight on _sflight.carrid = _scarr.carrid
      association [1..*] to ZI_spfli as _Spfli on _sflight.connid = _Spfli.connid
      {
      key _scarr.carrid,
      _sflight.airpfrom,
      _sflight.airpto,
      _Spfli( zconnid:$parameters.zconnid ).cityto
      }

      Example 2)

      @AbapCatalog.sqlViewName: 'ZIFLIGHTS'
      @AbapCatalog.compiler.compareFilter: true
      @AbapCatalog.preserveKey: true
      @AccessControl.authorizationCheck: #CHECK
      @EndUserText.label: 'testall'
      define view ZI_flights
      with parameters
      zconnid :char4
      as select from scarr as _scarr
      inner join ziflight as _sflight on _sflight.carrid = _scarr.carrid
      association [1..*] to ZI_spfli as _Spfli on _sflight.connid = _Spfli.connid
      {
      key _scarr.carrid,
      _sflight.airpfrom,
      _sflight.airpto
      }

      Though example 2 will give you a warning that association _spfli is not used(no warning given in case left outer join or inner join used).

      I hope I was able to clear your doubt. Feel free to ask any other questions you may have. Happy to help 🙂

      Have a great day!!

      Regards,

      Krishma Bhatia

      Author's profile photo Aman Garg
      Aman Garg

      Thanks Krishma for taking out time to share these examples. And sorry to bother you again with a question. I am all clear with example 1, but with example 2, since ZI_spfli is a parameterized CDS, and we are not even passing parameter value to this CDS, so how would this association even work. Let say we are exposing this CDS as an oData service, and we want to read data from associated entity something like this "/ZI_flights/to_ZI_spfli('connid')" then if go via example 2 path, how are we passing value 'connid' to associated CDS as parameter?

      Thanks,

      Aman Garg

      Author's profile photo Krishma Bhatia
      Krishma Bhatia
      Blog Post Author

      Hi Aman,

      Please do not get confused with example 2 since it is not a real case scenario since we will only associate CDS when we need to use it else why would we associate it and once we call/use it with the needed field as already mentioned we will be passing value to the parametrized zconnid field since it is mandatory.

      Regards,

      Krishma

      Author's profile photo Aman Garg
      Aman Garg

      Sure Krishma, thanks a lot!

      Regards,

      Aman Garg

      Author's profile photo Murli Mohan Irla
      Murli Mohan Irla

      Hi Krishma,

      Thank you for this post,

      • I am implementing scenario of calling "CDS in AMDP using CDS sqlViewName in AMDP" in my system but it is giving me design time error.

        "ZITESTRPT" is client-specific and restricts access to a single client. The calling AMDP method "zamdptest" must also allow this restriction.

      • and on another note if standard CDS view does not bring mandt or client in their view list, then we might not be able to use standard CDS in our AMDP table Function scenario ?

      any hint or comment will appreciate.