Amazon Redshift refresh failure

[Anonymous]

Hi all,

We are facing below error with schedule refresh in Power BI service which working fine in Power BI desktop refresh.

ODBC: ERROR [08S01] [Microsoft][Amazon Redshift] (50) The connection has timed out, please reconnect. Current connection status is: [SQLState 08S01] CONNECTION_BAD

Suggestion would be appriciated. Thanks in advance.

[ibarrau]

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,

[Anonymous]

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.