Skip to Content

Hi! In the post I would like to consider important topic of filtering data in ABAP CDS views.

I’d like to thank Sergey Shablykin for close collaboration. Without his participation, knowledge and experience the post would not have appeared.

Introduction

There currently three main ways of filtering data:

  1. Parameters in all level of ABAP CDS views
  2. Variables in consumption level ABAP CDS views
  3. Authorisations with DCL views

1. Parameters

Syntax of using parameters is considered in SAP documentation. In the table below there are pros and cons of parameters functionality.

Pros (+) Cons (-)
With help of @Environment.systemField annotation they could be filled by values of standard system variables. See SAP documentation Only single
Manual transfer of parameters from consumption to basic level of CDS view garantee logic push-down and performing filtration at bottom level Only mandatory, optional are not possible

In my opinion current limitations strongly restrinct area or parameters usage. Parameters are suitable for transfering to logic some numeric values used in calculations of key figures or may be to split complex logic at different scenarios. Usually I try to write a default value and to hide parameter so business user couldn’t see it.

2. Variables

Syntax of using parameters is considered in SAP documentation. In the table below there are pros and cons of parameters functionality.

Pros (+) Cons (-)
Very flexible settings, similar to BEx variables Not obvious transfering of values to bottom level
Could be mandatory or optional
Could be single, interval, range
Support miltiple selections
Could use derivations
Could work with hierarchies

Of course you notice that I write only one limitation in right part of table.

All other part of the post will deal with eliminating of the single limitation and answering a performance question:

Will filtration by variables in ABAP CDS views be pushed down to the most bottom level?

This question used to appear very often in case of S/4HANA and (its part) Embedded Analytics promotion. Nowadays if you have S/4HANA the main stream is not to create “old” ALV reports or develop all analytical reports in separate SAP BW system. You could use embedded analytics and create reports directly in S/4HANA based on ABAP CDS views.

3. Authorizations with DCL views

They are not considered here, for more information see post.

Checking performance of CDS view with variables

The following model was used for testing.

1. Sales Model Tables

1.1. Table of sales transactions. It contains time characteristics, sales manager, customer and amount. Number of records is 133 848.

1.2. Master data table of sales managers contains language dependent text and sales groups. They combined in one table only for minimization of data preparation and manual data input. Number of records is 9.

2. ABAP CDS views

2.1. Sales data transactional view:

@AbapCatalog.sqlViewName: 'XV_Sales'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Performance test'
@VDM.viewType: #BASIC
define view XV_I_Sales as select from sales002 as t1 {
  
  key t1.salesrep,
  key t1.customer,
  key t1.calyear,
  key t1.calmonth2,
   @DefaultAggregation: #SUM
   t1.amount
}

2.2. Sales managers texts view:

