1. Introdution 2. Preconditions in SAP BW system 3 Creation of a BEX Query and publication as a ODATA Service 3.1 Creating the BEX Query 3.2 Publishing the service 3.3 Making the URL 4. Conclusion |
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)
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 an ODATA service 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 with ODATA protocol (or that can interpret XML), thus guaranteeing the coherence of data between both systems.
In addition, since BW 7.5, ODATA can be an ODP provider
I hope this post was of your interest.
- SAP Managed Tags:
6 Comments
