Connecting to private Amazon Redshift from PowerBI

Question

Our Amazon Redshift is in private subnet an not open to internet. So we want to connect to Redshift from PowerBI online service.

So we installed powerBI desktop inside AWS VPC and able to connect to redshift as both of them are in same private subnet.

We installed on premise windows gateway inside the same private subnet from which db can be reachable.

Published the redshift pbix file to power bi service, but cannot connect to our redshift cluster.

Even the gateway doesn't show the option for redshift data source.

So my question is, How can we connect to redshift (which is not publicly available) from PowerBI service.

https://community.powerbi.com/t5/Integrations-with-Files-and/Proper-way-of-interacting-with-Redshift-inside-private-subnet-in/m-p/355618

Answer 1

So finally after more research and implementation, here are my findings

1) To connect to private resources from PowerBI service we need to have a gateway inside that private network.

2) There is no data source available for RedShift (as of now) to configure in PowerBI online service, the best data source to use is ODBC. Install redshift ODBC driver on gateway instance. configure ODBC connector via PowerBI desktop upload, configure gateway in PowerBI online and it works.

Answer 2

This is what worked for us:
(Note: as of writing, Microsoft's On-Premises Data Gateways don't support the Redshift data source. You need to use the ODBC data source for scheduled refresh through the Data Gateway.)

1. Deploy a Windows Server EC2 in the same private subnet as the Redshift cluster (and ensure it can talk to your Redshift cluster over your defined port for the cluster).
2. Install the Microsoft On-Premises Data Gateway on the EC2 instance.
3. Install the 64-bit Amazon Redshift ODBC Driver on the EC2 instance. (No need to define a DSN for Redshift, but you can to test connectivity from your EC2.)
4. In Power BI service > Manage Gateways, add a new Data Source to the Gateway. Use the Redshift cluster's ODBC string without credentials (remove the default UID and PWD sections.)
5. Use this exact ODBC string in Power BI Desktop. When adding an ODBC data source in the desktop client, don't use a local DSN or it won't be able to match up with the Data Source defined for the gateway (even in the names match up, the connection strings won't). Instead, select "(None)" under DSN and under "Advanced options" enter the same ODBC string defined for the gateway's data source.
6. Publish the report & dataset to the Power BI Service.
7. If the ODBC strings are the same between the Desktop client's Dataset and the Data Source on the Gateway, you should be able to assign the Dataset to the Data Gateway in AWS.
8. Proceed with configuring Scheduled Refresh for the Dataset.