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. | 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.
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 very much for nice and helpful blog.
ReplyDeleteCould you post more about CDS View and ODATA?