SharePoint Online with ODATA Connection to BI Tools

[Brandon Tempel]

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'...
https://kecloud.sharepoint.com/sites/BusIntelligence/_vti_bin/ListData.svc/

[#2]

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.

[#3]

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.

[#4]

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

[#5]

I would be interested in looking at the URL connection string if you had some examples?  I know they may not be the same, but it might spark some ideas.  Unfortunately, we are not licensed with PowerBI to publish content and the goal was to create a project tracking cockpit published to the web for ease of access, using Business Objects.  This was a way to put some analytics to how we spend our time.  Are we hitting target dates, what types of projects are we working on, etc. A way to expand the Business Objects' package of value by integrating with SharePoint. Thanks again for sharing your knowledge, Michael!

[#6 · Accepted solution]

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

[#7]

For now, I'm going to mark it as lost functionality between Microsoft and SAP for migrating office to the cloud.  I've had our 365 administrators do the same test and still no luck withing the Information Design Tool.  I'll try the same tests after we upgrade Business Objects to the latest SP.  Thanks again for all of your help, Michael!

[#8]

@Brandon Tempel : did you ever had a solution for this, through IDT ?

Thanks for your answer