Forum Discussion
Amazon Redshift refresh failure
Hi. How much is the refresh taking? remember that Power Bi has a two hour window to refresh the data. If it is taking less than 2 hours to throw the timeout let's try something.
There is no documentation about the "options" parameters for redshift, but it says you can add options parameters.
https://www.powerquery.io/accessing-data/amazon-redshift/amazonredshift.database
Let's try adding a commandTimeout like in SQL Server
https://docs.microsoft.com/en-us/powerquery-m/sql-database
The idea would be getting in Power Bi Desktop, Transform Data, Source step. Check that Power query code and add a parameter before the last ")" like
AmazonRedshift.Database(ServerName, DatabaseName, [CommandTimeout=#duration(0, 1, 0, 0)])
That will use 1 hour window to resolve the query. By default power query set up at 10 or 15 minutes.
Hope that helps,
Unfortunately, since about Nov 2019 this is no longer an option. Redshift connector is no longer supporting this query. I had to remove it from my dataset. Since then Batch size was the only option. Batch size is also limited to 1000. So, when having a long query of 1M+ will hit the overall Power BI refresh timeout limit of 120min. Looks like MS has been doing some behind the scene changes to the connector which affects the users.