Consuming SAP BW queries externally

Most of the time you will use one of SAP’s standard frontends to execute BW queries: Analysis for Office, Lumira Designer, the legacy BEx suite – or maybe connect the query via live-connect to SAP Analytics Cloud. However, there are times when you need to integrate the data delivered by a query in a custom developed frontend, or just fetch it for use in further calculations in an external system. What are the options?

The SAP documentation will give you an overview of interfaces here. Those interfaces are grouped into two categories, the first ones are the technologies to connect BW to external data sources, APIs to push data to BW or to change master data and hierarchies, custom logic in Virtual Providers, and pushing data from BW towards external systems with OpenHub and other Data Mart interfaces or the Data Federator. This is all the “backend stuff”, typicall on the level of infoproviders, but not on the level of queries.

The second category is the one we are looking for: interfaces that allow access to data that is the logical result of queries (or the default query of providers.) These are the ones we would like to summarize today.

The MDX based interfaces

Historically most of the interfaces to consume query data were built upon the MDX ‘protocol’. To be more precise MDX is more a query language to query multidimensional datasets. And the result of a query is a multidimensional dataset as well, so it might seems straightforward to use MDX. Unfortunately, BW queries are then again quite special, with features that MDX does not recognize as a standard. Like variables for examples. Retrieiving data via MDX to me always seemd quite cumbersome, and definitely not trivial.

Based on the MDX ‘paradigm’ and the query language you can access query data via OLE DB for OLAP which opens up the world towards Microsoft clients. As an example, you could create an Excel workbook that would fetch your query data via this interface. Without using Analysis for Office. But what would be the point in that, honestly, if Analysis does that ‘natively’?

If you are not connecting from an interactive frontend, you could consider XML for Analysis which is a web service based access to the query result in an XML format. Alternatively, the OLAP BAPIs would allow retrieval of the data via RFC. While calling an RFC to get the data sounds pretty easy, remember that you would not just pass the query ID in and get a nice result table. You would need to formulate an MDX query against the query and get back an MDX resultset. In the SAP documentation the other access options (see below) are grouped into a section ‘Lightweight Consumption’ – and as you can guess, this pretty much suggests that OLE DB for OLAP and the OLAP BAPIs represent rather the ‘heavyweight category’.

External HANA View

External HANA views are – obviously – only an option if your BW is on HANA or “4 HANA”. The idea here is that a database view is generated for you which encapsulates all the logic – the restrictions, calculation formulas etc. – that are defined in the query. It returns the data as the query would return it, if an SQL select is fired against it. Variables are also supported and can be passed as ‘placeholders’ when writing the SQL.

Using this feature is as easy as checking a few checkboxes. First, make sure that your composite provider the query is built upon has the ‘External SAP HANA View’ checkbox marked, then check the same checkbox also in the query.

After saving the query you should find a view – usually called system-local.bw.bw2hana.query.[inforprovider]/[query] – in your _SYS_BIC database schema. The view contains all characteristics and structure members of the query as fields, and even has columns for navigation attributes and texts. Just run your first select against it, and you will see the structure anyway.

But beware! This all sounds too good to be true, istn’t it? Indeed, there are numerous limitations on the query features that are supported by this technology. Some features – like things related to hierarchies and variables – are not supported at all and will prevent the external view from being generated. Other features will not prevent generation, but are ignored at runtime, so the external HANA view of the query will not deliver the exact same result as if the query were run from one of the standard frontends. Check the documentation and OSS notes for an up to date list of these features.

Query as InfoProvider

Query as InfoProvider is actually not directly meant as a real-time external interface. It is more a snapshot mechanism within BW – but it might nevertheless be worth a try.

What happens if you configure a query as infoprovider in transaction RSDDB? The result of the query is calculated, the snapshot of the data is stored into an “analytical index” within HANA, and this index can be queried. Along with the index an external HANA view can be provided or the data can act as an infoprovider itself. Again, there are some limitations on the query features, but they are probably a bit less strict.

