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:
- Ask your SAP Administrator for the parameters you will need to build the connection URL
The URL has the following form:
-> 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:
- In KNIME configure the POST Request Node as follows
Basic Authentication is supported in most cases.
Insert your MDX Select Statement between the Statement tags.
- 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
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
Use the XPath node for further processing in KNIME
Configure the node as you need
Result could look like this:
Here are some links that might be useful for the SAP colleagues:
Thanks Carsten! A very helpful post.
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?
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
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!
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
From version 4.2. there is new option to read data from various SAP systems. Check here for more:
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.
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.
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.
First of all thank you for your post.
I have tried that but always I’m receiving same result:
?xml version=“1.0” encoding=“UTF-8”?>
SOAP-ENV:Envelope SOAP-ENV:encodingStyle=“http://schemas.xmlsoap.org/soap/encoding/” xmlns:SOAP-ENV=“http://schemas.xmlsoap.org/soap/envelope/”>
return xmlns:xsd=“XML Schema” xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance”>
anything Im doing wrong?
someone can help me here?
in browser it seems that we have this service:
w:definitions xmlns:s=“XML Schema” xmlns:http=“http://schemas.xmlsoap.org/wsdl/http/” xmlns:mime=“http://schemas.xmlsoap.org/wsdl/mime/” xmlns:tm=“http://microsoft.com/wsdl/mime/textMatching/” xmlns:soap=“http://schemas.xmlsoap.org/wsdl/soap/” xmlns:soapenc=“http://schemas.xmlsoap.org/soap/encoding/” xmlns:s0=“urn:schemas-microsoft-com:xml-analysis” xmlns:w=“http://schemas.xmlsoap.org/wsdl/” targetNamespace=“urn:schemas-microsoft-com:xml-analysis”>
s:schema targetNamespace=“urn:schemas-microsoft-com:xml-analysis” elementFormDefault=“qualified” attributeFormDefault=“qualified”>
s:element name=“RequestType” type=“s:string” nillable=“true”/>
s:element name=“Restrictions” nillable=“true”>
any help here will be apreciated.