Develop Powerful Analytical Queries using ABAP Core Data Services (CDS)

[MarkGreen]

Analytical Queries play an important role in SAP S/4HANA analytics. Analytical Queries sit on top of cubes and generate meaningful data sets that business users can explore using their own front-end tools, such as Fiori Review Booklets, SAP Analytics Cloud and Analysis Office (XL). Analytical Queries are developed using ABAP CDS in the ADT tools of Eclipse.

A new, free-of-charge Learning Journey is provided to help you quickly develop skills in this popular topic. The Learning Journey can be found here.

The CDS source files and data used in the course are available to all learners, along with instructions to setup your own cloud ABAP environment. This means you can follow along with the course, or practice what you have learned in your own environment and even try out your own ideas without having to setup a data model from scratch.  You will find the course files and instructions for importing into your own system here.

We updated the course recently (July 2025) and it now includes the important topic 'Working with Structures' plus some clean-ups and corrections.

For the next update of the course (planned end of year 2025) we will add content for:
  - How to implement time dependency hierarchies and attributes
 - How to implement commenting 
 - Elimination of internal business volume
 - Advanced OLAP features

For subscribers to the SAP learning platform (learning.sap.com), we also offer access to unlimited SAP Live Sessions where you can engage with experts in short, live sessions on topics around any SAP topic, including CDS analytical query development.  We have already run various sessions covering CDS analytical query development and more are already scheduled. You can also access recordings of Live Sessions that you missed.  For details click here. 

[David_Mohn1]

Hello @MarkGreen ,

the learning journey about Developing Analytical Queries is really great content. I appreciate it.

I have one issue regarding the consumption of "transient views" though.

During the learning course from time to time you choose "Data Preview" and then a Fiori App opens.

When i choose "Data Preview" Eclipse shows error message with something like "Entity XYZ can´t be used here". However, when i build a cds view with "define view" or "define view entity" a data preview is possible in eclipse. I am then consuming the consumption view in the App "Query Browser" (multi dimensional web dynpro report).

I am using S/4 HANA 2023 FPS 2 OnPremise and i am wondering if "data preview in eclipse" and "Fiori App Query Browser" are working with "transient views".

In another blog you shared a video from Giselle Lin and right at the beginning she is showing and later using the Fiori App "Multidimensional Analysis" F5152A. Is this the App you are using in the learning course? This App is brand new 2502 for Cloud customers and not yet available for OnPrem customers.

So which option do i have to consume transient views in S/4 HANA 2023 OnPrem ? 

Thank you 😀

David

[David_Mohn1]

Update:

Actually, based on the Fiori App Library the Fiori App "Multidimensional Analysis Reuse Component" (F5152A) is available for S/4 HANA OnPrem 2023 but the configuration sub section does not provide a business role or catalog so i am wondering if this App is really available and if this App i should use to consume transient views.

Beside that there are two more Apps Multidimensional Analysis (Key User) F7258 and Multidimensional Analysis (Developer) F7259 both are available only for cloud customers 2502 at the moment.

So my question still stands, how can i consume "transient views" on 2023 OnPrem?

[stefan_unnebrink]

Hello @David_Mohn1

You are right, the special “Data Preview” feature in the ABAP Development Tool (Eclipse) is not available in on prem systems 2023 or below. It is planned for the next on prem release (most likely 2025).

Instead, you can use the transaction RSRT in SAP GUI. It works similar to the preview.
The name of the query is “2C” followed by the CDS name of the analytical projection view. If the projection view has the header annotation Analytics.technicalName, then the name of the query is “2C” followed by this technical name. The annotation Analytics.technicalName should be used if an old query view (view entity with header annotation Analytics.query:true) is migrated to a projection view.

When you want to run an old query view, then you have to use the name "2C" followed by the SQL-view name.

To pass the authorisation check, your user needs authorization for SDDLVIEW ( DDLNAME = CDS name) or alternatively S_RS_COMP (RSZCOMPID = 2C-name).

Regards,

Stefan

[David_Mohn1]

Hello @stefan_unnebrink ,

thank you, RSRT is really helpful. I am able to test the transient view in S/4 system.

I also want to consume the "transient view entity" via Fiori App F1068 (Query Browser) (-> Multidimensional Web Dynpro Report) or any other front-end e.g. Analysis for Office or SAC.

@EndUserText.label: 'First CDS Analytical Projection View'
@AccessControl.authorizationCheck: #NOT_ALLOWED

define transient view entity ZCO_Transient_View1
  provider contract analytical_query
as projection on I_ActualPlanJrnlEntryItemCube

 Is this possible? What annotation do i have to add to the cds view?

I tested with @Analytics.technicalName: 'ZCO_Transient_View2' but it does not show up.

When i use "define view" i add two annotations about "analytical query" and the cds view can be comsumed in the app F1068.

@AccessControl.authorizationCheck: true
@AbapCatalog.sqlViewName: 'ZCO_SQL_COST'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@EndUserText.label: 'Cost Center Master Data'

@Analytics.query: true
@ObjectModel.modelingPattern: #ANALYTICAL_QUERY

define view ZCO_C_COSTC1 as select from I_CostCenter

Thank you

David

[giselle_lin]

Hi @David_Mohn,

To run the cds query in web dynpro Query Browser, AO or SAC, no additional annotation is needed.

Similar as in RSRT explained by Stefan, the query name is:
2C<sqlview name> for CDS view annotated with @analytics.query: true

2C<projection view name> for projection view

If the view has annotation @analytics.technicalName, the query name is: 2C<technical name>. (Usually @analytics.technicalName is only needed to keep the old query name when a query is migrated from CDS view to projection view query)

