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
                 }

2. 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: –

4. 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).

6. 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);

7. 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