SAP ERP as data source for Microsoft BI (Part 5)

Visual AdHoc Analysis with Microsoft Power BI on SAP Suppose you want to analyze your SAP data flexible and without having to load it into your Data Warehouse. Maybe you need a very quick solution or this kind of analysis is only needed temporarily. Wouldn’t it be great to just open Excel, get your data and do some pivoting and charts? Here are is an example for how you could do this.

OData feed via SAP Netweaver Gateway

Since 2014 SAP offers a way to make their data accessible via OData feeds over the Netweaver Gateway. To demonstrate this, I’ve used some sample feeds that SAP offers on their public sample Gateway application. SAP Netweaver Gateway Demo offers various sample applications that expose data via OData feeds. In the following I’ll use the EPM sample with sales order data.

But first of all, what are OData feeds? In a world of data mashups where a wide range of clients like web browsers, mobile devices and BI tools connects to data sources in different places, for example databases, cloud storage, web market places, there is a need for a universal way to model this data and connect to it through a standard protocol. This is the purpose of OData: The data provider exposes its data in a way so that every client can access it. If you want to learn more about OData there is a great whitepaper from Microsoft to start with: http://bit.ly/1H8fhY1  This overview picture shows the basic principle: Back to our Demo application. The data exposed through the demo application is quite simple:

But how can we connect to this sources via Excel? First get yourself an account for the SAP Demo system. This is done in a few minutes. Second, if you haven’t already done this, please download Power Query for Excel, available from Microsoft Download under http://bit.ly/1I91nDS and you’re ready to start. Start Excel and you’ll find Power Query as new option in Excel ribbon. Select ‚From Other Sources / From OData feed‘:

To connect to it you need the account information from your SCN Registration. After you are connected to the demo application Power Query retrieves metadata information and opens Item Navigator for this feed. I’ve selected SalesOrderCollection and choose to add this data to a new PowerPivot Data Model:

After data was loaded I choose ‚Edit‘ to open the Power Query Editor. You can browse through data and see that there is one row per Sales Order. But what is missing, is information about each single order line (Products, etc.) and information about Business Partners. The corresponding columns just show ‚Table‘ for LineItems and ‚Record‘ for BusinessPartner. In the column header you find a symbol indicating, that there is more information to get from this feed. This ‚Expand‘ functionality is special to the OData Data Model and a kind of denormalization if you compare it with a relational Data Model.

After clicking this ‚Expand‘ symbol you can choose which extra information you need. I selected ‚Product‘, ‚Quantity‘, ‚GrossAmount‘ from LineItem and ‚CompanyName‘, ‚Country‘ from BusinessPartner. After confirming you’ll notice that the additional data is loaded from data source and you’ll see the selected columns. Then select ‚Close & Load‘ from ribbon to load the data into your PowerPivot Model. Well, you’re already done! With a few clicks you connected to your SAP live data and imported the information you need. Now explore your data with all the nice Excel capabilities and refresh it quickly to get updated information. Here is my simple try for SalesOrder data: Of course, you’ll need SAP Netweaver Gateway installed and have someone to prepare the Odata feeds for you to be able to access SAP data in this way. If you don’t have this possibility there is another way again by using 3rd party product from Theobald Software.

PowerPivot Extractor XtractPPV from Theobald Software

If you don’t have Odata feeds on SAP Netweaver Gateway available this tool is the missing link between Excel and your SAP live data. It extracts data from SAP and exposes this data as Odata feed to Excel. So, after an easy installation and configuration process you’ll work the same way as shown above with access to SAP tables, queries, BAPIs, cubes, etc. Please refer to Theobald Software XtractPPV to see all the capabilities and get a free evaluation copy: http://theobald-software.com/de/xtract-ppv-produktinfo.html