XML for Analysis Timeout

[AdmVarr]

Apologies if this is covered elsewhere however I can't find any resources with the same issues I am facing.

I am getting the error: The XML for Analysis request timed out before it was completed. Timeout value: 225 sec.
This is on a 59kb Direct Query file that returns a table with ~50rows with no PQ steps other than to remove some unneccssary columns. It is set to page refresh every 2 seconds and has been running without issues in service for months. It still runs without issue in desktop but fails in service. Running a P1 capacity which only has about 60% of capacity in use at any given time. There is some DAX in use to handle some formatting and produce a few averages but is very light and optimised. 

I also have identical reports set up to run off 2 other regional production servers which are runing without issues which leads me to believe it must be a problem on the SQL server side. Was after some advice on if there is anything I can check to trouble shoot this?

[AdmVarr]

Hi @lbendlin .
Thank you for the reply. I have just done this and run the query through SSMS and it run in less than 1 second (returned 00:00:00) to retrieve the data. 

Simarly, have run the DAX through DAX Studio and the Server timings are 67ms on the storage engine and 150ms on the formula engine.

I'm confused by the way it works in Power BI desktop and not on service, and even more so that it works on 2 out of our 3 production servers on service without an issue. I have asked my IT team to run some diagnostics on the server to elimate anything on that end but feel i'd have the same issue in desktop if that was the case?

[lbendlin]

Testing this on the desktop has indeed limited value.

Can you reliably reproduce it on the service or is it a "transient"  issue? Is a gateway involved?

[AdmVarr]

Thanks again for you time @lbendlin 

I have tried publishing to a few different premium workspaces and get the same issue but guess it could be transient, unsure how else to test this. Unfortuantely high priority as is displayed in our contact centre wallboard showing Overdue activities. There is indeed a gateway involved, but the same gateway being used for all other reporting (including the other 2 working versions of the report). We did upgrade to the latest gateway version ~1 week ago but was working all of last week, only went down today so would be surpised if it was this. I guess we could roll back to test this but feels like this should be a last resort?  

[lbendlin]

How many gateway cluster members? Transient issues are sometimes caused by one out of x cluster members being misconfigured.

If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .

[AdmVarr]

We only have 2. One was offline which was resolved last week and led us to realising there was an update available, so was working for 4 days since updating and changing any gateway settings. 

I'm going to rebuild the report from the ground up tomorrow and see if it makes any difference

[AdmVarr · Accepted solution]

I completely rebuilt the report from scratch and it now works fine. It's identical in every way so although I haven't got to the bottom of the issue, I will mark this as solved.

Really do appreciate the time you spent @lbendlin 

[lbendlin]

Use Performance analyzer to capture the query (the Direct Query version) and then run that query through SSMS and evaluate the Actual Execution Plan.  Modify or add indexes accordingly. Make sure to update statistics too.