Different front-end tools can access data by deploying different methods. BEx Queries gives us three different ways to expose our data to the reporting tools. In the Remote Access screen area of the General tab, you can view and configure the settings for remote access. This blog covers the Remote access property in BEx query for SAP BW/4HANA.
With the transition of the BEx query in B4HANA, the remote access allows the following three options. Since external reporting tools use queries as their data source, this allows you to determine whether and how the query is accessed.
1. By OLE DB for OLAP
Enables the query to be accessed by MDX enabled front ends, such as SAP BusinessObjects tools (Web Intelligence, Analysis for MS Office, Lumira Designer, etc).
2. By OData
Enables the query to be accessed by applications based on Representational State Transfer (REST) such as Qlik and many other mobile applications.
3. External SAP HANA View
Enables the query to be accessed by SQL-based ODBC third-party tools such as Tableau. If the query is based on an Info Provider that supports SAP HANA views, you can use this setting to make the system create an SAP HANA view for the current query.
Let us discuss in detail about how each of these options works when enabled:
OLE DB for OLAP
OLEDB for OLAP is an interface specification that provides access to relational and multidimensional data without any database dependency. These interfaces provide applications with unified access to data from different sources.
When this option is enabled, the communication between the frontend and the SAP BW/4HANA are handled by a component called MDX processor.
What is MDX?
MDX (Multidimensional expression) is a Query language used for Multidimensional Interface. The main characteristic of MDX is the ability to send data (including complex objects such as hierarchies) to different front-end tools in a generic format.
The MDX processor receives a Query request from front-end tools using different protocols such as:
- Component Object Model (COM)
Using these it forwards the request to the OLAP processor for processing. Therefore, the MDX processor acts as an agent between the OLAP processor and the front-end tool.
With an increase in demand for reporting and dashboards, it becomes vital to be available for mobile devices. The integration of SAP BW/4HANA along with SAP Gateway services allows you to provide analytic queries of SAP BW/4HANA as OData Queries which are available for them.
An interface called ‘Lightweight consumption’ is used to send the SAP BW/4HANA Query requests directly to the OLAP processor by using the OData communication method.
When you define a Query with the ‘By OData’ property selected, a function module is generated in the back-end system. The name of the generated function module can be seen via transaction EqManager. You need to release the Query for access via the OData protocol by using the maintenance service Transaction /IWFND/MAINT_SERVICE in the SAP Gateway.
You can have the base URL exposed by the service displayed when you start a browser.
The structure of the URL is as follows:
You can pass parameters to your Query URL to redefine the end result of your Query.
Few important parameters are:
- $format = <format name> –Displays result in the format which you specify.
Example: To retrieve the data in xml format:
http://sapserver.com:8000/sap/pos/odata/sap/ZSALES_Q001_SRV/ ZSALES_Q001Results/$format = xml
- $top = 3
–Displays top 3 results.
You must have also noticed that the ‘Easy Query’ option which is available in the earlier versions but not in SAP BW/4HANA. It is just that it has been replaced by the OData Query in SAP BW/4HANA.
OData Query is consequently optimized for OData consumption (for example, by SAP Fiori clients) and therefore no longer offers RFC and SOAP access. Since the Easy Query was RFC and a not HTTP based protocol, it was substituted with the OData Query. The OData Query is a more open standard using the OData protocol based on REST services.
Accessing BW Queries via OData is more flexible than Easy Query:
- Ability to handle free characteristic and dynamically change the Query result set
- Hierarchy handling
- Optimized performance
- Optimized lifecycle – it is a transportable object (EQ was regenerated in each system transported to)
- Support for CDS based queries
External SAP HANA View
With this option, an SAP HANA calculation view will be created with the same name as the Query. You will have an SAP HANA view hyperlink generated. You can get the location of the view by hovering the cursor over the link or by opening it by clicking the hyperlink. The SAP HANA view will then be displayed in the SAP HANA studio modelling perspective.
The SAP HANA data source allows you to connect directly to the SAP HANA Views created. When you use this data source, you can choose either the relational or OLAP connection type. Once you select the connection type, the connection assistant shows the available objects that can be used as a source for your Query.
Know more about Visual BI Solutions SAP BW Services offerings here.