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.
This is great – thanks Gil – I had no idea. Have you experienced an error message when trying to publish a report after configuring the incremental refresh on a Pro license? I went through the steps to configure it and go to publish and it throws and error “You can only publish datasets with incremental refresh to premium workspace.”
Hi there
If you could make sure that you have downloaded and installed the latest version of Power BI Desktop. The previous versions will give you the error you are receiving.
[…] Gilbert Quevauvilliers shows how to set the default timeout for a query against SQL Server from Powe…: […]
Yes, that was indeed the issue. I thought I had installed the Feb version, but apparently i was mistaken. Now i am getting another error where the service states “Parameters have not been defined for this dataset yet…” However, I have followed the instructions for using the StartDate and EndDate for setting up the parameters. Anyways, thanks for the help!
Hi there
That is great to know you got the Feb Version installed.
If you can please make sure that your parameters are named RangeStart and RangeEnd and that they are set to DateTime
Thanks for the reply. Yes indeed they are both set to DateTime and named RangeStart and RangeEnd.
Is the Data itself also in a datetime format?
Hi there
Yes the data type being returned needs to be of the data type DateTime
Hi, Gil – I have a Power BI Desktop file connecting to an IBM Db2 database and it is retrieving 8.9 million rows. I have incremental refresh configured. In the Power BI service, I get a timeout error after about 2 hours, even though I have the optional Command Timeout in minutes set for 1200. Any guesses as to why this initial refresh in the service won’t complete? Previous to the November 2020 Power BI update, I was able to connect with OLE DB or ODBC however, in the November 2020 version I just see the little spinner spinning and it never starts to show the rows being returned.
Hi Jeff, thanks for the comment.
What about if you had to change the partitions to be monthly?
This might allow for the query to run per month and then fit into that 2 hour window?
Here is a link to my blog post I did: How I changed the partition configuration (Year, Month, Quarter, Days) for my data in Power BI Premium / Power BI Premium Per user!
Let me know if you got any other questions.