Refresh AWS RDS database from Power BI Web

Question

I created a report in Power BI Desktop, connected to an AWS RDS database, and published it to the Power BI Web App, with an intent to refresh the dataset from the web app.

I tried doing so with both MySQL as well as SQL Server (on RDS). However, Power BI web does not let me refresh the dataset and instead wants me to install an on-premise Gateway.

I am not sure why this is a requirement as my database is on the cloud and not on-premise, and on a public VPC.

Is it possible to refresh an AWS RDS dataset in the Power BI web app? How?

Answer 1

To refresh the Power Bi Visuals on the Web Application, it needs to have a successful connection with the Database. Now, in the case of connecting to an AWS RDS instance, since the AWS database server is installed on an AWS Virtual Machine, this acts as an on-premise source on that machine [6]. Therefore you would have to install the on-premises gateway on the AWS server with an access to the RDS possibly on the same VPC or with the help of best security practices.

The steps to create an on-premise data gateway an AWS:

1. Create an EC2 Windows instance, please see this AWS documentation [3] for more details. Refer to this public Microsoft link [4] for details around the instance(s) based on the operating system requirements. Following are the instance configurations that have worked for me and can be different based on your requirements: a. AMI Name: Windows_Server-2019-English-Full-Base-2021.10.13 b. Instance Type: t2.2xlarge

2. Please check the required network ports you will need to allow in your instance security group inbound and outbound rules. Please see this AWS documentation [5] to know how to work with security groups.

3. Ensure that you add a “Key Pair” to the instance and have access to the PEM file. This will be needed to RDP into the machine.

4. Once the instance is created, RDP into it and install any required software.
   For instance in my case to connect to an AWS Postgres Server, I had to install the Npgsql version 4.0.9. The latest version unfortunately didn’t work so be mindful of version compatibility. Also though not essential, I wanted to install the Chrome browser on the Remote Server as a preferred choice of my browser. To do so I had to run the following command on the PowerShell CLI

   $Path = $env:TEMP; $Installer = "chrome_installer.exe"; Invoke-WebRequest "http://dl.google.com/chrome/install/375.126/chrome_installer.exe" -OutFile $Path$Installer; Start-Process -FilePath $Path$Installer -Args "/silent /install" -Verb RunAs -Wait; Remove-Item $Path$Installer

5. With the Remote Desktop open, install the Power BI on-Premises Gateway [4].

6. Now search for the “On-Premises Data Gateway” on the Remote Server and register your account. Please use the account that is connected to your PowerBi Web App. This could be either the role based email to which you and the Power Bi has access to … or your own email address associated with the PowerBi Web App.

7. For details on creating a data source, please see the reference link [6] for more details.

Data Refresh Scheduling

8. Now back on the Power Bi Web app, when you click on your settings button > manage gateways, on the left hand side you should be able to see the gateway showing up. Add the credentials and test if the connection is working. If you get a green tick, you should be able to schedule the Data Refresh on the web.
9. You can configure PowerBi to refresh data. Please see the reference link [7] for more details.

Troubleshooting

Pay attention to the error that is given when you try to connect to the database on the web. It would usually have a useful hint of what might be missing.

Reference links:

[3] Launch an instance using the Launch Instance Wizard https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/launching-instance.html

[4] Install PowerBi gateway - https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-install

[5] Work with security groups - https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/working-with-security-groups.html

[6] Connecting the Microsoft Power BI service to AWS data sources - https://docs.aws.amazon.com/en_us/whitepapers/latest/using-power-bi-with-aws-cloud/connecting-the-microsoft-power-bi-service-to-aws-data-sources.html

[7] Data refresh in Power BI - https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-data

[8] https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem

[9] credits / author myself: https://www.smart5.co.uk/en_gb/article/25/on-premises-data-gateway-installation-aws-power-bi