Skip to Content

This is a post about how easy it is to release a BEx query for OData consumption.

 

Let’s say that your Ecommerce solution needs to get the quantity available in stock for a specific material at a specific plant. This can be achieved by making the Ecommerce solution call to an OData enabled BEx query in your BW.

 

Most “modern” applications can speak OData, for example SAP Fiori, SAP NW, and Microsoft Azure. More info and some nice diagrams about OData and BEx queries can be found here.

 

These are the steps to follow to make a BEx query available for OData consumption:

 

  1. Setup the integrated SAP Gateway in your Netweaver 7.4
  2. Mark the query as Odata enabled, and publish it
  3. Find a test client to check the structure and values

 

To demonstrate step 3 I’lll use a ready made solution on the BW side, and a Python program as my Ecommerce solution.

 

Security note: in this example I’m using plain text authentication, meaning that the credentials will travel through the network without encryption. This is not suitable for production use.

 

Let’s start:

Setup the integrated SAP Gateway in your Netweaver 7.4

For this, I could not find a specific note related to OData and BEx queries, but I found this note: “2113675 – RSPCM_WEB transaction error with 403 Forbidden” which talks about another nice feature about 7.4 which is the new APP for Process chain monitoring in UI5. Since this APP is based on UI5 which uses OData for communicating to the back-end, we can use this note also for our purpose.

 

Follow only the three steps in the fist part of the note: “I.Configuration of Gateway”.

 

Mark the query as Odata enabled and publish it

 

Note that the following is a simple query that just returns the stock quantity for material and plant, it does not have any input ready variables. The date is handled via a non input ready variable.

 

While testing you can put some static filters in your query, like 5 materials, and 2 plants, to avoid getting the list of all materials and all plants. In the final version the filter for material and plant will come from the OData query string.

 

The query looks like this:

 

/wp-content/uploads/2016/02/query_883084.png

Note that “By Odata” flag is turned on.

 

When saving the query check that there are no error messages, usually those are authorization related. When you save a query with the OData flag on, the system auto-generates some things related to OData.

 

In this case the query name is: Z_ODATA_INVENTORY

 

Now we need to release the service for OData, we can follow the instructions from here.

 

Go to transaction: /IWFND/MAINT_SERVICE and press add service:

 

add_Service.png

 

Then from the list (if the list is empty you can press “Get services” button) select the line that contains the query with a _SRV sufix and press “Add selected services”

 

add_Service2.png

Accept the default values, and press OK.

Note: If by any reason you cannot see the new service, you can force the generation of the service
for the query using function module: RSEQ_NAT_GENERATION in SE37. Just fill the the technical name of the query in the input structure.

Now you can go to this URL:http://host:port/sap/opu/odata/sap/Z_ODATA_INVENTORY_SRV/$metadata

 

And you should be able to see the XML definition of the query/service:

 

Service Definition.png

The relevant information here are the two characteristics from Rows section that can be found by key and text, with the “_T” suffix. The key figures from the Columns section are A00O2TM5ZRDCVNYBZ57LAF99TM and A00O2TM5ZRDCVNYBZ57LAF99TM_F (the “_F” suffix means formatted, in this case with Unit).

 

Now the query is ready to be consumed by any OData system.

Find a test client to check the structure and values

 

I’ve been playing around with OData in Azure before, and the best client I could find (this was in November 2015) is: XOData®: OData Visualizer and Explorer – PragmatiQa available here. Note that I’m using the paid version.
At the end I will also call this OData service from Python.

 

Let´s open XOData and choose this options:

 

/wp-content/uploads/2016/02/xodata1_883310.png

Then press the “Use this Setup” button

In the next screen:

/wp-content/uploads/2016/02/xodata2_883312.png

 

Choose OData Metadata URL, then enter the URL of our query, and press Get. Then you can switch to the next tab.

XODATA3.png

Here you can see again the fields that the service is returning. Now let’s go the next tab “Query builder”. Play a little and do a query like this:

XODATA4.png

In (A) you have the OData query that can be used to get the inventory quantity for a specific material and plant. You just need to replace the values in quotes with your material and plant key.

 

At the bottom is the result:

 

B -> Material key

C -> Material text

D -> Plant key

E -> Plant text

F -> Key figure without format or unit

G -> Key figure with format and unit.

 

And that’s it, any application that speaks OData can request the inventory quantity for any material and plant.

 

Now let´s try to do the same from Python:

 

I used PyCharm Community Edition and Python 3.5. I also added these two modules with from command prompt:

 

>pip install requests

>pip install feedparser

 

 

import requests

import feedparser

 

# These are the fields from the XML file in the URL of the query.

fields = {‘d_a0material_t’: , ‘d_a0material’: , ‘d_a0plant’: , ‘d_a0plant_t’: , ‘d_a00o2tm5zrdcvnybz57laf99tm_f’: }

 

# Replace the values for your host, port, user and password, plant and material

