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.

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

Leave a Reply