PowerBi automatic update with AWS Datasources

0

Hello everyone, In my company we have a powerbi file that is daily updated manually (using the refresh button), this file is connected using odbc to a couple datasources, both are in AWS, one is a linux server running PostgreSQL and the other one is a RDS. After the refresh I upload the new data and, in some cases, the new pages of the report into the PowerBi Service where all other employees can see the reports.

Now, the case is that it takes a lot of time to do the refresh in my powerbi desktop file and sometimes I am not physically able to do it so I was wondering how can I link the datasources directly with AWS, similarly with this diagram (https://docs.aws.amazon.com/whitepapers/latest/using-power-bi-with-aws-cloud/connecting-the-microsoft-power-bi-service-to-aws-data-sources.html), we do not know how to properly connect the datasource, we installed a gateway on a VM inside the VPC but we are not able to connect it to my powerbi desktop file.

How we should have done it with the gateway? Are we forced to use Amazon Athena to update the PBI Service without supervision?

Thank you

1 Answer
1
Accepted Answer

Hi,

I understand you want to connect Microsoft Power BI directly to data sources in AWS VPC. According to the documentation you've mentioned [1], this is possible and it requires following components and configurations:

  1. An Amazon EC2 instance which has Microsoft on-premises data gateway in the same private VPC with your RDS and PostgreSQL server
  2. The private VPC subnet must have a route to a NAT gateway [2][3]
  3. The security group associated with your RDS and PostgreSQL server must allow the traffic from the EC2 instance that launched in step#1

Amazon Athena is another supported data source by the Microsoft Power BI gateway, and it is not required for such an use case.

I'd suggest you to kindly double check the VPC network and security groups configuration according to the above-mentioned steps. If the issue still persists, please don't hesitate to cut a technical support ticket from your AWS account with network or RDS topic, our support team will be happy to help you further troubleshoot and resolve the issue!

[1] https://docs.aws.amazon.com/whitepapers/latest/using-power-bi-with-aws-cloud/connecting-the-microsoft-power-bi-service-to-aws-data-sources.html [2] https://docs.aws.amazon.com/vpc/latest/userguide/nat-gateway-scenarios.html#public-nat-internet-access [3] https://aws.amazon.com/premiumsupport/knowledge-center/nat-gateway-vpc-private-subne

AWS
Ethan_H
answered a year ago
  • Thanks Ethan_H, as you said I tried again the installation of the gateway and the EC2 instance and I noticed that there was some problems in the configuration of the ODBC. The EC2 had all the required settings such as same VPC, NAT routes, DB access, ODBC configs. But the issue was that the name of the ODBC driver was different from the one that I use in my computer. I did what they say in this reference 1 to match the driver name, I checked that the version and the type were the same and after that I corrected the name. After that I restarted the gateway on the EC2 then suddenly it worked. Now I can succesfully refresh the data on the PowerBi Service using the AWS gateway.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions