Technical Articles
Filtering on Association property using Table Function/AMDP (VS ABAP Virtual Elements)
Introduction
Filtering on the Association property(sub entity) via the odata service(with SADL & CDS) is not a straight forward task especially if the cardinality is 0..N. I’ve already written a blog post about filtering the entity data based on the sub entity(association) property using Virtual Elements in CDS view.
But in this post, I will show the way to apply filter on the association property using the table function(AMDP) approach.
Use this approach in case if the Virtual Elements are not available in your ABAP version as from my understanding Virtual Elements performance is way better compared to Table function approach.
Table Function & AMDP approach
If we use the virtual elements as described in the above blog, SADL layer will generate an “EXISTS” statement in the where condition dynamically and filter out the parent entity data.
As we cannot use that approach in AMDP, we use STRING_AGG function to concatenate multiple items fields and produce a single string. Follow this blog by Felipe de Mello Rodrigues for more details
and we expose table function as an association in the root CDS view but with cardinality 0..1. By this way, we can use expose the field which has all the items data concatenated in the root CDS view and from the UI, filter can be applied on that field.
Table Function: To consume AMDP
@ClientHandling.type: #CLIENT_DEPENDENT
@EndUserText.label: 'Flight Country Search Table FUnction'
define table function ZC_FLIGHT_COUNTRY_FROM with parameters
@Environment.systemField: #CLIENT
p_client: s_mandt
returns {
key client : s_mandt;
key carrid : s_carrid;
from_country : text240;
}
implemented by method ZFLIGHT_CSEARCH=>COUNTRY_FROM;
AMDP: Using STRING_AGG to concatenate multiple countries from which the Flight starts into a single field.
CLASS ZFLIGHT_CSEARCH DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb.
CLASS-METHODS COUNTRY_FROM FOR TABLE FUNCTION ZC_FLIGHT_COUNTRY_FROM.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.
CLASS ZFLIGHT_CSEARCH IMPLEMENTATION.
METHOD COUNTRY_FROM BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY
USING spfli.
return select mandt as client,
carrid,
STRING_AGG ( countryfr, ',' ) as from_country
from spfli
WHERE mandt = :p_client
GROUP BY mandt, carrid;
ENDMETHOD.
ENDCLASS.
CDS View: same cds view which is used in Virtual elements blog but enhanced with table function consuption
@AbapCatalog.sqlViewName: 'ZVCCARFLTTEST'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Car1 Test'
@OData.publish: true
define view ZC_CarrierFilterTest
as select from scarr
left outer join ZC_FLIGHT_COUNTRY_FROM(p_client : $session.client ) as _countrySearchTblFnc on scarr.carrid = _countrySearchTblFnc.carrid
association [0..*] to ZC_SPFLI1 as _spfli on $projection.carrid = _spfli.carrid
association [0..*] to sgeocity as _geoCity on $projection.country_from = _geoCity.country
{
@UI.lineItem: [
{ position: 10 }]
key scarr.carrid,
@UI.selectionField: [{ position: 20 }]
@UI.lineItem: [{ position: 20 }]
scarr.carrname,
@UI.selectionField: [{ position: 30 }]
@UI.lineItem: [{ position: 30 }]
scarr.currcode,
@UI.lineItem: [{ position: 50 }]
scarr.url,
@ObjectModel.virtualElement
@ObjectModel.filter.transformedBy: 'ABAP:ZCL_VE_CAR1'
@EndUserText.label: 'Country From Virtual Element'
@UI.selectionField: [{ position: 50 }]
@Consumption.valueHelp: '_geoCity'
cast( ' ' as land1 preserving type ) as country_from,
@EndUserText.label: 'Country From table Function'
@UI.selectionField: [{ position: 60 }]
/**** Exposing the table function field in the main CDS view, not mandatory in case of association*****/
_countrySearchTblFnc.from_country as country_from_tblfnc,
/* Expose the associations*/
_spfli,
_geoCity
}
All done, but only thing pending is to inform the front end developer to send the filter via “Contain” condition and not “Equal” condition, as we have all the child items concatenated in a single field.
Note: In the above screenshot, I am sending the filter as *DE* (contains condition).
Some more Technical Details for the Geeks
I liked and also received some nice feedback for this header, so whenever it is possible, I will reuse it 😛 😀
So in the beginning, I mentioned that the performance is better while using ABAP virtual elements instead of Table function. I came to this conclusion by testing it out on bulk data, parent table (100 thousand ) and item table around (500 thousand).
When the Virtual Elements are used,
- Filter is first applied on the item data directly at first. (less data, filtered)
- and the filtered item data will be joined with header data for filtering out the header data
When the Table Function is used,
- First all the item data will be aggregated via STRNG_AGG function (more data)
- Then filter will be applied on the item data(less data, filtered)
- At the end, item data will be joined with the header data.
Here the performance hit is at the items aggregation level, where rows will be concatenated into a single line in the case of Table Function scenario. It is taking about 75% of the total execution time.
I used vizplan to do this. check the below blog by Ravi Dasari
https://blogs.sap.com/2018/12/31/planviz-analysis-on-cds-views/
Conclusion
If you have access to Virtual elements use it and if the system is old and supports table function, use this approach.
Let me know your thoughts on this 🙂
Thanks,
Mahesh
So I've read this - and went back and read the previous blog. Nice job bringing them together.
AMDP - I don't understand why you didn't use any other code prior to the selection. You could easily limit the records and avoid the string_agg functionality. Is there a reason?
Thank you!
Michelle
Thanks for the feedback Michelle 🙂 🙂
You can see I am exposing CDS view as the odata service and "Limiting" of the data data comes from the odata service dynamically by "SKIP" and "TOP" parameters. Then SADL Layer here will pass that limit filters to the CDS view select query dynamically. So for this reason, i shouldn't limit the data that AMDP produces, it should be limited dynamically from the filter coming from the top level cds view else it will produce inconsistent data.
But a LIMIT filter on a CDS view is applied only at the end of the data selection. Here in our case, first
But incase of Virtual Elements scenario, there will be no aggregation, SADL Layer use "EXISTS" statement on the CDS view dynamically and so the performance is increased by many folds.
Thanks,
Mahesh
Makes sense to me now.
Thank you!
Super cool!! Nice blog Mahesh :)..
Regards
Vijay
Thanks Vijay 🙂
Good one Mahesh. Very nice informative blog.
Can't we use CDS Table Function as association instead of left outer join?
because i tried to write association to CDS Table function but it was giving syntax error.
Regards,
Balu