Using OData Feed with SharePoint

[irina_l]

I want to use OData Feed connection to get data from my sharepoint site. When I connect to root site collection it works perfectly, but it's impossible connect to any site which is not a root site collection.
http://myserver/_vti_bin/ListData.svc - works
http://myserver/sites/anysite/_vti_bin/ListData.svc - doesn't work

DataSource.Error: Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed: 
OData Version: 3 and 4, Error: The remote server returned an error: (500) Internal Server Error. (Internal Server Error)
OData Version: 4, Error: The remote server returned an error: (500) Internal Server Error. (Internal Server Error)
OData Version: 3, Error: The remote server returned an error: (500) Internal Server Error. (Internal Server Error)
Details:
    DataSourceKind=OData
    DataSourcePath=http://sp2016/sites/rem/_vti_bin/ListData.svc
    SPRequestGuid=a44cdb9d-a582-103c-70f3-9827ef1368d7, a44cdb9d-7585-103c-70f3-9dab4bbc9ad2, a44cdb9d-1588-103c-70f3-9bdac7542290

[v-haibl-msft]

@irina_l

Can you connect to the subsite with same link and user credential in browser? Have you tried to use the built-in SharePoint connector in PBI Desktop?

Best Regards,

Herbert

[irina_l]

@v-haibl-msft , yes it's connected without any issues in browser, it also works in PowerPivot in Excel (using OData Feed). I tried built-in SharePoint connector(if you mean connection to SharePoint list)  - it works but it is not what I need. I need use my own service to get specific data using OData Feed. It works in Power BI when I connect to the root site collection and doesn't work when a site is not root site collection. So I checked connection to standard SharePoint service (_vti_bin/ListData.svc) - behaviour is the same as I described above.
It can be easily reproduced on any sharepoint server, it's not my local issue only.

[v-haibl-msft]

@irina_l

I’ve reported it internally to Power BI Team: CRI 33087277
I’ll post here once I get any update about it.

Best Regards,
Herbert

[v-haibl-msft]

@irina_l

I’ve got response from the Product Team.

This is a SharePoint issue. The SharePoint server is returning an error.

Maybe you can try with another SharePoint server or try to ask for help in the SharePoint forum to see how to troublshooting the error message returned by the SharePoint server.

Best Regards,
Herbert

[irina_l]

@v-haibl-msft
thanks for your help.

It's a pity that the Product Team doesn't want investigate this issue. For me it looks like the issue on the side of Power BI. It can be easily reproduced on any sharepoint server with any service (custom service or even with standard SharePoint service ListData.svc). At the same time such service works in browser, in Excel PowerPivot, etc.

May be Power BI converts URL and use it as relative to the root site collection  - don't know. 

[v-haibl-msft]

@irina_l

In Mar 2017 version of Power BI Desktop, we tried to use OData Feed connection to get data from sub SharePoint site and got no error message. The connection is successful.

We tested on both SharePoint Online and on-premises SharePoint Server 2013 (Build 15.0.4885.1000).

Could you please provide your detailed test environment? If possible, you can also provide a fiddler trace to us, it should be helpful to address this problem exactly. You can start the trace before make the OData connection, and share the saved .saz file to us through online file service like OneDrive.

I can try to communicate with the product team to see if they can investigate it more.

Best Regards,

Herbert

[irina_l]

@v-haibl-msft
to ensure we are discussing the same things because 'sub site' can be used with different meanings:
http://myserver/ - root site collection (works fine)
http://myserver/subsite - sub site on the root site collection (works fine)
http://myserver/sites/mysite - not root site collection (doesn't work)

here two fiddler captures: the first one with connection by PowerPivot for Excel, the second one with connection by Power BI. Both use the same url - 'http://lisovskaya-i/sites/rem/_vti_bin/Elearningforce/Dashboard/Data.svc/Courses'. As you can see PowerPivot returns data, at the same time Power BI converts few url to relative to the server so for my service's code it looks like 'http://lisovskaya-i' is current sharepoint site (instead of 'http://lisovskaya-i/sites/rem') and it cannot get data correctly.

I hope it will help to understand my issue. Thanks in advance.

p.s. probably I was mistaken when said that issue can be reproduced also with standard sharepoint service _vti_bin/ListData.svc - I found data on my site which can cause issue with standard sp service.

[v-haibl-msft]

@irina_l

I think we are discussing the same thing since we used the not root site collection as below.

I’ve provided your fiddler traces to product team and try to ask them to look into them again. I’ll post here once I get any update about it.

Best Regards,

Herbert