PowerBI Premium Query Timeout

[tctrout]

Hello,

I am receiving the following error for a single visual published to the Power BI Premium Service:
The XML for Analysis request timed out before it was completed. Timeout value: 225 sec.

I would like to better understand what is causing this error as the Premium Capacity Workload setting for Datasets Query Timeout is set to 3600 (seconds) [the default setting]

What is also odd, the PBIX desktop renders the visual within 30 seconds.  This made me focus on the Premium Capacity Workload setting for Datasets Query Memory Limit %, though the default setting is set to 0 which is 6GB for our P1 node.  

Additionally, this solution has been published for several months and just became an issue last week.  There have been no updates made to the solution and in the Capacity Metrics App along with Report Usage Report (Performance) I can see a spike in total memory consumed starting last week.  The datasources have not been changed either.

I went ahead and increased both of those settings outlined above(throwing horsepower at the issue), and the visual still will not render, displaying the same error with the same timeout value of 225 sec.

I know the solution needs performance tuned, however I would like to better understand why changing those settings did not make a difference.

Any help will be appreciated.

[jmlove0923]

We had this problem starting yesterday with one report (and never before and we haven't changed anything.)   When we publish it to a non premium capacity workspace it works fine...  Once we got rid of the filters that were in essence just hiding blank rows it was fine on the premium service.   However since this just started yesterday AND the other things that started yesterday too (Custom visuals dissappearing from the app) my hunch is it's related to a group of servers that host the app and premium capacities - perhaps a load balancer or something.   Have you tried publishing it to a non premium service and see whether it works?

[tctrout]

I am not able to test on a non premium capacity as this is not permissible within our organization.

Same scenario though, it stopped working with no changes made to the file where it had been working for several months.

[jmlove0923 · Accepted solution]

I put in a ticket but they're going down the "you don't have enough capacity even though we have a P2" path.   So for now we are keeping the filters removed and hoping the fix for the custom visual situation fixes this too.   We're also going to upgrade to the new Premium workspace architecture to see if perhaps it's in relation to that (IE - the stuff they're rolling out to the service now only works properly in the new architecture and they don't realize it's harming workspaces in the old architecture.)  Did you check your filters to see if there are ones that might be able to be removed or retooled that will fix this for you?   

[tctrout]

I never found an answer to this, but I suspect what you are outlining.  We too have a P2 node so increasing the capacity settings should have been more than enough to resolve this timing / memory issue.

We optimized the DAX and it resolved the issue.

[collinq]

Hi @tctrout ,

I know that you mentioned Premium, and the following thread is not Premium (that I saw) but this might be helpful in your thinking process:

The XML for Analysis request timed out before it w... - Microsoft Power BI Community

Also, you might want to try the Performance Analyzer in desktop just to see what might be the issue in the Service:  Use Performance Analyzer to examine report element performance in Power BI Desktop - Power BI | Micr...

And, perhaps take a look at this blog by Chris Webb:

Chris Webb's BI Blog: The “Visual Has Exceeded The Available Resources” Error In Power BI Chris Webb...

[tctrout]

Hello, thank you for the response.

I have read all the reference articles.

1.  The first one focuses on Refreshing the data; my data model refreshes without error in under a minute, its a small .txt /.csv /.xlsx sources.  

2.  I leveraged the performance analyzer within the PBIX desktop and saw that the DAX queries could be optimized.  However, locally within the PBIX the visual DAX Query will render in 30 seconds.  When its published to the service, the visual will error after 225 seconds with the above error.

3.  Chris Web's blog provided me the direction to post this specific question within the community.  I have tried changing those setting outlined in his post but they did not make a difference.  The odd thing is that it still errors out, stating a time out of 225 seconds where I know that I changed the settings to use more memory (50% of capacity) and increased time out to 7200 seconds.  

It doesnt appear that any changes I make to our capacity will render that visual where at the beginning of last week it rendered fine and at the end it cannot render.  

[collinq]

Hey @tctrout ,

I also had a similar problem once, but, like you I increased my timeout and the world was a magic place after that.  So, my experience went fine with the change.  I am thinking this would be worth a ticket to Microsoft since they have the back-end service logs and can perhaps point you in the right direction.  And, if they do, please let us know in this ticket the result so we can all learn!