Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Amazon Redshift refresh failure

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.

 

Need Help

2 Replies

  • ibarrau's avatar
    ibarrau
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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.