@AbapCatalog.sqlViewName: 'XV_IT_SALESREP'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Salesrep'
@VDM: { viewType: #BASIC }
@ObjectModel: { dataCategory: #TEXT }
define view XVITSALESREP as select from salesrep {
   @ObjectModel.text: {
       element: [ 'text' ]
   }
   key salesrep, 
   @Semantics: {language: true }
   key spras, 
   @Semantics: { text: true }   
   text
}

2.3. Sales managers dimension view:

@AbapCatalog.sqlViewName: 'XV_IA_SALESREP'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Salesrep'
@VDM: { viewType: #BASIC }
@Analytics: { dataCategory: #DIMENSION }
@ObjectModel: { representativeKey: 'salesrep' }
define view XVIASALESREP as select from salesrep 
 association[0..*] to XVITSALESREP on salesrep.salesrep = XVITSALESREP.salesrep
{
   @ObjectModel: {
       text: {
           association: 'XVITSALESREP'
       }
   }
   key salesrep, 
   srgroup,
   XVITSALESREP
}

2.4. Sales cube view:

@AbapCatalog.sqlViewName: 'XV_C_Sales'
@AbapCatalog.compiler.compareFilter: true
@Analytics: {
    dataCategory: #CUBE
}
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Performance test'
@VDM.viewType: #COMPOSITE
define view XVCSales as select from XV_I_Sales 
 association [0..1] to XVIASALESREP on XV_I_Sales.salesrep = XVIASALESREP.salesrep
{
@ObjectModel: { foreignKey: {
    association: 'XVIASALESREP'
}}
    key salesrep,
    key customer,
    key calyear,
    key calmonth2,
    key XVIASALESREP.srgroup as srgroup1,
    @DefaultAggregation: #SUM
    amount,
    XVIASALESREP
}

2.5. Sales analytical query view with variables:

@AbapCatalog.sqlViewName: 'XV_Q_Sales'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Performance test QUERY'
@VDM: {
    viewType: #CONSUMPTION
}
@Analytics: { query: true}
define view XVQSALES as select from XVCSales {
  //xv_c_sales 
  @AnalyticsDetails: { query: {
      axis: #FREE,
      display: #KEY_TEXT 
  } }
//  @Consumption: { filter: {selectionType: #RANGE, multipleSelections: true, mandatory: false, hidden: false }}
  salesrep, 
  @AnalyticsDetails: { query: {
      axis: #ROWS 
  }}  
  @Consumption: { filter: {selectionType: #RANGE, multipleSelections: true, mandatory: false, hidden: false }}  
  customer, 
  @AnalyticsDetails: { query: {
      axis: #ROWS
  }}  
  @Consumption: { filter: {selectionType: #RANGE, multipleSelections: true, mandatory: false, hidden: false }}  
  calyear, 
  @AnalyticsDetails: { query: {
      axis: #ROWS
  }}  
  @Consumption: { filter: {selectionType: #RANGE, multipleSelections: true, mandatory: false, hidden: false }}  
  calmonth2,
  @AnalyticsDetails: { query: {
      axis: #FREE
  }}  
  @Consumption: { filter: {selectionType: #RANGE, multipleSelections: true, mandatory: false, hidden: false }}    
  srgroup1,  
  @AnalyticsDetails: { query: {
      axis: #COLUMNS
  }}    
  amount
}

3. Launch query in RSRT

3.1. Enter analytical query

3.2. Execute with following debug options for analysis performance and generated SQL from application server side of view.

3.3. Enter variables. Restrict year and sales group to analyse where and how filtration will be transfered and executed.

3.4. Get resulted sql statement and find restrictions in where clause.

/* Statement Preview (might be truncated) */
SELECT
 A1~CALYEAR AS K____256
, A1~CALMONTH2 AS K____255
, A1~CUSTOMER AS K____257
, COUNT( * ) AS Z____151_SUM
, SUM(  A1~AMOUNT  ) AS Z____259_SUM
FROM
XVCSALES AS A1
WHERE
   ( ( ( (
A1~CALYEAR
= '2016'
 ) ) AND  ( (
A1~SRGROUP1
= '1'
 ) ) ) )
GROUP BY
A1~CALYEAR
,A1~CALMONTH2
,A1~CUSTOMER
ORDER BY
 K____255
, K____256
, K____257
%_HINTS 'NO_RESULT_CACHE'

3.5. Result data set is filtered by year, sales group and aggregated by sales managers (they are in free part of a query). Initial view:

Only 2016 as expected:

Only sales group 1 and sales managers of this group (see master data in paragraph 1.2).

Please note, that texts are shown in english as expected (you could find more information about time-dependent attributes and language-dependent texts in blog). In master data table we have 1 test record in german with sales group A1.

3.6. Query Statistics show that Total Number of Read Records in 3744 and Total Number of Transported Records 936. But this statistics could not demonstrate how this filtration and aggregation result performed at DB level (I mean logic push-down exist or not exist).

4. HANA SQL Trace

To understand how exactly filtration and aggregation from 133 848 to 936 records performed we need to analyse HANA SQL Trace.

4.1. Switch on SQL Trace on at Administration – Trace Configuration – SQL Trace.

4.2. Run analytical report in RSRT, stop SQL Trace and analyse the results.

4.3. Find in trace log generated SQL statement and execute in with Execution plan

EXPLAIN PLAN FOR 
SELECT "CALYEAR" "K____256" ,
 "CALMONTH2" "K____255" ,
 "CUSTOMER" "K____257" ,
 COUNT(*) "Z____151_SUM" ,
 SUM( "AMOUNT" ) "Z____259_SUM"
 FROM /* Entity name: XVCSALES */ "XV_C_SALES" "A1"
 WHERE "CALYEAR" = ? AND "SRGROUP1" = ?
 GROUP BY
   "CALYEAR" ,
   "CALMONTH2" ,
   "CUSTOMER"
 ORDER BY
   "K____255" ,
   "K____256" ,
   "K____257"
 WITH HINT(RESULT_LAG ('hana_long'))

Enter for CALYEAR and SRGROUP1 values 2016 and 1 accordingly.

4.4. Execution plan graph and table format

We have 2 filtering conditions by year and by sales group. In the Execution Plan we see that filtration performed at bottom level in HANA DB.

Result

We considered main filtration options in ABAP CDS views and got an answer for the primary question:

Filtration by variables in ABAP CDS views is pushed down to the most bottom level!

 

Thank you for attention!

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply