Skip to Content
Author's profile photo Andreas Eissmann

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

/wp-content/uploads/2015/10/1_804671.jpg

Technical information

/wp-content/uploads/2015/10/2_804672.jpg

3. Open Microsoft Excel

4. Go to PowerQuery Tab

/wp-content/uploads/2015/10/3_804673.jpg

5. Click on Get from web

/wp-content/uploads/2015/10/4_804692.jpg

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:

/wp-content/uploads/2015/10/5_804675.jpg

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):

/wp-content/uploads/2015/10/6_804676.jpg

/wp-content/uploads/2015/10/7_804677.jpg

  • Convert source to table

/wp-content/uploads/2015/10/8_804678.jpg

  • Split Value column

/wp-content/uploads/2015/10/9_804679.jpg

  • Split Value.results

/wp-content/uploads/2015/10/10_804680.jpg

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

/wp-content/uploads/2015/10/11_804681.jpg

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).

/wp-content/uploads/2015/10/12_804682.jpg

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.

/wp-content/uploads/2015/10/13_804686.jpg

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)

/wp-content/uploads/2015/10/14_804687.jpg

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

/wp-content/uploads/2015/10/15_804688.jpg

/wp-content/uploads/2015/10/16_804689.jpg

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Ivan Bondarenko
      Ivan Bondarenko

      Great! Thanks for the post. I would like to add only one things - auto-renaming.

      Technique described in post: https://bondarenkoivan.wordpress.com/2015/04/17/dynamic-table-headers-in-power-query-sap-bydesign-odata/

      Also I would like to share with community my version of function that helps to get reports data from ByD:

      https://github.com/IvanBond/pquery/blob/master/ByD.GetReportData.m

      Any feedback is highly appreciated.

      Author's profile photo Andreas Eissmann
      Andreas Eissmann
      Blog Post Author

      Thanks Ivan for you reply. Why do not cross posting your blog here in SCN? I would definitely like it to see your posts also in SAP Business ByDesign space on SCN.

      Cross posting is not forbitten...

      Best Regards,

      Andreas

      Author's profile photo Ivan Bondarenko
      Ivan Bondarenko

      Hi Andreas. I thought about this, however decided that my current posts are not fully relevant to this community. I hope to find time and make massive post on "Usage of Power Query for multi-tenant SAP ByDesing reporting solution" where I could descibe step by step my experience of building automated / scheduled models and dashboards.

      Totally agree with your phrase

      "In my opinion Microsoft Power Query is currently the most power full tool for creating dashboards for SAP Business ByDesign".

      Don't know more helpful tool existing currently for self-service analytics, which takes place in every department of any company.

      Kind regards,

      Ivan