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
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
Thanks Dmitry.
So the link comes with the domain used for the data element of the parameter then :).
Regards,
Destiné.
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
Hi Frederic,
I am using it in:
But you can find the list of supported consumers of BEx query, it’s quite a lengthy one.
Cheers,
Dmitry Kuznetsov
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.
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
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
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
Thanks for confirming the same .. Please let me know if you have an alternate solution for this
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.