Forum Discussion
How to Set Up Scheduled Refresh for MySQL (ODBC) in Power BI Service?
Issue Description
I have installed MySQL Connector ODBC 8.0.33 (winx64) on my machine and connected to a remote MySQL database in Power BI Desktop using the ODBC data source. I successfully imported the data and created a report.
In Power BI Desktop, when I manually refresh, the data updates as expected based on the latest data in the MySQL database.
However, after publishing the report to Power BI Service, I need to set up a scheduled refresh. Here are my questions:
Questions:
Do I need to set up an on-premises data gateway for scheduled refresh in Power BI Service?
Under the "Data Connections" section in Power BI Service, what configurations need to be set?
Where do I need to provide the database credentials for the refresh to work properly?
Current Setup:
Power BI Desktop: Connected via ODBC to MySQL
Power BI Service: Published report and dataset
MySQL Database: Hosted remotely
MySQL Connector: Installed MySQL Connector ODBC 8.0.33 (winx64)
Expected Outcome:
I want my Power BI report to refresh on a scheduled basis in Power BI Service without requiring manual refresh in Power BI Desktop.
Any guidance on setting up the gateway (if needed), credentials, and scheduled refresh settings would be greatly appreciated!
Thanks in advance for your help!
Hi Anonymous , Thank you for reaching out to the Microsoft Community Forum.
Install and configure the on-premises data gateway in standard mode on the machine where your ODBC setup is active. Next, create a matching System DSN using MySQL Connector ODBC 8.0.33, replicating the Desktop configuration with secure, read-only service account credentials instead of root or admin access, and test the connection. Then, in Power BI Service, register the ODBC data source with the gateway, map your published dataset to it using the same credentials, and verify the setup. Finally, enable scheduled refresh in the dataset settings, setting a frequency and times and monitor the refresh history to ensure smooth performance and address any issues.
If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
12 Replies
- lbendlin
Super User
Please describe what "Remote" means. Is it directly visible from the Azure service?
Hi lbendlin
The data is hosted on a web hosting platform called Hostinger. It uses a MySQL database to store the data, and phpMyAdmin is provided as the management tool for accessing and managing the database.- lbendlin
So on the open interwebs and anyone can access it ? In that case you would not need a gateway.
SaiTejaTalasilaHi Anonymous ,
Why you want to use odbc connection?
You can install MySQL connector on that gateway and create a MySQL connection on the power bi service.You can use same connection under your semantic model and dataflow.
I hope it helps.
Thanks,
Sai Teja
Thanks for your input.
Initially, I did set up the connection using the MySQL connector. However, when I tried to manually refresh the report in Power BI Desktop, I kept encountering two alternating errors:
“An error happened while reading data from the provider: ‘Object cannot be cast from DBNull to other types’”
“An error happened while reading data from the provider: ‘The given key was not present in the dictionary’”
Due to these issues, I decided to try using an ODBC connection instead. With ODBC, the connection was successful, and I was able to refresh the report in Power BI Desktop without any issues.
That said, I'm still unsure how to set up a scheduled refresh using the ODBC connection in Power BI Service.
Appreciate any guidance on this.
Thanks,
Gokul Saraboji- lbendlin
Downgrade your native MySQL driver to 8.0.28 and try again.
v-hashadapuCommunity Support
Hi Anonymous , Thank you for reaching out to the Microsoft Community Forum.
Install and configure the on-premises data gateway in standard mode on the machine where your ODBC setup is active. Next, create a matching System DSN using MySQL Connector ODBC 8.0.33, replicating the Desktop configuration with secure, read-only service account credentials instead of root or admin access, and test the connection. Then, in Power BI Service, register the ODBC data source with the gateway, map your published dataset to it using the same credentials, and verify the setup. Finally, enable scheduled refresh in the dataset settings, setting a frequency and times and monitor the refresh history to ensure smooth performance and address any issues.
If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.- v-hashadapu
Hi , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.
