Load data from SAP into PowerPivot for Excel

Last week at teched I met Patrick Theobald from Theobald Software.  Theobald Software GmbH is specialized on the development of products for SAP integration. Seeing the great potential in exposing SAP data to PowerPivot he created a go between service between SAP and PowerPivot:

You can define views the Xtract for PowerPivot designer on top of these sap entities:

Which then will transform this data into a ATOM Feed which we can use in PowerPivot, these feeds will be hosted by the Xtract for PowerPivot Server. Which is just a lightweight service.

In this blog post we are going to load data from a SAP table into PowerPivot.

So we first we open up the designer:

We then need to connect this designer to a server by clicking connect, this will enable us to build new extractions of SAP, we again can select what type we want:

In my case I selected a table

Next we need to make a connection to SAP:

I used a shared connection here.

I then can go into sap and select a table i want to expose:

I selected the SBOOK table here, we can also select all the columns we to expose from this table:

What else is great is that we can give certain users access to this feed or deny access:

Now we just can just press run, the extraction will be saved on the server and we are ready to extract the data from SAP:

We see some additional settings here we can set, but more important is the feed URL generated by the feed server, you can use this url in PowerPivot to load the feed. Or just press Run in PowerPivot:

And then the information will be loaded into PowerPivot.

The server now contains this feed and is at the disposal of the users to create a new model or refresh the current one with new data. Nothing is stored inside the software, the data just flows through it.

So very easy to set up and maintain and a great way to get your data. At teched they also showed a session on Extracting SAP with PowerPivot which you can see online here. Theobald starts at 55 min.

I will most definitely show more on SAP and PowerPivot later.

  • marlina

    how about to connect powerpivot using REST to SAP Netweaver PI (web services). FYI, our implementer unsuccesfull trying this approach. pleas advise.

    • Kasper de Jonge

      Hi Marlina,
      I have tried this tool when I wrote the blog post and it worked great. I haven’t tried since then. I would contact Theobald, they are usually very quick to reply.

      Thanks,
      KAsper

  • De Villiers Walton

    Kaspar, I thought your readers may be interested to learn about an alternative 3rd party PowerPivot connector for SAP developed by De Villiers Walton. You can read about it in the following blog: http://www.dvwsolutions.com/blog/entry/enabling-flexible-reporting-with-powerpivot-and-sap.html