PowerBI Service connect to AWS Redshift Serverless directly and not via On-Prem Gateway on EC2

[icassiem]

Good day,

I have sources from ERP/CRM salesforce/NetSuite directly to powerbi services

I have product data within AWS Redshift and am setting up an EC2 instance to configure the On-Prem Gateway for accessing the AWS Redshift serverless db. This EC2  added more constraint to AWS Env as we scripted pause states as it is only for the PowerBI service redshift refresh

Are there alternative options for Connecting AWS Redshift to the PowerBI service and not using on-prem gateway/EC2 or is it the only option within a PowerBI Service/AWS environment?

Thank You

[vfa]

Hello, I am trying to connect powerbi service to redshift serverless with public access enabled on redshift. All security and newtork configuration are correct, the connection is working for powerbi desktop only when i'm connected to a vpn, but not working for powerbi service even when i'm and when i'm not connected to a vpn. Please help. Do I have to install a gateway on aws EC2? If so could you share a well detailed documentation that can help? Thank you

[amustafa]

On a second thought...you can always set a cron job on AWS to extract summarized data from your Redshift and put it in S3 in parquet or csv format. Microsoft Fabric Lakehouse can now create a shortcut to S3 seamlessly. This will save you time and money. 

You can also try to use the Postgress connection from Power to Redhsift and it works too.

[icassiem]

The "Postgresql or Redshift connection from Power to Redhsift" will work with a VPC endpoint for external access correct?

Anything PBI/Redshift/AWS require when selecting the postgresql/redshift connection source in service/desktop other than the redshift credentials?

connection string is redshift url?

Apologies, not to clued up on AWS either, will be testing this next week

[amustafa · Accepted solution]

Checkout these 2 links for further details:

Integrate Power BI with Amazon Redshift for insights and analytics | AWS Big Data Blog

Solved: Redshift Scheduled Refresh without a Gateway...? - Microsoft Fabric Community

[icassiem]

@amustafa Thank You very much

I will have to wait and see as I have other custom connectors that requires the gateway (Netsuite etc)

[icassiem]

Thank You so much, are you sure? this will be a huge plus for the env architecture & costs

please give me a weak as we configure Redshift Serverless this week.

I came across the below hence the EC2 On-Prem install design

"Due to VPC PowerBI Gateway is needed. Only MS related cloud services does not require On-Prem Gateway" 

and 

amazon web services - AWS EC2 instance -> Microsoft Windows Server -> Run Power BI Smoothly + Cost E...

[amustafa]

In Power BI Service or Desktop you can connet directly to Amazon Redshift without EC2 layer. Why you need EC2 and gateway? If it's a VPC issue then creating a new endpoint for external access to Redshift.

[icassiem]

Please Confirm, as i would like to pause DevOps of configuring an EC2 instance

1. The "Postgresql or Redshift connection from Power to Redhsift" will work with a VPC endpoint for external access correct?

2. Anything PBI/Redshift/AWS require when selecting the postgresql/redshift connection source in service/desktop other than the redshift credentials?

3. Connection string is redshift url? 

Has anyone done this previously, some links please?

The below links, suggest its an security risk for VPC enpoint external and not recommended, i really hope not, Real waste to setup EC2 for gateway only

Please Help, Thank You

[icassiem]

Hi,

In PBI desktop one can connect directly to Redshift, however once published into PBI service for the sources to refresh a gateway needs to connect to within the AWS Redshift env an on-prem gateway is configured on EC2

With AWS not being a microsft an on-prem software needs to be installed on aws and the option is EC2

Please help, can you give more detail or provide examples please?

How would the service source view/connect to an VPS AWS Redshift?

[icassiem]

Hi,

My other connections such as API using power query or PowerBI REST API Custom Connectors

require an On-Prem Gateway?

That would mean i need the EC2?

Please Advise