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.
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
-
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:
Have fun!
BR
Carsten
