Ensuring your Power BI Incremental Refresh does not Timeout when using a SQL Server Source
I recently was updating my PBIX files to use the Incremental refresh. I ran into a timeout error as shown below.
This was because on the first refresh it has to process all the data before it can incrementally refresh the dataset.
As per the documentation the default timeout for a SQL Server database is set to 10 minutes, and when I am processing a lot of data it can easily take longer than 10 minutes to return all the data.
To allow the dataset to run for longer, as per the documentation above I can specify the CommandTimeout optional parameter.
The CommandTimeout will allow me to specify how long to wait for the query to complete.
Below is the syntax on how to add this to my source (It did take me a while to get it right on how to get it working correctly).
- I clicked on Transform Data and when to my data source which is using Sql.Database
I then clicked on the Settings button which opened up the SQL Server database Window.
- I then put in the command timeout in minutes to 90 minutes as shown below.
- This is what the syntax looks like in the M Query
Sql.Database("DatabaseServer", "DatabaseName", [CommandTimeout=#duration(0, 1, 30, 0), CreateNavigationProperties=false])
- I then closed and applied my settings.
This then allowed my incremental refresh to refresh the entire dataset the first time it ran, where it had to query all the data into the data model.
Thanks for reading and if you got any questions or suggestions please let me know in the comments section below.