Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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.

 

2 REPLIES 2
ibarrau
Super User
Super User

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,

 


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Anonymous
Not applicable

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. Captureerror2.JPG

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors