Skip to Content
Personal Insights

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

5 Comments
You must be Logged on to comment or reply to a post.
  • 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é.

    • 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

    • 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