Skip to Content
Author's profile photo Miguel Peredo Zurcher

BEx queries and OData in BW 7.4

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)

Assigned Tags

      20 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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


      Author's profile photo Vanessa Martinez
      Vanessa Martinez

      Hi Miguel Peredo Zurcher thanks for this great tutorial.

      Author's profile photo Jordi Vila
      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?

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Eduard Sanz
      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

      Author's profile photo BO bo
      BO bo

      Hello Cesar,

      Did you resolved this issue?

      Author's profile photo Rachid Kaouass
      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

      Author's profile photo Miguel Peredo Zurcher
      Miguel Peredo Zurcher
      Blog Post Author

      Hello Rachid,

      Not sure if I understood you correctly. The drilldown happens at the frontend, if you manage to a write a frontend that detects when the users does a drilldown, and then generates the new query, that's it.

      If you are talking about hierarchies, those are not supported.

      Regards,

      Miguel

      PS.: Check also this: Connect to an OData feed (Power Query) - Excel

      Author's profile photo Rachid Kaouass
      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Miguel Peredo Zurcher
      Miguel Peredo Zurcher
      Blog Post Author

      Hello Deep,

      I think the best way is to open an OSS message.

      Br,

      Miguel

      Author's profile photo Miguel Peredo Zurcher
      Miguel Peredo Zurcher
      Blog 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.

      Author's profile photo Former Member
      Former Member

      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

       

      Author's profile photo Arne Witte
      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

      Author's profile photo Former Member
      Former Member

      Very cool! Thanks for the effort.

      Author's profile photo Former Member
      Former Member

      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 !!!

      Author's profile photo Anders Kortbæk
      Anders Kortbæk

      Hi Miguel,

      Any experience with implementing Odata Version 4 for a BW query?

      The automatic generation is only Version 2.

      Thanks, Anders

      Author's profile photo Wolfgang Lindner
      Wolfgang Lindner

       

      Hi all,

      based on the following documentation https://help.sap.com/saphelp_nw74/helpdata/en/f3/0c7316de924a98bb173904f7a61b4a/frameset.htm?frameset=/en/c9/384c774bcc4837b84bee3679520fb4/frameset.htm&current_toc=/en/22/c927d9cd6f48d18cf4793ccce1cb71/plain.htm&node_id=110

      the oData enabled query does not supported the central hub architecture.

      Is that correct?

      My goal is to consume the query with a Fiori frontend by using a separate gateway system as recommended by SAP.

      Do I have to use the easy query instead?

      Regards

      Wolfgang

      Author's profile photo Sangharsh Aggarwal
      Sangharsh Aggarwal

      Thanks Miguel for the wonderful blog. On the t-code /n/IWFND/MAINT_SERVICE, after hitting the "Add Service" button, the next screen does not bring me any Backend Service. I have tried all the options you mentioned here. But none worked. Also, for me System Alias field populates whatever I entered last and can't blank it out. Plus there is nothing that works on that field. Everything pops error: System alias 'ABC' does not exist.