Incremental refresh - 10 min timeout

[Gadik]

Hello,

We are having Premium capacity workspace, with a model defined with incremental refresh. The data source is Oracle.

Frequently we see that the refresh is failed, 10 min after it was started!

After some investigation, i can guess the reason. While the timeout for fetching all the data (say daily partition refresh) is 5 hours and we are not hitting this limitation, there is one pre-fetching select that Power BI is firing, to get the latest updated time, similar to following:
"select max(""rows"".""STATS_DATE"") as ""STATS_DATE""
from
(
select ""_"".""STATS_DATE""
from FACT_TABLE
where ""_"".""STATS_DATE_ID"" >= TO_TIMESTAMP('2022-11-22 00:00:00','YYYY-MM-DD HH24:MI:SS.FF') and ""_"".""STATS_DATE_ID"" < TO_TIMESTAMP('2022-11-23 00:00:00','YYYY-MM-DD HH24:MI:SS.FF')
) ""rows"""

The table is relatively large, the fields are not indexed (DBAs refuse to do it for their reasons) and after 600 sec the query is aborted by Power BI, because we see the error "ORA-01013: user requested cancel of current operation"}},


It is clear that the db architecture is not optimal, but assuming it is a "black box" - how can we change the timeout of 10 min or replace the query that is used for incremental refreshes?

Thank you.

[v-xiaosun-msft]

Hi @Gadik ,

The default timeout for a SQL Server database is set to 10 minutes.

Try to turn to Transform Data -> Data source settings. And then set the timeout minutes.

Or you can try to edit it in Power Query.

Sql.Database("DatabaseServer", "DatabaseName", [CommandTimeout=#duration(0, 1, 30, 0), CreateNavigationProperties=false])

Please follow the following document.

Ensuring your Power BI Incremental Refresh does not Timeout when using a SQL Server Source - FourMoo...

Troubleshoot incremental refresh and real-time data in Power BI - Power BI | Microsoft Learn

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Gadik]

Thank you for your reply, but it is an Oracle database. And some other queries are running for longer periods and not failing.