Occasional scheduled refresh errors of postgre SQL: PostgreSQL: Exception while reading from stream

[zenisekd]

Hi everyone, 

I have a problem that I can't figure out by myslef...

I use Posgre SQL database (on AWS) IMPORT. 

I loaded about 30 tables (I use the dataset as a datasilo). 

I made a couple of complex calculations in DAX, in order to get a ABC-XYZ analysis. 

Everything eventually worked fine. I managed to upload the file and refresh it. 

Now here comes the problem: Once I schedule a refresh, it works only about 30% of the time. 

Giving me an error: PostgreSQL: Exception while reading from stream.

To me, the puzzle is, that another dataset (with just a few tables) from the same source works fine every time. 

Any suggestions what should I do?

I have read something about setting up Timeout, however I am not sure why would it be sometimes working and sometimes not...

Thanks in advance for your advice.

[zenisekd]

After some communication with MS support, I have learned that the exception is:

Microsoft.PowerBI.Common.PowerBIModelRefreshFailedDueToUserErrorException: Data refresh failed due to an error in the data processing engine due to user.


Error ErrorCode="3242524690" Description="{"error":{"code":"ModelRefresh_ShortMessage_ProcessingError","pbi.error":{"code":"ModelRefresh_ShortMessage_ProcessingError","parameters":{},"details":[{"code":"Message","detail":{"type":1,"value":"PostgreSQL: Exception while reading from stream"}}],"exceptionCulprit":1}}}" Source="Microsoft SQL Server 2014 Analysis Services" HelpFile=""

They will further investigate it.

[lbendlin]

Could be query timeout issues but my money is on an overloaded PostgreSQL server.  In your Power BI file, disallow parallel loading of tables and see if that improves reliability (at the expense of speed).

[zenisekd]

Timeout set to 90 minutes, didnt help. Now I am testing your slow solution...

[lbendlin]

Also talk to the owner of the PostgreSQL server to see if they can do diagnostics on their end, and maybe add an index or two.

[zenisekd]

it runs on AWS... not sure, if there could be an issue.

[Anonymous]

I have the same intermittent problem refreshing multiple datasets with PostgreSQL in AWS. Did you get closer to a solution ?

[zenisekd]

After a lot of digging it turned up, that it was caused by the way I cleaned the tables in Power Query and connected them afterward (I think that there was some duplicity in ID in some table for some strange reason). For some reason, I would work in the desktop and it could be uploaded to Power BI, but when it tried to do a scheduled refresh, it would fail. 

So my recommendation is to go thoroughly through your Power query steps and model and look for any kind of error when loading the data to your model...