Solved: Re: Connecting Power BI Service to Amazon RDS MySQ...

x1ejm · ‎09-04-2020

Hello. I have created a report in Power BI Desktop that connects to an Amazon RDS MySQL data source. This of course required me to first install the MySQL connector, which was completed with no dramas. I then published the PBIX file to the Power BI Service with the aim of sharing the dashboard with my colleagues. Although I am able to refresh the data locally in Power BI Desktop and re-publish the updated data set to the Power BI Service, I want to automate this process - however the Scheduled Refresh option is greyed out. I understand the concept of installing a gateway to act as a go-between, but don't want to do this because I don't a server sitting in the corner of my office. I can in the Power BI Service admin console that scheduled refresh is available for Azure data sources (like SQL Azure) so why is it not possible to connect to Amazon RDS in the same way? After all, my PBIX file has all the credential information embedded into it in order to gain access to Amazon RDS. Could it be the fact that right at the start I needed to install a MySQL connector into Power BI Desktop to make things work - and that no such equivalent exists in the Power BI Service? I can think of a couple workarounds to this - a) using a generic ODBC connector available on the Power BI Service (is there one?) instead of the MySQL connector or b) signing up to an intermediary service that acts as the gateway. Any practical advice would be greatly appreciated. Thanks Ed.

amitchandak · ‎09-04-2020

@x1ejm , Please check I think amazon RDS might require gateway.

Gateway
https://radacad.com/the-power-bi-gateway-all-you-need-to-know
https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem
https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem-tshoot

Troubleshoot gateway
https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem-tshoot
https://guyinacube.com/2016/09/21/troubleshooting-connectivity-tips/
https://www.youtube.com/watch?v=mGnNn6gnrKk

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

x1ejm · ‎09-08-2020

Dear all. I took the plunge and decided to install the Gateway on an Azure VM, here's what I did...

1. Opened my Azure account.

2. Created the smallest Windows VM I could get away with (this turned out to be 4MB RAM).

3. Downloaded + installed the Gateway onto the VM.

4. Downloaded + installed the MySQL connector onto the VM.

5. Ran the Gateway + logged in used my Power BI account credentials.

6. Tested the Gateway using the test feature + it reported all was well.

7. Made a note of the IP address of my VM.

8. Added my VM's IP address to the whitelist on my DB server.

9. Went to the Power BI Service - chose my Power BI dataset + then chose settings.

10. My new gateway was visible (logging in on the VM made the connection it appears).

11. Setup the new connection inside the Power BI gateway config - using the same credentials as I did in Power BI desktop.

12. Tested the connection to make sure it ran.

13. Created my refresh schedule.

14. Saved everything + manually pushed the data set refresh button to make sure the end-to-end data refresh happened.

Hope this helps everyone!