r = requests.get(http://host:port/sap/opu/odata/sap/Z_ODATA_INVENTORY_SRV/Z_ODATA_INVENTORYResults?$top=20&$filter=A0PLANT eq \’YOURPLANT\’ and A0MATERIAL eq \’YOURMATERIAL\’, auth=(‘youruser’, ‘yourpass’))


if r.status_code == 200:

d = feedparser.parse(r.content)

data = d[‘entries’][0]

for item in fields:

fields[item] = data[item]

for item in fields:

print (fields[item])

else:

print(r.status_code)

 

 

This is the output:

 

python output.png

In line 1 we get the material description, on the next line we see “1.0 PC” that is the formatted quantity, on line 2 we get the plant description, on line 3 we get the material key, and on line 4 is the plant key.

 

This is it!

 

Hope you enjoyed this example. If you have managed to use OAUTH for this queries, please share.

 

Thanks!

 

Tested on BW 7.4 SP11

Other great blogs about OData: OData – Everything that you need to know (Part 1)

To report this post you need to login first.

16 Comments

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

  1. Cesar Begazo

    Hello Miguel

    It’s a really interesting blog. I have an issue in its implementation:

    I have marked OData flag in my query, but I can’t see it in /IWFND/MAINT_SERVICE tx, in order to select the service.

    When I press “Get services” button appear many services except mine.

    I have checked Gateway Configuration and it looks fine. I am working in embedded deployment and my system is Netweaver 7.4 SP8.


    Please do you have any idea about the cause?

    Thanks in advance.

    Best regards


    Cesar Begazo

    (0) 
    1. Miguel Peredo Zurcher

      Hello Cesar,

      No idea, sorry. But just in case check note:

      2103222 – Generation and Transportation of BExQuery with active OData-Flag

      Also make sure that the BEX query designer in your PC is up to date, and pay attention to messages when saving:


      “When saving the query check that there are no error messages, usually those are authorization related. When you save a query with the OData flag on, the system auto-generates some things related to OData.”


      I my case I was lacking some authorization objects, and thus it was not generating the objects when saving, but was showing some warning in the Bex query designer.


      Best regards,

      Miguel


      (0) 
    2. Jordi Vila

      Hello Cesar,

      I have the same problem. After create the query with the odata flag. I don’t see the service.

      Have you been able to solve it?

      (0) 
      1. Cesar Begazo

        Hello Jordi

        I have solved the problem. In my case, my query was not in a transport request, because that OData objects couldn’t be created.

        I used transport connection to assign query to a transport request. It resolve the problem.

        Best regards

        (0) 
        1. Eduard Sanz

          i’have the same problem. My query is in a transport request and I’ve executed  RSEQ_NAT_GENERATION. The service is not showed in   /IWFND/MAINT_SERVICE

          (0) 
  2. Rachid Kaouass

    Hi,

    nice blog, I’ve succeeded to generate my oData service and consume it.

    We wanted to get the data from a drilldown as we can do using the BEx Query, however, I don’t see how to manage the “drilldown” using the oData Service.

    I think it is not possible but I prefer to ask to be sure 🙂

    Regards,

    Rachid

    (0) 
      1. Rachid Kaouass

        Hi,

        I don’t know if it is Hierarchies, but let me explain differently:

        When you execute a Bex Query, you can right-click on the report, on a certain value, and then you can drill down. So in that case a new column will be displayed and still using the same Query.

        Unfortunately, using the generated oData, they are not ways to request this detail with the same Bex Query.

        I had to ask to generate a Bex Query that display every column, and then I did in the UI5 application the aggregation and the drill down.

        I don’t know if it is the hierarchies, that you mention.

        Best regards,

        Rachid

        (0) 
  3. Deep PIYUSH DESAI

    Hi Miguel,

    I am trying to do the exactly same thing .

    Created a BEx query ; enabled the By Odata checkbox ; publsihed using the Tcodea and tests fine in the NW gateway.

    In the browser, I was able to generate the metadata but when I try to do /QuerynameResults,

    it throws me

    ERROR: Access using a ‘ZERO’ object reference is not possible. (termination: RABAX_STATE)

    Searching through all available online sources, I understand that my Odata service is absolutely correct and could generate the Metadata perfectly fine.

    Can you help me in understanding what the issue can be?

    Regards,

    Deep

    (0) 
  4. Miguel Peredo Zurcher Post author

    Hello,
    In other system I ran in the the same issue that the service was not being generated. I did a trace and I found that you can generate the service for the query using function module: RSEQ_NAT_GENERATION in SE37. Just fill the the technical name of the query in the input structure.

    (0) 
  5. Karthik Sridhar

    Hi Miguel,

    Thank you for the Blog!

    I am new to BW/BI, i have the same requirement as your example is, But the problem is that “By

    oData” option is missing in my Query Designer to expose it as oData service,

    My Query Designer Version is:

    How to enable “By oData” option? Is this because of lower level Support Package?

    Please help me with this,

    Regards,

    Karthik Sridhar

     

    (0) 
    1. Arne Witte

      Hi,

      you need to update your BI Addon front end tools. Query Designer SP0 is too low. Just take the actual SP, then the option will be displayed. I have installed SP7 and the option is available.

      Regards,

      Arne

      (0) 
  6. elena kolb

    HI Guyz,

    I am facing the same problem, I have collected the query in transport request, and i have tried generating the service via the  functional module, i am still not able to generate a service.

    Any pointers would be helpful !!!

    (0) 

Leave a Reply