Skip to Content
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=”parametersis 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 đŸ˜‰ ). 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.
Query Parameter.png
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.
To report this post you need to login first.

15 Comments

You must be Logged on to comment or reply to a post.

  1. Abhishek Wajge

    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

    (0) 
    1. Chandan V.A Post author

      Hi Abhishek,

      Thanks,

      The use case which you have mentioned was not tried out. You could check with SEGW.

      Regards

      Chandan

      (0) 
      1. Abhishek Wajge

        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

        (0) 
        1. Chandan V.A Post author

          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

          (0) 
  2. reda miraoui

    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

    (0) 
  3. John Lu

    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.

    (0) 
  4. Tushar Joshi

    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?

    (1) 

Leave a Reply