Data filtration options and performance analysis in ABAP CDS views

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.

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.

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!