ODATA services from Queries BEX

1. Introduction

The following document discusses the method to create 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 that has this service.

2. Previous configurations in the system

2.1 Configuration of the SAP Gateway

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

For the configuration of the same, the first three steps of note 2113675 have been implemented

SPRO transaction:

We created the LOCAL Gateway

With the values:

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

2.2 Activation of ICF services

Transaction SICF_INST.

2.3 EQ_RS_AUTOSETUP

After configuring the previous points, the ODATA queries were not published as a service. After revision of SAP notes, I executed the program EQ_RS_AUTOSETUP.

After this execution, the publication of queries is carried out correctly (see below)

3. Creation of BEX query and publication as ODATA service

3.1 Creation of 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, although the ODATA services have some restrictions. So far the restrictions that I have been able to verify are the following:

◈ Free features are not accepted

◈ There must be at least one characteristic 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 run directly in the ODATA external service (that is, a screen does not appear as in Analysis for Office or Analyzer, for example). However, we will place all the variables by which the external service will ask dynamically, since:

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

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

Record the query

We mark the query as ODATA

3.2 Service publication

The steps to follow 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 pressing "get services" in the list should appear our query with the suffix _SRV at the end:

Important note: If it does not appear, run the function module RSEQ_NAT_GENERATION via SE37

We click 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, being already part of this consultable moment:

3.3 Interpretation of the service - preparation of the URL

The following examples are about the query ZUS_ODATA_TEST_Q001.

The indicated URLs will be valid for any other query, simply replacing the name of the query and the tlcmbpccid01.cnx.ad.internal with the name that corresponds to your system.

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

Query metadata:

http: //tlcmbpccid01.cnx.ad.internal: 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 A bw. In the metadata (in XML format) there are diverse information such as the name of the fields, content ...

Query results

http: //tlcmbpccid01.cnx.ad.internal: 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

Passage of variables

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

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 observe:

The ZZSITECOD_SVO is the variable that I have put in the query for the 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: //tlcmbpccid01.cnx.ad.internal: 8000 / sap / opu / odata / sap / ZUS_ODATA_TEST_Q001_SRV / ZUS_REFX_ZCP_REFX06_Q001 (ZZSITECOD_SVO = '5') / Results

As it is possible that 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: //tlcmbpccid01.cnx.ad.internal: 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:

Below we can see a comparison for example between before and after placing the filter per site (in this case the query was on a BW model of Real Estate)

(result showing all of the detail of the query -without filter-)

4. Conclusion

Except for the initial configuration tasks in the SAP Gateway (which must be done only once), we can publish any BEx query as ODATA service practically automatically and without any additional development.

In addition, the same query could be consumed for example from a reporting in Analysis for Office (AFO) and at the same time from another system that has ODATA protocol (or can interpret XML), thus ensuring data consistency between both systems .