How to do Odata Services from BEx Query

1. Introdution

The following document discusses the method for creating ODATA services from BEX queries in the SAP BW environment.

ODATA services allow a BEx query to be consumed by any system external to SAP (mainly web services) using this standard language.

2. Preconditions in SAP BW system

Step 1: Configurating SAP Gateway

If this is the first time that ODATA queries have been performed in our environment, the SAP Gateway may have to be configured. The actions listed below only need to be done once.

In our case, the first three steps of note 2113675 have been implemented

SPRO transaction:

(note: some of screenshots had been made in spanish language)

Creation of LOCAL Gateway,

with values:

SAP System Alias	LOCAL
Local GW	Selected
RFC Destination	NONE
Software Version	DEFAULT

Step 2:  Activation of ICF services

SICF_INST transaction:

Step 3: EQ_RS_AUTOSETUP

After configuring the previous points, it happened that the ODATA queries were not published as a service. After revision of SAP notes, this issue was solved executing the program EQ_RS_AUTOSETUP.

3. Creation of a BEX Query and publication as a ODATA Service

3.1  Creating the BEX Query

The first step is to create the BEX query that will provide us with the information we want to supply to the external system. The query will be created in the usual way in the query designer or in Eclipse.

Keep in mind that queries for ODATA services have some restrictions. For example:

◈ Free attributes are not accepted

◈ There must be at least one attribute in the rows

Filters and variables

As in any other query, we will try to filter the information as much as possible, in order to obtain a better performance.

The variables (as indicated below), do not interact with the ODATA external service (that is, a screen does not appear as in Analysis for Office or Analyzer, for example). However, it is convenient to use variables, because:

◈ The available variables will be indicated in the metadata of the service (see below), thus facilitating the interpretation of the service.

◈ We can compare the performance of the service vs. the BEx query

Saving the query

We active the flag as ODATA query:

3.2  Publishing the service

The steps to publish the service are the following:

Execute the transaction /n/IWFND/MAINT_SERVICE and click on “Add Service”

In “Alias System” we indicate the Gatway created above and “Get services”

After clicking in “get services”, in the list should appear our query with the suffix _SRV:

Important note: If it does not appear, execute function module RSEQ_NAT_GENERATION using SE37Click on the service and assign it to the corresponding package or as a local object:

If everything has been correct, we will see our query in the list of services. From this moment, the odata service is ready to be used

3.3  Making the URL

The following examples are about the query ZUS_ODATA_TEST_Q001.

The following URLs will be valid for any other query, simply replacing the name of the query and the “url_internal_server” with the name that corresponds to your system.

The URLs will be accessible from any web browser or tool that has the ODATA protocol (such as excel 2013 as indicated below)

Querying metadata:

http://url_internal_server:8000/sap/opu/odata/sap/ZUS_ODATA_TEST_Q001_SRV/$metadata

This URL will provide diverse information that will be useful for the external system developer. In the metadata (in XML format) there are diverse information such as the name of the fields, content …

Query results:

http://url_internal_server:8000/sap/opu/odata/sap/ZUS_ODATA_TEST_Q001_SRV/ZUS_ODATA_TEST_Q001Results

This is the ULR that will return the results of the query. This URL must be used from an external system with ODATA protocol

Variables (passing filters):

Using the standard ODATA language, any field could be used to pass a filter from the external system to the BW system.

In the following example, I use the filters that are in the query using the corresponding variables. In our example (extrapolated to any query). In the metadata we can see:

The ZZSITECOD_SVO is the variable that I have put in the query for the field “site” (in the example it is a query of the Real Estate module). To filter for example by the site ‘5’, we will add to the URL of results that we had previously:

http://url_internal_server:8000/sap/opu/odata/sap/ZUS_ODATA_TEST_Q001_SRV/ZUS_REFX_ZCP_REFX06_Q001(ZZSITECOD_SVO=’5′)/Results

If the external system wants to have the possible values to show them to the user, the range of values can be obtained through the URL:

http://url_internal_server:8000/sap/opu/odata/sap/ZUS_ODATA_TEST_Q001_SRV/ZUS_ODATA_TEST?$select=ZZSITECOD_SVO

To visualize this example, I used the Excel ODATA (office 2013):

And I get as a result in excel:

In case of detecting performance problems, we must limit if the problem is in BW or in the service. For this we will use BW statistics in the RSRT:

Next (as an example), we can see a comparison between before and after adding the filter for the site (in this case in a query of BW Real Estate module):

(without filter)

(with filter)