In your case, the query name is: 2CZCO_Transient_View1 for the transient view, 2CZCO_SQL_COST for the second one.

To run it in web dypro query browser, you can select the query view in the app and click preview. that is to use the app URL like this:
https://xxxx:xxx/sap/bc/webdynpro/sap/fpm_bics_ovp?bsa_query=2CZCO_Transient_View1&sap-client=xxx&sa...

However, to run it in AO or SAC, please note the query view need to be set to 'C1 released'.  See details here. 

After this, user with authorization of this query can run it in AO or SAC.

Best regards,

Giselle

[David_Mohn1]

Thank you @stefan_unnebrink  thank you @giselle_lin  for your guidance & patience.

I was able to consume the "transient view / V3 queries" via AO after i used Eclipse -> Properties -> API C1 released and i also "changed" the authorization.

However, still no luck with "View Browser". I just came across a blog from 2023 where Andreas Riehl posted a comment that in OnPrem 2023 View Browser is not supported yet and that i have to wait for OnPrem 2025.

CDS Analytical Projection Views - the new Analytic... - SAP Community

Guess i have to use "V2 queries" for now.

Kind regards

David

[David_Mohn1]

Ok, wow. I am very surprised that the link @giselle_lin  provided also works but the Fiori App "Query Browser" does not work/show the project view. Can you explain why that is? 

There has to be a way to place that link inside a "Fiori Tile" to call the provider view this way? That would be amazing.

Kind regards
David

[giselle_lin]

Hi @David_Mohn,

Sorry I didn't make all clear in the last reply. Query Browser app is only to preview queries created in Custom Analytical Queries app (CDS view name starts with YY1, or ZZ1), therefore queries created in ADT will not show up there. See https://help.sap.com/docs/SAP_S4HANA_CLOUD/a630d57fc5004c6383e7a81efee7a8bb/3a24b854ee8f8d21e1000000...

So the frontend to directly consume projection view created in ADT on S4 2023 is AO or SAC. 

At the same time, to access projection view query in SAC or AO, the better (recommended) way to setup authorization is to use S_START Authorization for INA1 Service, see  https://help.sap.com/docs/SUPPORT_CONTENT/bwplaolap/3956877110.html?locale=en-US

With this, the query don't need to set C1 released any more. The authorized queries in INA1 service will listed in AO or SAC open query box.

Best regards,

Giselle

[David_Mohn1]

Hello @giselle_lin ,

thank you again for the help. I managed to setup Service Definition and Service Binding. Also authorization for S_START with INA Service. Consumption on SAC is now possible.

I also removed the setup of "Contract C1" again and access is still working with the three options described. WebDynproLink/BSA Query, Analysis for Office, SAC).

Kind regards

David

[albertosimeoni]

Hello All,

I have a question:

from what I know, you can not specify a cache in the BEx query generated by the activation of CDS analytical projection view, as they are based on a transient provider.

What are caching strategies available other than Dynamic Cache object?

Best Regards,

Alberto

[stefan_unnebrink]

Hello Alberto,

the OLAP cache you know from BW-queries on top of BW InfoProvider (ADSO, HCPR) is not available. The OLAP cache is in sync with the data from the InfoProvider. This is possible, since the BW controls the data flow. In CDS this is not possible. Furthermore,  the data the Analytical Engine selects is user dependent (DCL!). This is not foreseen in the concept of the OLAP cache.

Only a local cache is available, which is used during a user session.

At the moment "dynamic Cache" is the only session independent cache. There are plans for a "static cache" which is similar to "dynamic cache" but more integrated with CDS. Also annotations are planned to control the usage of this cache for a single CDS analytical query.

Regards,

Stefan

[albertosimeoni]

Hello Stefan,

Thank you for the informations,

Do you think that a viable / temporary solution could be using HANA static result cache? (But I do not know if it is feasible as the CDS view entities do not have database artifacts, I can make some tests.) 

Probably it can be triggered with HINTS.
I know we are on embedded Analytics (real time reporting), but using cache for few seconds / minutes could improve performances in context of many users / repeated queries.
What do you think?

[albertosimeoni]

I am deep diving and testing the behavior of queries defined with "transient view entity" statement.

A thing that I notice is that when I consume attributes from am associated #DIMENSION, the execution behaviour for me is not good:

1) Application server (AS) reads from the #CUBE view with aggregation (group by).

2) Application server issues a FDA Write to the database putting every attribute ID to join with master data of the #DIMENSION view.

3) the join with fact is effectively done in AS !!!

I try to apply this annotation @analytics.settings.olapPushdown: #MAX

and I notice the correct change in RSRT in query properties.

But with trace ST05 I keep getting the "FDA Write" behaviour.

How can I control this? is this a limit of transient provider?

Thanks,
Alberto

[stefan_unnebrink]

Hello Alberto,

your observation is correct, that display-attributes and also texts (for dimensions and display attributes) are read by the analytical engine in a different step and the "join" is done in the application server.

"Pushdown" only influences the way how cube data is processed. With "pushdown" e.g. exception aggregation is handled by the HANA. Without "pushdown" the analytical engine will add an additional "group-by" to the statement and calculate the exception aggregation in the application server. Usually "with pushdown" is faster but there are situations where the handling in the application server is faster.

This (reading cube-data and attributes and text separately) is standard behavior of the analytical engine and not specific to CDS. 

If you have problems with the performance of reading attributes, you should open an incident. Then some experts will analyze and check if it can be optimized.

Regards,

Stefan 

[ankita489]

Hello,
How to consume analytical projection views in a web dynpro application without using any ABAP code and if you can explain the configuration step by step.

Thanks

Ankita