Skip to Content
Personal Insights
Author's profile photo Dmitry Kuznetsov

BW Query on CDS cube?

After spending some time in S/4HANA Embedded Analytics building Queries in CDS views I came to a realization that it is a painful exercise. While in some (simple) cases it does, indeed, make a good sense, for real analytical queries probably a Query Designer will be a better choice. By Query Designer I mean BEx (t.b. deprecated) or a BW-MT flavor of it. I will use the latter in my examples.

Start

So, where do I start? I start by connecting to an S/4HANA from BW Modeling Tools perspective in Eclipse.

 

Then we proceed the same way as in BW cases. When creating a new Query we need an InfoProvider. In S/4HANA this will be a generated TransientProvider (which is based on CDS view), so don’t forget to tick that checkbox before searching.

OData?

If you are planning on using the query in KPIs or Reports, it is a good idea to check the box for OData service.

Parameters

In many cases our CDS-based “Cube” will have at least some parameters defined. And there is no such thing as optional parameters in the CDS world. So, we need to fill them somehow. Typically, they are prefixed with a P_* in CDS editor. So we need to find all of them in Query Designer and they look like fields! Take them and add to “Filters” part.

Procedure

Go to transaction RSRTS_ODP_DIS, paste the name of TransientProvider you’re dealing with (less 2C prefix) and you will find mapping between parameter name and description.

Find the same field by technical name or description and drag to Filters area, map values for them by clicking “Restrict”.

 

Variables

Chances are you are reading this post because you also would like to use BEx variables. So yes, they can be created and yes, you can use standard ones if your CDS model is properly annotated with @Semantics annotations. I hope you know how to create your own variables. So let us see what is needed to be done to use the SAP-delivered ones.

What you need to do is activate BI Content (same old RSA1) for variables. Decide yourself if it hurts to have all 2000 of them activated…

 

And voila! They’re visible in Query Designer

 

From this point on, Query build is no different than creating in on e.g. CompositeProvider

 

Cheers,

Dmitry Kuznetsov

BI Consulting

Assigned Tags

      11 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Destiné Dinga
      Destiné Dinga

      Hi Dmitry,

       

      Thanks for this interesting article.

      Could you please just tell me the annotation you have used in your cube CDS for your parameter P_STARTDATE so that it is bound in your query to corresponding 0DATE BW infoobject?

      I know that you assign the CDS variable to a data element but not sure that you only used data element /BI0/OIDATE in your CDS (P_STARTDATE : /BI0/OIDATE).

      Thanks,

      Destiné.

      Author's profile photo Dmitry Kuznetsov
      Dmitry Kuznetsov
      Blog Post Author

      Destiné,

      I did not annotate it myself, it was already defined (since I use standard in my example), but I see P_StartDate is defined as data element vdm_v_start_date, which points to a domain DATUM. I think that is what makes it work. Check example CDS I_APInvoiceProcessingAnalysis for your reference

      Cheers,

      Dmitry Kuznetsov

      Author's profile photo Destiné Dinga
      Destiné Dinga

      Thanks Dmitry.

      So the link comes with the domain used  for the data element of the parameter then :).

      Regards,

      Destiné.

      Author's profile photo Frederic Cincet
      Frederic Cincet

      Hi Dmitry,

      Thanks for sharing.

      Just to have a complete picture, what kind of reporting tool are you using on top of your BEX query ?

      Regards,

      Frederic

       

      Author's profile photo Dmitry Kuznetsov
      Dmitry Kuznetsov
      Blog Post Author

      Hi Frederic,

      I am using it in:

      • Fiori KPI tiles / ALP Reports (via OData)
      • Multi-dimensional client in Fiori, which is based on DesignStudio Analytis Template run-time

      But you can find the list of supported consumers of BEx query, it’s quite a lengthy one.

      Cheers,

      Dmitry Kuznetsov

      Author's profile photo Vadim Zaripov
      Vadim Zaripov

      Dmitry, how exactly do you expose BEx queries as a tile with the preserved original query settings?

      When I try with the custom semantic object and XQUERY it just shows me the measures and all dimensions from rows are in "free dimensions" section.

      Author's profile photo Dmitry Kuznetsov
      Dmitry Kuznetsov
      Blog Post Author

      Vadim, sorry for late reply. This seems not to be the case for me, The system seems to preserve Row/Colum/Free parts pretty well

      Author's profile photo T M Prasad
      T M Prasad

      Hi Dmitry,

      Thanks for this article.

      I have created Bex Query as you mentioned in this article and the same is available in RSRT, but it is not available in the Query browser Fiori App. Could you please guide me on this?

       

      Regards,

      Prasad

      Author's profile photo Dmitry Kuznetsov
      Dmitry Kuznetsov
      Blog Post Author

      Hi Prasad,

       

      you’re right, it won’t show there. Query browser in Fuori will show only CDS-based queries (whether scripted or created using Fiori Custom Analytical Query app).

       

      Cheers,
      Dmitry

      Author's profile photo T M Prasad
      T M Prasad

      Thanks for confirming the same .. Please let me know if you have an alternate solution for this

      Author's profile photo Srinivasu Koppda
      Srinivasu Koppda

      Hi Dmitry,

      Thanks for the interesting article.

      Please let me know which will have better performance

      'BW Query on CDS cube OR Consumption CDS View' consumed in Analysis for Office

      Regards,

      Srinivas.