Direct query fails and shows time out error in the visuals of the dashboard

[kanga99]

Direct query fails and shows time out error in the visuals of the dashboard. This can be had to the loss of connection or something turning off the gateway. Any way, we solved this restarting the service of the gateway. As this is recurrent, we created a scheduled task to restart the gateway automatically every night. Now we have more demand of dashboards with direct query connection and more data is displayed in real time. So the issue reappeared one or two times a week despite the gateway is being restarted at night, we need to restart the gateway every fail in real time querys ussing direct query. We are working with Power BI Premium Per User and One premises data gateway. ¿Can someone help us to understand what happens and how we can solve the issue?

Error text:

More details: The XML for Analysis request timed out before it was completed. Timeout value: 225 sec.
Activity ID: 787dd149-039d-42fa-bd7e-a773d99dd2ee
Correlation ID: d204514d-c316-597f-322d-7545bcd0116f
Request ID: 6dcd583b-32e8-43fc-bd28-181a114901a0
Time: Tue Jan 24 2023 19:12:07 GMT-0600 (hora estándar central)
Service version: 13.0.19866.57
Client version: 2301.2.11954-train
Cluster URI: https://wabi-south-central-us-redirect.analysis.windows.net/

[v-xiaoyan-msft]

Hi @kanga99 ,

As stated in this blog, refreshing the dataset will give an error message indicating you that your dataset is too large when data model size grows beyond 1 GB.(yours is around 1.2GB)

I would recommend you

1. Make sure your on-premise gateway is the lateset version.

2.Use Power BI Desktop to check the refresh duration.If it takes more than 2 hours, consider moving your dataset to Power BI Premium. Your dataset might not be refreshable on shared capacity. Also consider using Incremental refresh for datasets that are larger than 1GB or take several hours to refresh.

3. Set the Time out parameter on the connection. Try setting this to something larger than 3600, like 7200 for 2 hours or even 36000 for 10 hours. Default number if you put nothing = 10min

4. Break up your dataset into smaller datasets and refresh them in Power BI Service .

Refer to:

Data refresh in Power BI - Power BI | Microsoft Learn

Troubleshoot refresh scenarios - Power BI | Microsoft Learn

Solved: Timeout value in powerbi service - Microsoft Power BI Community

Solved: data refresh timeout - works sometimes, fails othe... - Microsoft Power BI Community

Best regards.
Community Support Team_ Caitlyn

[kanga99]

Thanks @v-xiaoyan-msft 

The gateway version is 3000.154.3 (December 2022) and does not appear a new available version.

I already am using Power BI Premium. The data that is imported is refreshed once a day and it takes less than 30 minutes to complete. This is working correctly.

The issue is with the real time data that is refreshed with direct query. How can I set the time out parameter for direct query connections?