Forum Discussion

Brandon Tempel's avatar
Brandon Tempel
Copper Contributor
Sep 07, 2018

SharePoint Online with ODATA Connection to BI Tools

After migrating our SharePoint (on-premise) solution to the cloud, we can no longer establish a connection between SharePoint content and our BI Tools utilizing the ODATA connection.  Any help or suggestions would be greatly appreciated.

 

Service Root URI (worked w/ on-premise):

http://wss-test.kess.keint.com/sites/BusIntelligence/_vti_bin/ListData.svc/

 

Other variations we've tried:

https://kecloud.sharepoint.com/sites/BusIntelligence/default.aspx
https://kecloud.sharepoint.com/sites/BusIntelligence/_api/web/lists/getbytitle('Assigned%20Requests')/items
https://kecloud.sharepoint.com/sites/BusIntelligence/_vti_bin/ListData.svc/

 

  • By URL connection string samples, you mean just examples of accessing the OData? If so, this doc  has a number of examples. Again, the trick is to simply paste the URL in chrome. Other browsers will certainly work, but the other browsers assume you don't want to see raw data and either prompt to have you download a file or try to hide it from you. Chrome just shows the raw data. For example, your URL with lists/getbytitle is fine, and will return all the list items (or, the first batch if there's a lot of items).

  • First, you can use Chrome to view any of the data urls, so you don't have to wonder if you have a working URL. Second, what BI tools are you using and what error message? Without knowing any details I'll take a guess that the issue is related to authentication, as Online has a completely different authentication mechanism than on-prem, and not all tools support it.

    • Brandon Tempel's avatar
      Brandon Tempel
      Copper Contributor

      That was my fear!  We resolved the issues we were having with on-prem by using "basic authentication".  Before we migrated to the cloud, I asked what that looked like on the cloud and was told that I should have more flexibility and functionality, not less.  We are using the Information Design Tool (IDT) associated with SAP's Business Objects Suite.  It offers a number of different connection types, but currently working with a "Generic OData 2.0" connector (because it worked with on-prem).  The error message I get is:

       

      "A problem occurred while communicating with the provider. Detailed message: Server error:  (response: status=403, reason='Forbidden', request='/sites/BusIntelligence/_api/web/lists/getbytitle('AssignedRequests')/items/$metadata')"

       

      Thank you, Michael!  I appreciate your input, as I've not been able to find much to help.

      • Michael Gauntlett's avatar
        Michael Gauntlett
        Brass Contributor

        This may not help you, but both Excel and Power BI can easily connect to lists in SharePoint online.

Resources