Power BI Import Model Refresh from Databricks SQL Whse - Query has been timed out due to inactivity

[SethParker]

We have an intermittant issue where occasionally a partition in our Power BI Import Dataset times out at 5 hours.  When I look at Query History in Databricks SQL, I see a query that failed with the following error message:  "Query has been timed out due to inactivity".  That query info screen, in one example, shows that the total Wall-clock duration was 15.587seconds.  But the Result fetching by client row shows 1.04hours.  Power BI doesn't seem to know that the query failed as it continues to wait for results until it times out at 5 hours (it is a Power BI Premium Per User dataset).  In other examples, the query wall-clock sometimes runs for a few minutes, but the Result fetching by client row always shows a very long time (over an hour to a few hours) relative to the wall-clock.

I would like to know what this error message means in Databricks.  It looks like the query is returning rows in a normal amount of time, but the query still fails.  I have not been able to find what this error message indicates I should address.  

I have attached a screenshot of the example query I mentioned above that failed with this error.

This error is intermittent as the model does not usually time out, but when it does, I always find a query with this error message in query history during the time of the load.  It is also not always on the same partition, but it is always on a partition on one of the larger tables.  The full refresh on this particular data model usually takes around 2 hours.  When this error occurs, it times out at 5 hours.  Then the retry will complete in the normal 2 hours.

Another issue, albeit this issue is likely with Power BI and not Databricks, is that the query fails with this inactivity message long before the Dataset times out at 5 hours.  In this example, the query failed 2 hours before the Power BI Dataset timed out due to inactivity, so Power BI does not know the query failed and continues to wait for results until the refresh times out at the Premium timeout of 5 hours.

Thank you in advance for any information you are able to provide that may help me narrow down how to address this.

[Schizzomarino]

We have had the same problem and tried so many things but none have solved it
we have:

Increased resources on the gateway (we only get this issue when using a gateway) (both RAM up to 64GB and CPUs 8 logical processors)

Affected various settings in the gateway configuration file

[SethParker]

The only solution we have been able to come up with was to create a Notebook in Databricks that uses the Power BI API to check the status of a Refresh.  We schedule it a bit after we expect the Refresh to complete.  If it is still running, we kill the refresh via the API and re-issue the Refresh command.  Essentially, we manually overrode the 5 -hour timeout so that we can set it at whatever interval we want.  There is a bunch of other logic in the notebook to handle different scenarios, but that is the general idea.