Using SAP ByDesign OData in Microsoft Power Query

Eine deutsche Version des Blogs ist hier verfügbar-.

In this blog I would like to give you a short overview about extended functionality of SAP Business ByDesign`s OData reporting feed.

In my opinion Microsoft Power Query is currently the most power full tool for creating dashboards for SAP Business ByDesign. Regarding this you can find here a step by step guide to get ByDesign OData feed working with Microsoft Power Query:

1. Searching for a report in SAP Business ByDesign, you want to use in Power Query

2. Get the technical name of the report (for example ZB68545FD12E4222BE8B904)

In Business Analytics report view add ID field via Personalize or open report and click in report on Details for Technical information:

Report view

Technical information

3. Open Microsoft Excel

4. Go to PowerQuery Tab

5. Click on Get from web

6. Insert report URL with specific oData parameters (for parameters see following blog post http://scn.sap.com/community/business-bydesign/blog/2015/03/10/odata-for-sap-business-bydesign-analytics of Knut Heusermann)

If single sign-on is configured in system you also have to add the following parameter to url: saml2=disabled

7. Chose Standard Authorization:

Example request for Sales order volume:

https://myXXXXXX.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPCRMSLOIB_Q0001QueryResults?$top=1000000&$format=json

(for structure of URL see also following blog post http://scn.sap.com/community/business-bydesign/blog/2015/03/10/odata-for-sap-business-bydesign-analytics of Knut Heusermann)

I would recommend to use JSON format because it is more performant as XML.

8. Now you can go ahead with configuration of your request

Example:

• Define source stream as JSON by clicking on Option logo behind the source (on the right side):

• Convert source to table

• Split Value column

• Split Value.results

• Split Records (At this point you have to choose which Characteristics and Key Figures you would like to use)

9. Now you find on the right side the automatically generated steps from Power Query. You can modify all these steps by simple click on it (for example renaming).

10. You can also rename column headers, delete columns or modify data in columns (for example splitting by specific char) (Please find more information about Power Query possibilities in this blog http://blogs.msdn.com/b/powerbi/archive/2014/07/08/getting-started-with-excel-and-power-bi-series.aspx)

11. If you now use Save and Load Button, your Power Query Request loads data in default defined data model.

12. You can define in the Options if Power Query should load by default data in Excel table or directly in Power Pivot (https://msdn.microsoft.com/en-us/library/gg399183(v=sql.110).aspx)

13. Now you have data available in data model and you can work with it