The drawback of this technology is that we are talking about a pre-calculated snapshot of the data. This needs to be kept up to date which needs to be scheduled somehow and takes time. For real-time “live” data that changes frequently this might not be an option, but if your data changes only once a day and your query is not compatible with an external HANA view directly, you might be able to get an external HANA view on top of the “query as infoprovider” index generated from the query.

EasyQuery and OData

By checking the “By EasyQuery” checkbox in the query properties, the query would get accessible over the network: this means by RFC call, a classic web service (SOAP), or – with an additional gateway – also via an OData compliant REST interface. Yes, you heard right, EasyQuery also supports OData without checking the “Per OData” checkbox. After checking the checkbox, you can review EasyQuery services in transaction EQMANAGER, and if you wanted OData access as well, you would create the gateway-service in transaction /IWBEP/ANA_SRV_GEN.

The “By OData” option is a more modern integration possibility, directly via the SAP gateway. According to the documentation it supports more features, has better performance, and means less hassle when configuring: check the checkbox, tap your shoulders, done.
So forget EasyQuery and use the native OData access right away! But it might not hurt to know that internally also this features shares some code with EasyQuery, so expect similar limitations.

Will it work?

Well, before getting too enthusiastic, let’s note again, that all restrictions of EasyQuery also apply to the OData access, which in particular means: (Source: here and here – except below from the documentation)

• Key figures cannot be mixed with characteristics.
• Key figures can only be used on the X axis.
• Characteristics can only be used on the Y axis.
• Query variables are allowed. Single values or intervals can be used (depending on the variable definition). Multiple selection (of single values) is not possible.
• The analytic query is static (standard view only).
• The analytic queries can contain hierarchies and free characteristics. The easy query runtime only returns the standard view of the analytic query however. Hierarchy data is returned. Navigation and drilldown in the hierarchy or using free characterstics is not supported however.
• The analytic query can contain formatting settings. The easy query runtime does not return this information however.
• The easy query does not support any exceptions. The easy query can display the query result but ignores exceptions.
• The easy query does not support any planning. The easy query runtime can display the query result but ignores planning functions and is read-only.

In particular, I was trying to retrieve data from a query with a column and a row structure, which did not work. You can have one structure only. A simple “5 x 3 grid” with colums for PY, Act, Plan and 5 key-figures in the rows – that is something pretty simple and common, for example to feed a few KPIs in a UI5 application. The column structure and its elements simply do not appear in the metadata of the OData service, it is practically ‘removed’ from the query. When running the query via OData an empty resultset is returned – which is obvious, if we consider that the structure is neither in the drilldown nor filtered to a single element. All the restrictions above render OData pretty useless in practical scenarios in my opinion, unless you find the way to completely ‘flip around’ your queries into a column-only-with-keyfigure-and-drilldown-in-the-rows format.

How will it work?

So you have set that OData checkbox. What next? Well, either the system generates the service definition for you in the background. Or it generates some dumps and error messages for you in the background… More on those later.

With a bit of luck, at this point in time there should be an OData service definition, but it is not yet exposed to the outside world. Visit transaction /IWFND/MAINT_SERVICE to see a list of activated services. You will not yet find your query in the list. Hit the Add Service button, select your system, search for the service and add it. If you do have a separate gateway server, you would execute these steps there – if you don’t or would like to test locally, you can use the built-in gateway of your BW ABAP server. In this case select ‘LOCAL‘ as system alias when adding the service.

Once the service is registered, it should also pop up in transaction SICF as a node within the path /sap/opu/odata/sap/[service_name]. And this means of course, that at this stage the service can be accessed via HTTP(S) already, with the same URL as the SICF path. For a test possibility directly within SAPGui hit the SAP Gateway Client button in the service maintenance, or use an external tool – eg. Postman – to test the service from outside, if your firewall settings allow.

Calling the service without additional parameters with the GET verb will give you an overview of the structure to expect. Most probably you will have some variables in your query, so your URL should look like this:

/sap/opu/odata/sap/[serviceName]/[queryName]([varName1]='[varValue1]’,[varName2]='[varValue2]’,…)/Results

For more details on possible parameters see here (and here) or try to figure it out from the metadata of the service. Endless hours of fun guaranteed. Let me save 2-3 of those hours with a few hints:

