BEx queries and OData in BW 7.4

[miguel_peredozurcher]

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:

 



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:

 



 

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"

 



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:

 



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:

 



Then press the "Use this Setup" button

In the next screen:



 

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



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:



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=A0... 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:

 



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)