Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
amthog
Regular Visitor

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

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

1 ACCEPTED SOLUTION
nickyvv
Community Champion
Community Champion

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.



Did I answer your question? Mark my post as a solution!

Blog: nickyvv.com | @NickyvV


View solution in original post

8 REPLIES 8
Anonymous
Not applicable

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
Not applicable

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
Helper I
Helper I

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

Anonymous
Not applicable

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

 

amthog
Regular Visitor

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
Not applicable

I am seeing exactly the same issue.

jcecil2
Helper I
Helper I

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
Community Champion
Community Champion

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.



Did I answer your question? Mark my post as a solution!

Blog: nickyvv.com | @NickyvV


Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.