Forum Discussion
Helper IRefresh Cloud hosted MySQL Database
Hi Agreenwood,
If I understand your scenario correctly that you connect the data from My SQL database which hosted on AWS RDS and you connect it with Amazon Redshift in Power BI Desktop?
If it is, I'm afraid that you may require a gateway to refresh the data.
Here is a similar thread you could refer to.
Best Regards,
Cherry
Resident RockstarHi Agreenwood,
If I understand your scenario correctly that you connect the data from My SQL database which hosted on AWS RDS and you connect it with Amazon Redshift in Power BI Desktop?
If it is, I'm afraid that you may require a gateway to refresh the data.
Here is a similar thread you could refer to.
Best Regards,
Cherry
- Agreenwood
Hi,
That's correct - though the service is Aurora rather than RedShift.
I'm in a postition where I have a full serverless solution setup, though now I need to setup a server simply to refresh data from our cloud database to Power BI. This does seem very odd... is there an alternative way of facilitating a data refresh rather than setting up and maintaining a whole server? For example, perhaps this could be triggered through an API refresh? Or is there a third party application which may do the trick?
Agreenwood have you had any success?
I am in a similar situation as you. I have a fully serverless setup with data source being MariaDB on AWS, but cannot refresh the dataset without a Gateway. It is confusing and strange to say the least.
- Agreenwood
ben_kog Hi Ben,
I'm afraid to tell you that your solution is doomed! I can confirm that you require the Power BI gateway setup on a Windows PC / Server to be able to finish the solution you are after.
My solution, which was built serverless with the intention of handling potentially hundreds of clients had to be redesigned. I intended on each client to have their own database, and then the reporting via Power BI would be pulled with individual connection strings. This would be doable in Tableau and other BI solutions but no so easily in Power BI.
My solution in the end:
Create a Data Warehouse which sits above all the individual databases. EC2 solution with Power BI Gateway installed, connecting to the Data Warehouse. Power BI to the Data Warehouse with Row Level Security (RLS).
It was a pain, though it resolved all of my issues.
Your options for the EC2 solution:
1) If your dataset is small, and doesn't require much power a T2 Nano Server could be powerful enough
2) If you need something more powerful and have small budgets, I have some Lambda scripts which turn a T2 Large Server on shortly before a Power BI scheduled refresh is due to happen, and then switches it off again after the scheduled refresh should have finished
3) Have an EC2 server switched on all the time
So basically, I did resolve all of my problems so I can continue to use Power BI for multiple clients, but (1) it was a real pain to setup a Data Warehouse, (2) it still didn't ensure my end solution was serverless, and (3) it cost $$$.
Hope this helps. If you want some Lambda scripts for switching EC2's on and off let me know.
