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?
