Online refresh not working with Amazon Redshift views

[ncbshiva]

Hi Team,

I have connected to some Amazon Redshift view with 1.4 Million records. After publishing to the Power BI service , when i click on the Refresh button , it starts the process and throws below error after sometime.

ODBC: ERROR [08S01] [Microsoft][Amazon Redshift] (10) Error occurred while trying to connect: [SQLState 08S01] could not connect to server: Connection timed out (0x0000274C/10060) Is the server running on host "xxxxxxx.cfqdgyyjdyi9.us-west-2.redshift.amazonaws.com" (x.xx.xx.xx.x) and accepting TCP/IP connections on port 5439? Table: xxxxxxxx.
Cluster URI: WABI-NORTH-EUROPE-redirect.analysis.windows.net
Activity ID: 45825011-5e84-4719-9155-204c6b9c81dd
Request ID: 9518282d-0b7e-4081-eba5-4db6c495ab91
Time: 2018-12-13 09:04:45Z

Can anyone please tell me how to resolve this refresh issue in Power BI service ?

And where is the problem occuring is it in Power BI service or Amazon Redshift ?

Regards,

[v-juanli-msft]

Hi @ncbshiva

First please check if you could refresh succefully in Power BI Desktop.

Then Go to Dataset->Setting->Refresh setting, Configuring Credentials and Refresh Settings, 

Reference here:

https://powerbi.microsoft.com/en-us/blog/building-power-bi-reports-on-top-of-amazon-redshift-data/

If these above doesn't help, 

According to your error message, I'd like to suggest you to check your amazon redshift rules to allow power bi service and gateway request can connect to that datasource. 

Microsoft Azure Datacenter IP Ranges

Creating and Configuring a Web Access Control List (Web ACL)

Besides those, please check outbound ports list that On-premises Gateway supports.

Best Regards

Maggie

[ncbshiva]

HI @v-juanli-msft,

Thanks for the reply . I am able to refresh the same in the Power BI Desktop. And the credentials have been configured as required.

The refresh seems in Power BI service seems to work when the number of records is less in my imported views.

But when the number of records are more like 1.4 billion , i always get this "Timed out exception" error message.

I am not using any gateway to refresh.

Regards.