Transform SAP BW Queries into OData Service: Part 4 - Query Parameters

[Former Member]

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.

1. 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.
2. The Result Entity Set Type is marked as sap:addressable=false.
3. 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 :wink: ). 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

1. We are navigating from Parameter Entity Type to Result Entity Type
2. 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.
3. 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.