Dataset refresh started spontaneously failing in Service but still works in Desktop

[amthog]

Hi,

Just seeking some troubleshooting advice and whether this is something that we definitely need to refer to MS support.

We have a report published to the Service in a workspace on Premium capacity.
It has a daily refresh schedule.
The report uses some SharePoint file and folder datasources connecting to excel files in our SharePoint Online tenant without a gateway i.e. "cloud" data sources. The same data sources are in use whether in the Service or in Desktop.

The scheduled refresh started failing (on May 1).
The report had not been re-published.
The underlying data sources had not been changed (as far as we could tell)

Our main barriers to getting any traction on this problem are:

• The refresh works fine in Desktop
• Refresh errors in the Service keep changing each time, with occasional repetition but no particular pattern.

For example the most recent failures have had the following messages (note some file paths removed for security reasons) :

• <pii>SharePoint: Request failed: https://<<path to file 1 - removed>>_xlsx/_api/contextinfo</pii>. The exception was raised by the IDbCommand interface. Table: tblDSO
• The underlying connection was closed: An unexpected error occurred on a send.. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. Table: tblRevenue.
• <pii>SharePoint: Request failed: https://<<path to file 2 - removed>>_xlsx/_api/contextinfo</pii>. The exception was raised by the IDbCommand interface. Table: tblDSO
• <pii>SharePoint: Request failed: https://<<path to file 2 - removed>>_xlsx/_api/contextinfo</pii>. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. Table: tblRevenue.
• The underlying connection was closed: An unexpected error occurred on a send.. The exception was raised by the IDbCommand interface. Table: qryTotalHoursPerMonth
• The underlying connection was closed: An unexpected error occurred on a send.. The exception was raised by the IDbCommand interface. Table: qryTotalNRPerMonth.
• The underlying connection was closed: An unexpected error occurred on a send.. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. Table: tblCost.
• The underlying connection was closed: An unexpected error occurred on a send.. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. Table: tblRevenue.

We have tried the following as part of troubleshooting with no resolution:

• Refreshing the original (source) pbix in Desktop works without error.
• Downloading the current pbix from the Service and refreshing in Desktop works without error.
• Re-published to the Service to but refresh still failed in Service.
• Altered the datasources in the Service to use the same (personal) credentials that work when refreshing in Desktop.
• Reviewed monitoring e.g. looking to see if any limits are being hit (hence varying error message ??)
• Reviewed audit logs.

Does anyone thoughts on potential root causes or further troubleshooting steps we have not tried ? Something we have
Or should we just refer this to MS support ?

Thanks

[Anonymous]

In case it may help anyone: We had exactly the same issue. Power BI was connected to several files on sharepoint using basically only two types of connector (1. Sharepoint file connector and, 2. Sharepoint folder connector). One to connect directly to files, and other to merge similar files we stored in sharepoint folders. Suddenly the refresh started failing. We received this idb command interface error message pointing to random tables in our data in every load attempt.  After several months discussing with power bi and sharepoint support teams we discovered what the issue was. Basically, sharepoint has a limitation of maximum paralel access to data stored in it. On PBI desktop one is able to turn off parallel loading of tables, whereas in PBI server there is no such configuration. This means that queries created on PBI desktop accessing files on sharepoint will not always work on server. Although I see this is as a major product flaw that prevents power query from becoming an scalable solution, the response I've got from Microsoft product team is that no solution was in sight for this issue, and nothing was on their pipeline now or in any foreseable future to approach this. Given that, my suggestion is that you aknowledge the limitations of this tool, remove your data transformation steps from powerquery, migrate them to other reliable tools such as alteryx, Pandas, pentaho, or others.

[Anonymous]

Actually, there is an approach that can help.  I resolved it for us by limiting the queries to ShapePoint.  I made a base query, and then referenced all the other queries to it.  This way, SharePoint is being pinged once rather than multiple times.  Hope this helps you.

[ctowery]

Any resolution to this?
We are now experiencing this exact issue.

[Anonymous]

Ok, I have exactly the same issue, what should we do then? There is no solution for this?

[amthog]

Replying to my own post just to thank all those who responded. 

The response from @nickyvv is correct. We opened an issue with Microsoft and they have confirmed this. 
I will mark that as the solution for now.

Thanks also to those who responded confirming they were seeing similar issues as that also helped.

Cheers 

[Anonymous]

I am seeing exactly the same issue.

[jcecil2]

Unfortunately, I don't have a solution for you. However, I think it is beneficial that you know my team has been experiencing the same errors in our premium space on a couple reports that rely heavily on sharepoint data sources. 

Hopefully Microsoft support is able to get a fix in place!

Best wishes,

Jacob

[nickyvv · Accepted solution]

Hi @amthog, @jcecil2,

I just checked the support page and there seems to be an issue with Premium workspaces that have SharePoint Online as a datasource.

Please check back on that page for a solution or workaround, and maybe you could still also file a support ticket. It might help solve the issue faster.