• As the variables act as an entity key in the OData call, and the key field list is static, you will need to supply all variables. Even the optional ones having a default value, and also exit-variables if they are not hidden.
• Variables of type interval need to have their from and to values passed separately. The second parameter having the suffix “To”.
• In general, do yourself a favor, and first open /sap/opu/odata/sap/[serviceName]/[queryName]$metadata and check the exact names of variables under the section where it says semantics=”parameters”
• Don’t forget the quotes around the values.
• Special characters need to be URL-escaped.
• If you are unsure about the variable value format, you can try querying the value help of the variable at /sap/opu/odata/sap/[serviceName]/[queryName]?$select=[variableName],[variableName]Text&$format=json
• If you make changes to the query, especially the list of variables used, the service does not notice! You need to reload the metadata of the service in /IWFND/MAINT_SERVICE.

In general, to avoid problems: Create own queries for OData, reduce the list of variables to the minimum required, remove any rows and columns that are not necessarily needed, and avoid any special query feature that you do not need. Rather than starting with a copy of a big and complex existing query, start with a very simple one, build it up step by step, and check after every change if OData is still working.

If you get stuck, here are some tips for debugging:

1. First of all watch for any errors or warnings in the Problems view of BW Modeling Tools. Issues which are just a warning in the query itself – like two structure components having the same technical name – might cause hard errors during generation.
2. Peep into the error log in transaction /IWBEP/ERROR_LOG – if there is any. This is usually the case if your OData request is syntactically invalid. If you face an error jump to Active Source and debug the spot to find out more.
3. Watch for the sap-message response header of the HTTP call for further error or warning messages.
4. You can try adding the debug parameter sap-ds-debug=true to the URL to get some further information in the call.
5. If you need to debug query execution itself, start in CL_NAT_ODATA_DATA_ABS->GET_ENTITYSET.

How will it work, once it works?

Once you manage to find the right query settings and the proper URL, this is how the result looks like: (if using $format=json)

Basically you get one entry for each row of the query – also the totals and subtotals have an own entry. The example above shows a detail row – totals have the TotaledProperties list filled so you know on which subtotal level you are. The query has a drilldown by calendar year, planning version and KPI name (account model, ZKPI is a characteristic). The fields with _T are the texts while the ones without _T are the keys. The infoprovider (0INFOPROV) has not been added to the query as drilldown characteristics, it is listed in the free characteristcs, but for some reason nevertheless included in the result.

The properties with the GUIDs represent the 3 columns of the column structure – amount, quantity and count key-figures of our data model. For each key-figure there is a value, a unit, and formatted value.

If you omit $format=json the result will be XML, and you can even request $format=xlsx to retrieve an Excel file – latter not being supported officially.

The BICS interface

If none of the interface above are suitable for your scenario, it’s time to get radical. The so-called BICS interface – BI Consumer Services – is a set of function modules and classes that are also used by standard SAP frontends like Analysis for Office, BEx and Lumira Designer to retrieve query data, set variables etc. And guess what the OData interface uses internally… Yes, BICS.

In terms of functionality this is definitely the most extensive interface. However it is not something that propagated for external use. It might not stay backwards-compatible, you will not get any support if you get stuck. But honestly, did this ever stop us from calling function modules?

If you record an RSTT trace while executing a use case from one of the frontends, you will see which BICS RFC function modules have been called in what order and with which parameters. This allows you to figure out what you have to call from your application to achieve the same.

When would such a radical approach be necessary?

Imagine you are trying to build a planning application with a custom frontend: A query delivers the data, and the user shall be allowed to execute a planning function which will recalculate some figures in that planning buffer. The updated data shall be displayed, but the data shall not yet be saved to the database. This is a scenario you will not be able to achieve with any of the other interfaces mentioned above – all of them are ‘query only’ interfaces, and typically stateless. The BICS interface however has a higher level object – the application – which can combine multiple queries and planning functions and sequences, and is aware of a user session holding changed data in the planning buffer.

For more details on the BICS interface see our blog post on that topic.

Cover image © Alice Wiegand / CC BY-SA 3.0 (via Wikimedia Commons)