How to extract Data from SAP Business Warehouse via REST

How to extract Data from SAP Business Warehouse?

You want to extract data from you SAP BW. Then here´s a blog post how to do this.

First of all I have to say that there are several hints and limitations that might get you confused. You should ask a colleague from the SAP team (Administrator or Developer) to support you.

Several configuration and SAP landscape settings are possible so that the specific set up from customer to customer might look quite different.

In general it is necessary that the MDX Interface on BW is activated, the user who wants to access the BW has the right user authorizations to be able to connect to the BW system and of course is allowed to view the content (BEX query for example) he needs.

Assuming a 3rd party application is already in place using the same MDX interface than you will use with KNIME then the following steps would be necessary:

  1. Ask your SAP Administrator for the parameters you will need to build the connection URL
    The URL has the following form:

image

-> http or https
-> the so called message server
-> the used port

And also the used SAP client (3 digits) very often 020

(In SAP System use transaction SE37 -> function module RSBB_URL_PREFIX_GET; Export Parameter E_URL_PREFIX contains what you are searching for)

Example connection URL:
image

  1. In KNIME configure the POST Request Node as follows

Basic Authentication is supported in most cases.

Request Body

Insert your MDX Select Statement between the Statement tags.

SELECT [Measures].Members
On COLUMNS
FROM [$0TCT_VCA1]

  • This would select all key figures from the cube 0TCT_VCA1 (this is part of the technical BW content and should be available on your BW also) (hopefully there is also data inside)
  • Ask your BW Administrator which cube you could use for the first tests
  • SAP transaction MDXTEST could help building your own MDX Statements
  1. Save & Execute the Post Request
    If everything went well you should receive a response from the BW server which contains a XML message with the content and technical metadata inside

  2. Use the XPath node for further processing in KNIME
    Configure the node as you need

  3. Result could look like this:
    image

image

Here are some links that might be useful for the SAP colleagues:

Have fun!

BR
Carsten

8 Likes

Thanks Carsten! A very helpful post.

1 Like

Thanks for the thorough instructions. While the node does execute, the BLOB that get’s produced is empty and my status=403. Any ideas as to what could be causing this?
image

Hi,
sorry for my late answer, but i was on vacation. This could be caused by many things. Try to reduce the possible reasons with starting from the very beginning. Is it possible to reach the XMLA service description from SAP BW? Just try to call the URL http://serverxzy.domain.com:6666/sap/bw/xml/soap/xmla (adapted to your settings) via Web Browser. If you get no response it could be a resaon that the MDX Service is not activated on your BW system or that you do not have enough permissions. Could also be the case that the service is only available via HTTPS. In every case you should ask your SAP BW Administrator how MDX is configured, BR Carsten

1 Like

Thanks so much for the feedback. So bit of a long story but.
We had to change our network settings from native to manual to get our Athena connections working. After your feedback, BR, I switched back to native and it worked!
Thanks!

1 Like

Buen día, esta solución aun funciona. Tengo entendido que en el mercado hay Driver y conectores que hacen esto. Sin embargo quisiera saber si funciona para SAP R3 Business Warehouse

Muchas gracias

Hello there!

From version 4.2. there is new option to read data from various SAP systems. Check here for more:
https://www.knime.com/easy-access-to-your-sap-data

Br,
Ivan

2 Likes

Yes it is still working. Even with a BW System on Netweaver 7.40 or newer. Also BW/4HANA should also work because the MDX Interface is still supported.

1 Like

It is also an option to buy the 3rd party connector like the one from Theobald. But without additional software and licences the MDX interface is the only way to connect to SAP BW.

Hi,

If anyone is still looking for SAP connectivity, please check out the KNIME Connector for SAP(KCS):

Our connector works even if the MDX flag is not switched on against BW objects.