Sunday, March 13, 2016

BEx Query: Release of external access to query

BW queries can be consumed by 3rd party BI tools for long time. This feature needs to be enabled for particular query. To enable it there are settings that are to be done in BEx Query Designer. Furthermore there are even more settings that can be set in the query level with regards to external access to the query’s data. In this blog post I look at these settings.
Following flags are available in BEx Query Designer under Properties section of the query and on tab called “Extended”.


1. flag 'By OLE DB for OLAP' = Allows to consume the query in external reporting tools that uses the query as data source. Sometimes such a query is it is referred as ODBO provider or just ODBO (Ole DB for Olap. The flag is present physically in following database tables:
RSRREPDIR-RFCSUPPORT - Report supports access using OLAP API
RSZELTPROP-RFCSUPPORT


2. flag 'By Easy Query' = so called Easy Query (EQ) flag. It allows an access of the query for external access as an Easy Query. The EQ is static query with no formatting settings and it can be used as SOAP service. Once this flag is enabled for particular query then it needs to be managed in Easy Query Manager (tcode EQMANAGER) in BW’s backend system. The flag is present physically in following database table:
RSZELTPROP- EQSUPPORT - Query: RFC Publishing as VirtualProvider (yes/no)


3. flag 'By OData' = so called OData Query flag. It allows consuming of the query data by ODATA service. It is also an integration of SAP Business Warehouse with SAP Gateway system where it provides analytic queries of in BW system as BW OData Queries for mobile scenarios. The flag is present physically in following database table:
RSZELTPROP-ODATASUPPORT



Both the EQ and OData techniques are focused on so called lightweight consumption. This means that need SAP Gateway to be used. Easy queries can be used also via SOAP or RFC as REST based services.

The above two techniques are also part of BW’s Open Analysis Interfaces. This is meat with regards to extraction of the data from source systems and analyzing of the data in BW in various reporting front-end tools.


4. flag 'HANASUPPORT' – Means generation of external SAP HANA view for BW object. There are many BW’s objects for which the HANA view can be generated and the BEx query is one of these. The HANA view is not used by BW itself but by SAP HANA natively while accessing the SAP BW. The flag is present physically in following database table:
RSZELTPROP-HANASUPPORT

For assigning the HANA external view flag to other SAP BW objects see here.

Please note that flag no 1 is available for long time and was introduced in BEx Query Designer 7.x. However for flags 2 and 3 there is a prerequisite to run SAP NetWeaver 7.0 BW Front End for GUI 720.


Useful information:
1598778 - Query property 'Release for External Access by Easy Query'
1601473 - Query property 'Release for External Access by Easy Query'
2103222 - Generation and Transportation of BEx Query with active OData-Flag

7 comments:

Bart said...

Very good post !!!
I am not SAP BW user, but as PL/SQL developer I need to connect to BEx Query and I am wondering how to do it. Let me resume your post:
1. ODBO is for BI tools, like Excel, BO and so on
2. Easy Query is for SOAP
3. OData is for REST, but for all I read this is not used in PL/SQL

On the other hand I read about BEx Query APIs RESTful Web Service, but it is the same as OData or this is quite different case used in BO only ?

Best Regards,
Bart

Bart said...

Very good post !!!
I am not SAP BW user, but as PL/SQL developer I need to connect to BEx Query and I am wondering how to do it. Let me resume your post:
1. ODBO is for BI tools, like Excel, BO and so on
2. Easy Query is for SOAP
3. OData is for REST, but for all I read this is not used in PL/SQL

On the other hand I read about BEx Query APIs RESTful Web Service, but it is the same as OData or this is quite different case used in BO only ?

Best Regards,
Bart

Martin Maruskin said...

Hi Bart,

there can be several things related to a term "BI Query APIs RESTful Web Service".

First of all in case the BI is meant as BO (BusinessObjects) then there are possibilities how to expose the BO objects like Web Intelligence or BI Semantic Layer, see here:
REST API Quick Reference
Data Provider APIs this is basically coming out of the box from BO software.

In case by BI is meant an BW then there is a possibility to create custom RESTful service for BW (formerly BEx) queries like suggested e.g. here:
Expose BI Query as RESTful Service

Perhaps there is a possibility how to consume either BW rest service or in general OData approach in your PL/SQL.

cheers

Bart said...

Hi Martin,
in fact I don't understand. RESTful mechanism works in what case ? When Business Objects is running and it is one of his API ?
I found other SAP application - BEx Web Application Designer.
https://help.sap.com/saphelp_scm41/helpdata/en/d8/3bfc3f8fc2c542e10000000a1550b0/content.htm?no_cache=true
but again the same question, this app needs to run some web application to able to use its API ?

Martin Maruskin said...

Hi Bart,

perhaps it would be easy to start with what technology you have. Do you need to expose BW (sometimes called BEx) query or do you have BusinessObjects tool (Web Intelligence, Crystal Reports, etc)?

BEx Web Application Designer - runs on top of SAP BW. It is tool to create web apps. I do not think it is your case that you would need it.
cheers

Bart said...

Thank you Martin for all you replies.
In fact there are used all apps you mentioned.
For me it is case that from PL/SQL I can reach data only by Web Services, so SOAP or REST.
I thought about REST as it is easiest, but in this case BEx offers only ODATA REST, so I can't use it, only SOAP, which is very complex, because of XML format.
And here are my doubts - if every SAP application has its own API, so which one should be the easiest and possible ?
I resume my understanding of possible APIs in our case:
1. BEx Query Designer API:
1a. ODBO for BI tools, like Excel, BO, etc...
1b. Easy Query for SOAP
1c. OData for REST
2. BEx Web Application Designer to produce Web App (link) and get access to data using SOAP
3. BO with RESTful

Do i understand it correctly ?

Martin Maruskin said...

Hi Bart,

if I understand it correctly you need to consume BW (or BEx) query in PS/SQL environment.
The easiest way I think would be to use ODATA by enabling its check box at BEx Query level. But seems you for some reason you do not want to you it.
What about SAP BW Easy Query? Also you can Generate web service (on top of Function Module) in SAP BW system which will trigger the BEx query execution.
But this would require an custom programming in ABAP.

I think more less your understanding is right.

cheers