'Run a query against a dataset' step timeout outside business hours with active Power BI Connector

[JoshKohlhase]

Background

I have a scheduled cloud flow in Power Automate, scheduled to run every 30 minutes every day, that uses the 'Run a query against a dataset' step with the Power BI REST API and a Power BI connector.

The Power BI report and dataset being queried is published in a shared Power BI service workspace and uses a DirectQuery connection to an SQL Server database with an on-premise data gateway. The Power BI connector has a status of 'Connected' using my account with a Power BI Pro license.

The step and flow both run successfully when ran manually or automatically during business hours on any day.

Issue

Very consistently, the 'Run a query against a dataset' step fails every single time between the hours of 6:30pm and 11:30pm CST and again between 1:30am and 6:00am CST. Outside of these hours, the step and cloud flow run successfully.

While this step typically takes ~10s to run when successful, outside of business hours this step will take 2 minutes to run and then return the following error, retrying the step two more times before ultimately failing:

BadRequeset. Http request failed: the server did not respond within the timeout limit. Please see logic app limits at https://aka.ms/logic-apps-limits-and-config#http-limits.

Testing

At 7:30pm CST one day, I directly confirmed that the step was failing in Power Automate. The scheduled runs of the Power Automate cloud flow since 6:30pm that evening had all failed with the same step error, and both a manual run and manual test also failed with the same step error.

However, the Power BI connector in the Power Automate flow had a status of 'Connected', the Power BI report in the Power BI service loaded successfully when opening and refreshing the report, and the actual query ran successfully when manually entered in SQL server.

I confirmed there is no downtime in the SQL Server or on-premise gateway, or refresh schedule in the Power BI service, that would cause this data to be unavailable during these hours. I also confirmed that our SQL database was not receiving these queries from the scheduled flow when the step failed.

Question

It seems that the timeout is specifically happening between the Power BI flow step being able to query the Power BI service dataset, even though the connector was shown as 'Connected' and the dataset worked fine in the Power BI service.

Does anyone know what could cause the 'Run a query against a dataset' step in Power Automate to timeout outside of business hours, given these details?

[lbendlin]

When this fails in Power Automate, and you then attempt the same refresh in the Power BI Service, will it fail too or will it succeed there?

[JoshKohlhase]

The step is querying the Power BI dataset, not refreshing it.

When the step fails in Power Automate, I've confirmed that the DirectQuery Power BI report built on the dataset is still able to open and refresh with new data, which suggests the actual Power BI dataset is working fine.

[lbendlin]

Shared or Premium capacity?  Which cluster?

[JoshKohlhase]

The Power BI report is hosted in a shared Pro workspace, not Premium.

Can you clarify what you mean by "which cluster" and where I can find that information?

[lbendlin]

Cluster is the physical location of your data, like "Europe" or "US West 2"

If you are on a Pro shared workspace then there is no performance guarantee.

[JoshKohlhase]

The data is on a local reporting server. It's accessed by the Power BI service using an on-premise data gateway in North Central US.

What's confusing is that the Power BI report itself works fine during these hours when opened manually. It's specifically the external request of the Power Automate flow querying the Power BI report that is failing. Unfortunately, due to the 2-minute timeout limit, I can't confirm if Power Automate is totally unable to query the report or that the request just takes longer than 2 minutes - however, the Power BI connector does have a 'connected' status at these times.

So it seems that this is just an un-documented limitation of either Power Automate or Power BI that Power Automate is either unable to send a query or receive a response from a Power BI dataset hosted in a non-premium workspace between 6:30pm -11:30pm and 1:30am - 6:00am CST.

I'd like to confirm if this is truly a limitation of a non-premium Power BI workspace or if it's a limitation of Power Automate to determine what configuration change is needed or possible to make this work.

[lbendlin]

Will be tough to get anyone's attention on this.  Reliable support is limited to Pro licenses.

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 .