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:
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:
(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
- 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
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:
Any feedback is highly appreciated.
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...
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.