Skip to Content

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

 

.

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

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

    (0) 
  2. 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

    (0) 
    1. Knut Heusermann 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

       

      (0) 
  3. Laura Smith

    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

     

    (0) 
  4. Laura Smith

    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

     

    (0) 
    1. Knut Heusermann 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

      (0) 

Leave a Reply