Forum Discussion
Pbirs auto refresh MySQL
Hi eRic_LaU
Since your MySQL data, Power BI Report Server (PBIRS), and SQL service are hosted within the same local network, you do not need an on-premises data gateway for data refreshes. The gateway is typically required when connecting to cloud-based data sources or external systems. Since the connection to MySQL in PBIRS is already tested and successful, the issue likely lies elsewhere. First, double-check the credentials used for the scheduled data refresh in PBIRS, ensuring they are correctly configured and have sufficient permissions to access the MySQL database. Additionally, ensure there are no issues with the firewall or network settings that might block communication between PBIRS and MySQL. If the credentials are fine, inspect the PBIRS logs for any errors related to the refresh process, as they may provide insight into why the refresh completes but no data is updated. Another useful test is to manually refresh the dataset in PBIRS to see if the issue is isolated to the scheduled refresh configuration. If the manual refresh works, the issue might lie in the scheduled refresh settings or configuration. Lastly, verify the connection string used in Power BI Desktop and ensure there are no discrepancies with the settings in PBIRS. By following these steps, you should be able to identify the underlying cause and resolve the issue preventing the data from being updated during scheduled refreshes.
Hi,Poojara_D12 ,and Vanchy_Liao ,thanks for your concern about this issue.
Your answer is excellent!
And I would like to share some additional solutions below.
Hello,eRic_LaU .I am glad to help you.
Are you using Power BI Desktop (RS version) or Power BI Desktop in general?
For your question about PBRS, I think you are having problems with Power BI Report Server. (Not Power BI Service)
Since Power BI Report Server is a local server, there is no need to use a gateway.
I only configured the MYSQL account authentication method in the Report Server Web portal normally, and ensured that the proxy for the MYSQL database instance is normally turned on, and was able to implement the Power BI Report Server Scheduled Refresh for pbix reports from the Mysql data source normally (provided that your connection mode must be import).
You do not need to configure Scheduled Refresh for Direct Query connection mode in RS.
Like this.
You need to make sure that the firewall allows 3306 (Mysql port) to pass through normally.
1.Users used to access the MySQL database
2. Data source credentials for Web portal
SQL Server Agent (SSMS ) must be enabled for execution Scheduled Refresh.
If you do implement the above steps in Power BI Report Server and ensure that the configuration is correct, then if the system shows that the refresh is complete, then it means that the data has indeed been updated.
The report (MySQL datasource) here refreshed successfully
Solved: Re: Scheduled refresh failed seemingly random - Microsoft Fabric Community
And if you are using Power BI Service, you need a gateway to connect to it
Suggestion 1
So I think your data has been successfully refreshed, if you want to check the exact information you can check the report server log file
Suggestion 2
Or appropriate modification of your MYSQL data source, in the use of the table for its use to add a time and date field, and bind a trigger for it, when the data source to monitor the data changes in the table, the column corresponding to the row of data to the latest date (i.e., to add a timestamp), will be the column where the table is added to your pbix file, and displayed in the report, so that you can most intuitively view the data changes. Or check the data in your MYSQL database itself (whether the timestamp has changed)
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super UserHi Anonymous
Thanks for the kind words.
If possible, please visit my Youtube channel, I upload videos around Power BI, SQL and related things, it will be really helpful and gives motivation to work more on that.
The link is given below of my channel.