Transform SAP BW Queries into OData Service: Part 4 – Query Parameters
With the release of SAP NetWeaver Gateway support package SP06 there is one new feature supported in OData Analytics Service – Query Parameters. In Transform SAP BW Queries into OData Service: Part 3 – Execute OData Analytics Service we saw how to execute OData Analytics Services. In this article I will explain how to interpret and execute an OData Analytics Service with Query Parameters.
Most of the times your SAP BW Queries are designed to take in some input parameters before the actual query is executed. For Example If I want to calculate Actual Costs incurred at each Cost Center level for a certain Controlling Area, I can pass Controlling Area as one of the input parameters for the execution of the query. The query result is now restricted to only those list of Cost Centers that belong to the given Controlling Area.
Well let us assume that we have designed a BW query that has Controlling Area as a mandatory parameter. Now generate and activate an OData Analytic Service as mentioned in Transform SAP BW Queries into OData Service: Part 1 – Generation and Activation.
First let us interpret the metadata of the generated OData Analytics Service and then see how to execute such services.
Metadata
You could see in the metadata document three major changes.
- A new Entity Type annotated with semantics sap:semantics=”parameters” is added to the service. This entity type describes the list of parameters that are required for executing the Query operation on Result Entity Type (annotated as sap:semantics=“aggregate”). I will refer to it as Parameter Entity Type.
- The Result Entity Set Type is marked as sap:addressable=false.
- There is a navigation property named “Results” created for parameter Entity Type. Well this ensures that you could navigate from parameter Entity Type to Results Entity Type.
These major changes tells that we cannot execute/address the Result Entity Type directly (Try accessing them đŸ˜‰ ). It can only be addressed via the Parameter Entity type. Refer to the sample attached metadata document which shows the three main changes to the metadata document.
Now that we know how to interpret the metadata lets see how to execute such OData Analytics services. I will be taking examples based on the sample service that you see in above image.
For the sake of simplicity I have listed down the key elements in the metadata document that shall be used in below examples.
| Element | Type | Annotation |
|---|---|---|
| ZGK_CCA_D21_Q0001Parameters | Entity Type (Parameter) | sap:semantics=”parameters” |
| ZGK_CCA_D21_Q0001 | Entity Set | — |
| ZGK_CCA_D21_Q0001Result | Entity Type (Result) | sap:semantics=”aggregate” |
| Results | Navigation Property |
Runtime
Case 1 – How to Fetch Non Aggregated Measure Values for a given Input Parameter
Let us take a simple case where I would like to fetch non aggregated values for a given Input Parameter.
OData Request
ZGK_CCA_D21_Q0001(ZP_ZCO_AREA=’US01′,ZI_ZCOSTCENT=”,ZI_ZCOSTCENTTo=”)/Results
In the above OData Request note down three main points
- We are navigating from Parameter Entity Type to Result Entity Type
- The Parameter Entity Type takes value for the its Key Properties. The Key properties of Parameter Entity Type is taken as Input Parameter for MDX queries. Refer the attached screen shot. In the screenshot you could see the properties for Parameter Entity Type. They are nothing but Key properties.
- The Parameter Entity Type should take value for its Key Properties that annotated as sap:parameter=”mandatory“. If the Key property is annotates as sap:parameter=“optional”, then it can take empty values. In the above example ZI_COSTCENT is an optional parameter and hence it can be passed with empty values.
Case 2 – How to know the values for the Input parameter
Before executing Case 1, you need to know what valid set of values Input Parameters take. To get the list of valid values for the Input parameter execute the following OData request
OData Request
ZGK_CCA_D21_Q0001?$select=ZP_ZCO_AREA
The above request gives the list of valid values for the parameter Controlling Area. Now you could use the values for constructing the OData request mentioned in Case 1.
Note – You cannot select more than one paramter in one call. Which mean I cannot execute the query ZGK_CCA_D21_Q0001?$select=ZP_ZCO_AREA,ZI_ZCOSTCENT
Now that you know how to access the Result Entity Type via Parameter Entity Type, you could carry out all set of operations that was explained in Transform SAP BW Queries into OData Service: Part 3 – Execute OData Analytics Service. It holds good, except that you need to always navigate via the Parameter Entity Type.
Excellent and well explained. thanks.
Hi Chandan..Excellent document.
I've one question...If we have multiple MDX queries and want to create one odata service consuming all of them same like collections...is it possible to do that?
Thanks
Hi Abhishek,
Thanks,
The use case which you have mentioned was not tried out. You could check with SEGW.
Regards
Chandan
Hi Chandan,
I am trying to create a basic odata service using BEx queries.
In my case SAP BW and Gateway are two different systems.
Question is The blog doesn't say about generating the service implementation classes and methods which i found in some other blogs.
Do we need to do this or it's optional?
Regards,
Abhishek
Hi Abhishek,
The tool SEGW in the background generates the service implementation classes.
You can visualize these classes by expanding the node - Runtime Artifacts for your generated Analytics service in SEGW.
Regards
Chandan
Hi Chandan,
I have little question for you, i created an odata service based on Bex easy query , but my problem im trying to put a filter on a an entity element, but i have the attribute sap:filterable="false" i want to change that so i can filter my data
Best regards
Reda
Hi Reda,
Below given link may help you in setting the Filter on an entity element.
http://help.sap.com/SAPhelp_nw73/helpdata/en/42/fdce952ca36faee10000000a11466f/content.htm?frameset=/en/47/99bcc8651717f9e10000000a42189c/frameset.htm
Regards,
Suma
Hi Reda,
Did you find solution to change Filterable property to TRUE from SEGW change/display?
I tried but not able to do it..
did you find a solution for this issue sir?
Hi Chandan...Helpful document.
But I have one more question. Assumption I've changed the parameter's name in the SEGW, then what are the related methods should be changed? Since there's no CRUD methods in this use case, I have no idea what to do next.
Thanks Chandan for the informative article series. I have a question. in the above example, the variable is being passed a range. How does one pass multiple single values?
hi i have tha same problem how did you resolve it?
I have the same problem... how resolve multiple values?
did you solve it Tushar
Nice Blog - thanks !