Why counting distinct values in ABAP CDS isn’t as easy as you think

Imagine you have a table with the following data in a table of your SAP system:

How do you count the number of different (distinct) plane types in an ABAP CDS View? The answer to that question might be more complicated than you would expect. There are three possible approaches to this task and we will have a look at their pros and cons. If you are in a hurry to find a good solution, try the last one ?

Some sample data and CDS Views

If you want to try this out by yourself, you can generate some test data in your system. I am using the SAP flight data model in my examples and it is available as standard in SAP (NetWeaver) systems. Use the program SAPBC_DATA_GENERATOR to generate some example data. They will be stored in several tables, but we will only need the table SFLIGHT. It contains some data on commercial airline flights such as seat capacity, price, flight date and the plane type which we would like to count. To start with, you can use the following, very simple ABAP CDS Views:

Basic View ZI_FLIGHT

@AbapCatalog.sqlViewName: 'ZISFLI'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: 'Flight Data Dimension'
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.viewType: #BASIC
define view ZI_FLIGHT as select from sflight {
    key carrid, 
    key connid, 
    key fldate, 
    price, 
    currency, 
    planetype,
    paymentsum,  
    seatsmax, 
    seatsocc,
    seatsmax_b, 
    seatsocc_b, 
    seatsmax_f, 
    seatsocc_f
}

ABAP CDS Cube ZC_FLIGHTDATAC

@AbapCatalog.sqlViewName: 'ZCSFLIDAC'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: 'Flight Data Cube'
@Analytics.dataCategory: #CUBE
@Analytics.dataExtraction.enabled: true
@VDM.viewType: #CONSUMPTION
define view ZC_FLIGHTDATAC as select from ZI_FLIGHT {
    key carrid, 
    key connid, 
    key fldate, 
    @DefaultAggregation: #SUM
    @Semantics.amount.currencyCode: 'currency'
    price,
    @Semantics.currencyCode: true
    currency, 
    planetype,
    @DefaultAggregation: #SUM
    @Semantics.amount.currencyCode: 'currency'
    paymentsum,
    seatsmax, 
    seatsocc, 
    seatsmax_b, 
    seatsocc_b, 
    seatsmax_f, 
    seatsocc_f
}

ABAP CDS Query ZC_FLIGHTDATAQ

@AbapCatalog.sqlViewName: 'ZCSFLIDAQ'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: 'Flight Data Query'
@VDM.viewType: #CONSUMPTION
@OData.publish: true
@Analytics.query: true
define view ZC_FLIGHTDATAQ as select from ZC_SBLU_FLIGHTDATAC {
    carrid, 
    connid, 
    fldate, 
    price, 
    currency, 
    planetype, 
    seatsmax, 
    seatsocc, 
    paymentsum, 
    seatsmax_b, 
    seatsocc_b, 
    seatsmax_f, 
    seatsocc_f
}

Using SQL

Coming from the database world, I was searching the internet for the term “count distinct” because this is essentially what we want to do. Most of what I personally found was about the SQL approach to it. Count Distinct is a standard SQL functionality and as such it is also supported within ABAP CDS Views. It looks like this:

@DefaultAggregation: #SUM
count(distinct planetype) as planetypecount

At the end of the ABAP CDS Code you then need to attach a group by clause with all the remaining (not aggregated) columns like this:

group by 
   carrid, connid, fldate, price, currency, planetype, seatsmax, 
   seatsocc, paymentsum, seatsmax_b, seatsocc_b, seatsmax_f, seatsocc_f

But here is exactly the problem: The grouped columns are pre-determined in the code – in this example we will execute the count distinct for every individual line of our dataset. Because of that, the result will be disappointing:

Now let’s see how this aggregates when only carriers and the plane type count is selected:

The only alternative is to remove columns from the grouping criteria – but then we need to remove them from the ABAP CDS View altogether and therefore loose the information. And again: When you access the query, the count will only be correct if you select the anticipated (grouped) columns. This is not what we are looking for, especially if we want to implement drill-downs based on this query.

Side-note:  This count distinct is not supported in CDS Views with the annotation
@Analytics.query: true or in CDS Views based on External Views

Using the DefaultAggregation annotation

Count distinct is essentially an aggregation function. And actually, the code completion for ABAP CDS Views offers the following option:

@DefaultAggregation: #COUNT_DISTINCT

Unfortunately, I was not able to find any kind of actual documentation on how to use it. Here are the only (not useful) SAP Help results I could find:

https://help.sap.com/http.svc/rc/abapdocu_752_index_htm/7.52/en-US/abencds_annotations_frmwrk_tables.htm

https://help.sap.com/doc/f9edb0c2e59e426da97a81719be1d11c/1511%20000/en-US/frameset.htm?5e5d319bd1a74552b99a36dfc739f74d.html

It seems the aggregation function itself is only supported by CDS query views that don’t generate a BEx query (@Analytics.query: true). If there is anybody who has used this, feel free to comment on how to do it.

Using Exception Aggregation

The third method for counting is to use exception aggregation. If you activate the BEx query generation in the CDS query view, you can use the following code:

@AnalyticsDetails.query: 
   {formula: '1'},
    exceptionAggregationSteps:
       [{exceptionAggregationBehavior: #SUM,
         exceptionAggregationElements: ['planetype'] }]}
cast(0 as z_planetypecount) as planetype_count

Please note that for this example, I have also prepared a data element (z_planetypecount) to assign a descriptive label to the new column. The code will execute the given formula for each unique plane type taking into consideration the other selected criteria. So essentially when only selecting Airline and the Plane Type Count it will work like this:

And the result will look like this:

This is correct! And it will adapt to the selection criteria. So, all in all: When trying to count distinct values and you don’t know in in advance which columns should be grouped, this is the way to go. The downside to this is

1. You need to publish this as an Analytics (BEx) query, which limits the functionality of the OData service that gets generated
2. The calculation is not done on the database but instead in the SAP BW engine on NetWeaver level which doesn’t provide the same performance