CDS Query: Restricted Measures with Exception Aggregation
As explained in CDS views: HowTo use in BW contexts, for a CDS query you can define calculated and restricted key figures and you can use the OLAP feature Exception Aggregation. In the following we take a look at two examples where we combine all three modeling options. We will see that, although they look very similar, there is an important difference between these two queries.
Example where the Restricted Key Figure is defined on Cube Level
We define the restricted key figure already on the Cube level and then use it in the query to define the exception aggregation:
The technical name of the restricted key figure is SeatRKF and it is used in the formula RKFExeptAggr with the exception aggregation MIN regarding the field Airline.
When the query is executed in RSRT, the query result is:
We can see that the 'empty' cells(no posted value for restricted key figure) are filled with zeros and hence the exception aggregation MIN calculates the value 0. The reason for the zeros is that(although in the definition of the RKF we can see that the coding line 'else 0' is commented out) on BW provider level, 'NULL' values are not allowed and replaced by the inital value. In case this is not desired and you can define the restricted key figure on query level as discussed below.
Example where both Restricted and Calculated Key Figure is defined on Query Level
The query definition is as follows(relevant part):
The restricted key figure SeatRKF is used in the formula RKFExeptAggr with the exception aggregation MIN regarding the field Airline.
In contrast to the example above the zeros disappeared in the last column. As a consequence the exception aggregation MIN calculates the value 5775.