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.
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.
If you are planning on using the query in KPIs or Reports, it is a good idea to check the box for OData service.
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.
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”.
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