Cannot refresh dataset in service

[Michael_Meissne]

Hi All,

I imported data from a redshift datasource into powerbi (pbix file roughly 60MB). I published this dataset in ourr workshace. Now I would like to schedule a refresh of this dataset online in the service and it gives me the following. Credentials work and I have included the IP adress in AWS so that it can connect to redshift.

Refreshing the dataset in powerbi desktop works.

thank you very much for your help.

Fehler bei der Datenquelle: DataSource.Error: <ccon>ODBC: ERROR [08S01] [Microsoft][Amazon Redshift] (10) Error occurred while trying to connect: [SQLState 08S01] timeout expired . DataSourceKind = AmazonRedshift. DataSourcePath = datawarehouse-controlling.cq6qfydzo4k7.eu-central-1.redshift.amazonaws.com;datawarehouse-master. OdbcErrors = #table({"SQLState", "NativeError", "Message"}, {}). </ccon>. The exception was raised by the IDbCommand interface. Table: db_marketing_dailysales.
Cluster-URI: WABI-GERMANY-WEST-CENTRAL-PRIMARY-redirect.analysis.windows.net
Aktivitäts-ID: 285100ae-9305-4d69-8f3e-4a851066c628
Anforderungs-ID: c7a4adcf-f0d0-7c00-dc58-72944300443b
Zeit: 2023-05-08 09:11:39Z

[v-yueyunzh-msft · Accepted solution]

Hi , @Michael_Meissne 

Based on my research and investigation, this error message indicates that the refresh operation failed due to a timeout error while trying to connect to the Amazon Redshift data source. This could be caused by a variety of factors, such as network connectivity issues, database server performance issues, or incorrect credentials.

Here are some steps you can try to resolve the issue:

1. Check your network connectivity to the Amazon Redshift data source. Ensure that the IP address of the data source is included in the AWS security group and that the firewall is not blocking the connection.
2. Verify that the credentials used to connect to the data source are correct and have sufficient permissions to access the data.
3. Check the performance of the Amazon Redshift database server. If the server is under heavy load, it may not be able to respond to requests in a timely manner.
4. Increase the timeout value for the connection. Based on my research, currently there's no option to do this in the Power query connection side, you may try it by adjusting the timeout settings in the Amazon Redshift console. Or you can try to first use "SQL Statement" in the Power query connection string to reduce the data amount that you query returns in order to make sure the dataset refresh is not timed out due to the too large executing time for one single query:

Power Query Amazon Redshift connector - Power Query | Microsoft Learn

Try refreshing the dataset again after a few minutes. Sometimes, transient network issues can cause connection timeouts, and retrying the operation may resolve the issue.

If none of these steps resolve the issue, you may need to contact the provider of the Amazon Redshift data source to check the corresponding backend log for data source side for the further troubleshooting.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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