Introduction – Purpose of this blog
In this blog, we will see the characteristic specifications of Analytical Query (= ABAP CDS View annotated with “@Analytics.query: true”) when it is executed through analytical engine inside SAP S/4HANA. The following points are covered:
- How an Analytical Query is accessed from front-end reports (like SAP Design Studio/SAP Smart Business generic drilldown app), and SELECT statement is executed
- Then how the authorization check by Data Control Language (DCL*) works with Analytical Queries
* Data Control Language (DCL) is a language which defines authorization for ABAP CDS Views. DCL enables row level authorization restriction on ABAP CDS views by referring Authorization Object within its definition. Standard DCLs have the same technical name as related ABAP CDS View.
For details, please refer to the SAP Blog post;
All About Data Control Language (DCLs)
Background / Motivation
Recently I often see questions like below;
How to analyze performance of Analytical Query?
The questioner traces an Analytical Query with T-cd: ST05, but it does not seem that any SELECT statements do not run on the Analytical Query even though the Analytical Query is executed through T-cd: RSRT / Query Browser. The question is how we can trace and analyze the performance of it.
I answered the question and realized that this topic is a bit complicated and important, so that need to be on SAP Blog post. The purpose of the blog is to illustrate how it is working and implemented in SAP S/4HANA.
Summary of this blog post
To tell the conclusion first, when Analytical Query is executed through analytical engine, the actual SELECT statements run on its Interface view, which is a source ABAP CDS View of the Analytical Query. The SELECT statements do not run directly on the Analytical Query.
For example, let’s consider the following scenario - the front-end report tool “SAP Design Studio” consumes an Analytical Query and forms a multidimensional report. In this case, analytical engine interprets the query request and thus the actual SELECT statements do not run on the Analytical Query, but on its source Interface View. I will trace different types of SAP Fiori application, and compare the trace results.
Let’s see the evidence in the following chapter.
Compare trace results between different types of ABAP CDS View
Overview
In this chapter I will execute and trace following SAP Fiori applications and compare their ABAP SQL trace results.
| No | Name of the app | UI Technology | Types of the source service |
| 1 | F2217 Display Line Items in General Ledger | List report | OData |
| 2 | F0996A Trial Balance | SAP Design Studio | Analytical Query |
| 3 | F1733 Aging Analysis | SAP Smart Business generic drill down | OData Analytical Query (*) |
Then you will see the exact place where ABAP SQL SELECT statements are executed according to the types of source service.
- The List Report app(F2217 Display Line Items in General Ledger) consumes an OData service “FAC_GLV_GL_ACCOUNT_LINEITEMS_SRV” and it uses a Consumption View “C_JournalEntryItemBrowser”, which is intended for OData use – thus this is not an Analytical Query.
- On the other hand, the SAP Design Studio app(F0996A Trial Balance) consumes Analytical Query/BW Query. In this case, Trial Balance app consumes an Analytical Query.
- Finally the Smart Business generic drill down app consumes an OData Service, but in the application (F1733 Aging Analysis), the source ABAP CDS View is also published(annotated) as Analytical Query as well.
(*) UI Technology “SAP Smart Business generic drill down” works with just an OData source service without Analytical Query, but in this app the source ABAP CDS View “C_PhysInvtryQuery” is also annotated as @analytics.query: true. As far as I know, most standard Smart Business generic drill down apps are built this way.
So, we will be able to see the difference of access path among different types of source service. The architecture for each application is below.
Trace Steps
I will perform the following steps for the List Report app(F2217 Display Line Items in General Ledger), the SAP Design Studio app(F0996A Trial Balance) and the SAP Smart Business generic drill down app (F1733 Aging Analysis) respectively, and see their differences.
- Enable trace with T-cd: ST05
- Execute the app directly - F2217 (List Report) or F0996A (SAP Design Studio) or F1733 (SAP Smart Business generic drill down)
- Click “Deactivate Trace” and then “Display Trace”. We can check how SELECT statements are executed.
Result
1. List Report app(F2217 Display Line Items in General Ledger)
In order to see the SELECT statement, sort by “Duration” column in descending order and select the top entry among the program name “CL_SADL_SQL_EXECUTOR”.
In the following trace result, we can see that the actual ABAP SQL SELECT statement runs on its Consumption View of the report – in this case, C_JOURNALENTRYITEMBROWSER.
At the same time, we can also see CDS access control is working on the Consumption View.
This result is very intuitive and easily understandable, because this Consumption View is the one which is directly used by the List Report app.
2. SAP Design Studio app(F0996A Trial Balance)
Next, let’s check with the SAP Design Studio app which consumes Analytical Query. I take the same step as above - sort by “Duration” and select the top entry among the program name “CL_RSDRS_SQL_QUERY”.
The result is below. In this case, surprisingly the Consumption View “C_Trialbalanceq0001” is not on the ABAP SQL SELECT statement. Instead, the Interface View “I_GLAcctBalanceCube” is there.
3. SAP Smart Business generic drilldown app(F1733 Aging Analysis)
Finally, I test a SAP Smart Business generic drilldown app, whose source ABAP CDS View is annotated as OData and Analytical Query at the same time.
Do the same steps and see the trace result. Similarly, the Consumption View “C_APFlexibleAging” is not on the ABAP SQL SELECT statement. Instead, the Interface View “I_APFlexibleAging” is there.
See the details for the SQL Trace.
These are the results of the experiment.
As shown above, the actual ABAP SQL statement runs not on Consumption View but runs on Interface View which is under the Consumption View.
This is a generic specification for any Analytical Queries and how analytical engine treats the request for them, even though it is also published as OData service. It is not limited for standard Analytical Queries, but also applied to custom Analytical Queries.
At first glance, it is so counterintuitive. However, we can consider it in the following way - Analytical Queries technically take a form of ABAP CDS View, but conceptually it is not a “view” or “data source”, but a “query”. A “query” has to be made upon a “data source”, which is, Interface View – in this case, I_GLACCTBALANCECUBE.
In fact, an Interface View under standard Analytic Query is normally annotated with “@Analytics: {dataCategory: #CUBE}” – literally we can consider the Interface View as a data source, and Consumption View(Analytical Query) as a query, not a “view” or a “datasource”.
At the same time, we can see CDS access control is also working on the Interface View. The access control must be working on data source layer, so I think it is now understandable for you if we take the discussion above into account.
Now, I update the architecture slide with some comments:
The important thing is that we can analyze performance/authorization issues of Analytical Queries by understanding the concept. For example, it is not appropriate to check the Data Control Language (DCL) of an Analytical Query itself when we want to see/implement how row level authorizations are working. Instead we must check the DCL of an Interface View of the Analytical Query. This logic does not change even if it is a custom Analytical Queries – a business user may be able to see unintended data when appropriate DCL is not set on the Interface View. We must be careful when configuring row level authorization on a report which uses Analytical Query.
* For authorization issues for ABAP CDS View, we have T-cd: SACM to check this. For details, see the blog below;
Tcode SACM(Access Control Management) to check authorization issues of CDS Views
Conclusion
In this Blog post, you learned the following topics;
1. How the Analytical Query is accessed from front-end reports (like SAP Design Studio/SAP Smart Business generic drilldown app), and SELECT statement is executed
-> Analytical engine receives the request from front-end and generate an ABAP SQL Statement. Analytical engine does not recognize the Consumption View (=Analytical Query) as a data source but recognize the Interface View under the Analytical Query as a data source. Thus, the actual ABAP SQL SELECT Statement is executed on the Interface View layer. We saw the trace result with ST05.
2. Then how the authorization check by Data Control Language (DCL) works with Analytical Queries
-> The actual ABAP SQL SELECT Statement is on the Interface View, so the related DCL is also for Interface View.
Reference
[Questions what I answered]
How to analyze performance of Analytical Query?
Data preview error in CDS view 'C_MaterialStockActual'
[SAP Blog Posts]
About tracing Analytical Queries:
How to analyze query performance for ABAP CDS Views
How to trace authorizations in ABAP CDS views
About DCL:
All About Data Control Language (DCLs)
Tcode SACM(Access Control Management) to check authorization issues of CDS Views
CDS View Row Level Authorizations with Data Control Language (DCL)
Implementing DCL with CDS Views and Roles
Wonder how Data Control Language (DCL) works with ABAP Core Data Services (CDS)?
Hope this blog helps your understanding on these topics.
Brought to you by the S/4HANA RIG
- SAP Managed Tags:
