DataSource.Error: An error happened while reading data from the provider

[ChielFaber]

Hello everyone,

I'm wondering if anybody can help me with the following problem.

I have a dataflow with facts and dimension that doesn't refresh since last friday. The dataflow has multiple staging dataflows as a source. The tables that are causing the errors are the f_dossier fact table and d_dossier dimension table. Both use a source dataflow that pulls data from a azure sql server database with a native query.

We use a company gateway to get our data from the azure sql database to the dataflows. I do know that our azure sql database recieved an update the day before our errors occured.

In the refresh log I get the following error message:

2023-03-06 15:37:04,f_dossier (PRD)_nieuw,Failed,f_dossier,NA,Failed,2023-03-06 15:37:04,2023-03-06 15:41:49,00:04:44.4100,NA,NA,NA,NA,NA,NA,Error: Data Source Error : DataSource.Error: An error happened while reading data from the provider: 'An error occurred while sending the request.' DataSourceKind = <ccon>PowerPlatformDataflows</ccon> DataSourcePath = <ccon>PowerPlatformDataflows</ccon>. RootActivityId = 6e96bad1-73ef-4477-891d-a7aaf43be2a9.Param1 = DataSource.Error: An error happened while reading data from the provider: 'An error occurred while sending the request.' DataSourceKind = <ccon>PowerPlatformDataflows</ccon> DataSourcePath = <ccon>PowerPlatformDataflows</ccon> Request ID: 9e656deb-8a45-7313-4730-1a785a6cfd5b.

I've already searched for a solution on the internet, but have not found an answer so far.

Any help is welcome. Thanks in advance! 

[ChielFaber · Accepted solution]

The above solution does work albeit with a negative impact on the performance due to stopping the query folding opportunities. 

While searching further on the internet I found a solution that works and it's really simple, but it also feels a little strange. For the query folding principle to keep working witht the PowerPlatform.Dataflows connector it appears that you need admin rights to the gateway. When I asked our PowerBI admin to give me admin rights on the gateway the issue was resolved and our dataflow load time went back to expected (20 minutes instead of 2 hours).

[mark_carlisle]

Hi @ChielFaber Do you have the source(s) for your solution I'd like to do some reading up on it I have a dataset that appears to have the same issue.

[ChielFaber]

i don't have a clear cut list of blogposts for you because I found it to be a not very common problem.. At least not a problem that's been written about. I've read a lot of blogposts, comments and discussions and didn't find one good solution. I read semi-related topics and put 1 and 2 together and tried different solutions. This ultimatly brought me to the solution.

Regarding the connectors I found this reply by bslattz :

https://github.com/MicrosoftDocs/powerbi-docs/issues/3703

This made me try the legacy connector and that worked albeit very slow.

As a possible solution based on my knowledge I then the stopfolding code to refresh the dataflow. This also worked but it was also much slower than the original.

I've also used this. Not really related but it did help me find the solution regarding the admin rights to the gateway:

https://community.fabric.microsoft.com/t5/Service/Error-The-connection-you-are-trying-to-use-has-not...

[ChielFaber]

I managed to find two solutions to my problem. The problem seemed to only occur for the table that was connected through a linked table to the staging dataflow with a native.sql query. 

When  I change the connector from PowerPlatform.Dataflows to  PowerBI.Dataflows the dataflow refreshes again. The second solution is to use the Table.StopFolding function on the source table. 

What I find strange is that the connection used to work for months and the error only appeared a few days ago. \

As a consequence of the solutions the load time of the dataflow has increased.  

I'm still trying to figure out the exact reason why the previous connector PowerPlatform.Dataflows stopped working. Does anyone of you have an idea?

Regards,

Chiel