Connect Excel with SAP Business ByDesign using OData for Analytics
Using Microsoft Excel and SAP Business ByDesign OData for Analytics you can download analytical data from ByDesign systems and use the data for offline analysis in Excel.
This blog post describes how to connect to ByD systems using ByD OData for Analytics and native Excel capabilities as well as Microsoft PowerPivot.
Further information about ByD OData for Analytics:
OData for SAP Business ByDesign Analytics
Beyond the possibility to connect Excel with ByD as described in this blog post, ByDesign offers various possibilities to work with Excel incl.
- open ByD reports in Excel using the ByD Add-in for MS Excel,
- download ByD reports in Excel xml format,
- download ByD reports and its underlying data sources in csv format,
- and Excel Workbooks using the ByD Add-in for MS Excel
Furthermore ByD offers a URL schema to launch ByD reports as Excel Workbooks.
Steps to use ByD OData for Analytics as Data Source in Excel PowerPivot
Microsoft PowerPivot (PowerQuery) is a tools that can be used to join query results and other data.
Further information:
Introduction to Microsoft Power Query for Excel – Excel
- Create a data source in Excel using POWERPIVOT >> Manage
- On the PowerPivot popup select: Get External Data >> From Data Service >> From OData Data Feed:
- On the Import Wizard enter the „Data Feed Url“:
https://myXXXXXX.sapbydesign.com/sap/byd/odata/crm_customerinvoicing_analytics.svc/RPCRMCIVIB_MQ0001QueryResults
Note:- The wizard does not process the OData service file, but the data feed
- For large data sources use ByD OData parameter to select and filter ByD data. For example:
myXXXXXX.sapbydesign.com/sap/byd/odata/crm_customerinvoicing_analytics.svc/RPCRMCIVIB_MQ0001QueryResults?$top=…&$select=…&$filter=…
For more details with regards to ByD OData access to high volume data sources, please check
SCN blog OData for SAP Business ByDesign Analytics, section “High Volume Data Sources“.
- Use buttom Advanced to enter the ByD credentials:
- Test the connection to ByD using buttom „Test Connection“
- Press buttom Next and select the Source Table:
- Use buttom Finish to extract the data from ByD … done:
Now you can display and work with ByD data in PowerPivot:
Steps to use ByD OData for Analytics as Data Source in Excel
- Create a data source in Excel using DATA >> From Other Sources >> From OData Data Feed:
- On the Data Connection Wizard enter the OData data feed URL and the ByD logon credentials:
Example OData URL:
https://myXXXXXX.sapbydesign.com/sap/byd/odata/crm_customerinvoicing_analytics.svc/RPCRMCIVIB_MQ0001QueryResults
Note:- The wizard does not process the OData service file, but the data feed
- For large data sources use ByD OData parameter to select and filter ByD data. For example:
my331895.sapbydesign.com/sap/byd/odata/crm_customerinvoicing_analytics.svc/RPCRMCIVIB_MQ0001QueryResults?$top=…&$select=…&$filter=…
For more details with regards to ByD OData access to high volume data sources, please check
SCN blog OData for SAP Business ByDesign Analytics, section “High Volume Data Sources“.
- Press buttom Next and select the Source Table
- Press buttom Next and enter a data connection name, etc.
- Press buttom Finish
- Import the data … done:
.
Hi Knut,
thanks for your post.
I prefer to use Power Query to connect the odata query to power Pivot. It is much more flexible and solid.
best regards
Daniel
Thank you Knut for this guide.
How Daniel wrote, I would also prefer Power Query instead of Power Pivot for requesting data via OData. I would only save data (data modeling) in Power Pivot.
Regarding this I posted following short overview (with references to your first great post about OData and ByDesign):
Using SAP ByDesign OData in Microsoft Power Query
Best Regards,
Andreas
Hi Knut,
thanks for this great tutorial.
Unfortunately it seems that for Excel on Mac, there is no OData Services available.
Is this correct or did you hear about another possibility?
Thanks,
Thomas
Hi Thomas,
I'm sorry, but I can't help you with Excel Limitations on Mac. Did you already try to contact the Microsoft Support?
Best regards,
Knut
Hi Knut,
I am new to odata, SAP BD and power queries, but have been trying for some days now to obtain the report data in excel from SAP to no avail, and I am hoping you can help….
If I use the following:
https://myxxxxx.sapbydesign.com/sap/byd/odata
https://myxxxxx.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc
https://myxxxxx.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/$metadata or
https://myxxxxx.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/$metadata?entityset=RP<ReportID>Queryresults
then I get the desired results, but the minute I try https://myxxxxx.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RP<ReportID>QueryResults I get a “Web page cannot be found 404” error, or a “Bad command (400)” error….. I have tried adding saml2=disabled to the url, but no joy, apologies for the basic nature of my question, but what am I doing wrong?
Regards
Laura
Hi Laura,
Your last URL example just points out the URL-pattern, but is not a valid OData URL. In fact you have to replace "<ReportID>" by the ID or your report that you your like to access.
Please find details in my blog post OData for SAP Business ByDesign Analytics.
Best regards,
Knut
Dear Knut,
Thank you for your comment. The URL I tried had the report ID in it as indicated by the pattern, but still generated the error,
Regards
Laura
Hi Laura,
Could by that the report is not part of the work center (here "Home"). Please check on the UI if the report is visible in the work center and that your user has access to the work center and the report.
Another possible root cause could be that your report has more than the allowed 50 properties. You can solve that by reducing the number of characteristics and key figures in the report (e.g. by copying the standard report) or by using the $select statement to choose the fields to be fetched (choose less than 50).
Regards, Knut