PBI PPU Import Dataset from Databricks SQL Warehouse Timeout

[SethParker]

We have an intermittant issue where occasionally a partition in our Power BI PPU Import Dataset times out at 5 hours.  This model is importing data from Databricks via the Databricks SQL Warehouse.  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).  The query failed in this example 2 hours before the refresh timed out at 5 hours.  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.  

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.

It seems there is a disconnect between Power BI and Databricks that is causing Power BI not to receive or grab the rows from Databricks.  The query fails in Databricks, but Power BI doesn't know that it failed and continues to wait for the results until it hits the 5 hour timeout.

I have not found a way to reduce that 5 hours Import refresh timeout.  Is there a way to reduce this timeout?

Does anyone have any information on the handoff between Databricks SQL and Power BI that may help me narrow down how to address this issue.  It does not happen every day, but at least a few times per week.  The model does a full refresh overnight, then runs incrementals during the day.  It's the overnight refresh that occasionally fails.  The incremental refreshes do not seem to hit this issue.  I have tried increasing resources on the Databricks side, but the issue seems to be with Power BI consuming the results, not with Databricks returning rows on the query.

Thank you in advance for any information you can provide!

[GilbertQ]

Hi @SethParker 

Could I ask why you are doing the full refresh every night, if you have got incremental refreshing working that should always get the latest data?

Also there is no way for Power BI to know that the query has failed from databricks.

[SethParker]

Hello Gilbert,

Thank you for your answer.  We could just run the incrementals, but due to the dynamic nature of our partitions, we would have to rerun the full load at least weekly.  This is irrelevant to the underlying issue, though.

Power BI does know when Databricks queries fail for other reasons.  For example, I wrote a view that has a datatype cast issue (as a test).  The dataset refresh returned the following error when it tried to load that table:
Data source error: {"error": {"code": "SSAS_ShortMessage_ProcessingError","pbi.error": {"code": "SSAS_ShortMessage_ProcessingError","parameters": {},"details": [],"exceptionCulprit": 1}}} Table: GL Balances FailTest.

It did not just sit there waiting for results for 5 hours.  But when the Databricks SQL query fails with the "Query timed out due to inactivity" error, it does sit until the 5 hour timeout.