Skip to Content
Author's profile photo Knut Heusermann

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

 

  1. Create a data source in Excel using POWERPIVOT >> Manage
  2. On the PowerPivot popup select: Get External Data >> From Data Service >> From OData Data Feed:
    PowerPivot_1.jpg
  3. On the Import Wizard enter the Data Feed Url“:
    https://myXXXXXX.sapbydesign.com/sap/byd/odata/crm_customerinvoicing_analytics.svc/RPCRMCIVIB_MQ0001QueryResults
    Note:

    1. The wizard does not process the OData service file, but the data feed
    2. 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“.
  4. Use buttom Advanced to enter the ByD credentials:
    PowerPivot_2.png
  5. Test the connection to ByD using buttom Test Connection

  6. Press buttom Next and select the Source Table:
    PowerPivot_3.jpg
  7. Use buttom Finish to extract the data from ByD … done:
    PowerPivot_4.jpg

Now you can display and work with ByD data in PowerPivot:

PowerPivot_5.jpg

 

 

 

Steps to use ByD OData for Analytics as Data Source in Excel

 

  1. Create a data source in Excel using DATA >> From Other Sources >> From OData Data Feed:
    Excel_1.png
  2. On the Data Connection Wizard enter the OData data feed URL and the ByD logon credentials:
    Excel_2.jpg
    Example OData URL:
    https://myXXXXXX.sapbydesign.com/sap/byd/odata/crm_customerinvoicing_analytics.svc/RPCRMCIVIB_MQ0001QueryResults
    Note:

    1. The wizard does not process the OData service file, but the data feed
    2. 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“.

  3. Press buttom Next and select the Source Table
  4. Press buttom Next and enter a data connection name, etc.
  5. Press buttom Finish
  6. Import the data … done:
    Excel_3.jpg

 

.

Assigned Tags

      8 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Daniel Berwanger
      Daniel Berwanger

      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

      Author's profile photo Andreas Eissmann
      Andreas Eissmann

      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

      Author's profile photo Thomas Kramer
      Thomas Kramer

      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

      Author's profile photo Knut Heusermann
      Knut Heusermann
      Blog Post Author

      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

       

      Author's profile photo Former Member
      Former Member

      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

       

      Author's profile photo Knut Heusermann
      Knut Heusermann
      Blog Post Author

      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

      Author's profile photo Former Member
      Former Member

      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

       

      Author's profile photo Knut Heusermann
      Knut Heusermann
      Blog Post Author

      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