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.
Dear Anonymous Poojara_D12
Thanks both of you, you two really provided me a lot of useful information.
I'm using Power BI Desktop (RS version), the Pbirs, MySQL all hosted at the same local network, so I hvnt setup a gateway and the credentials and the connection were tested ok.
But I found that at the bi desktop when I check at the storage mode it was import but not directquery!! Is that why I can't active auto refresh? If yes how can I change the storage mode into directquery?
The data was connected from get data>MySQL and input the localhost:3306, but it show import
- Please help
Hi,eRic_LaU .Thank you for your reply.
For your operation. I hope the following suggestions can solve your doubts.
1. About the setting and use of the gateway connection.
If the Web Portal you are using is Power BI Report Server (PBIRS)
In this case, your data source (MySQL) and Web Portal are both local. Therefore, you do not need to use a gateway.
You just need to set up the data source credentials on the Web Portal side to ensure successful report refresh.
As I mentioned in my previous reply, Report Server itself only needs to set up Scheduled Refresh for data in import connection mode.
And I simulated Report Server refreshing Mysql data source in import mode yesterday, and it worked. (I only set up the datasource credentials and did not use a gateway)For Direct Query connection mode, Report Server can't (doesn't need to) go to Set Scheduled Refresh.
Save as > Save to Power BI Report ServerIf the Web Portal you are using is Power BI Service
Your data source (Mysql) is local and the Web Portal (Service) is in the cloud. In this case you need to Publish to Service and set up a gateway connection for it.
Publish >Publish to Power BI Service.2. About MySQL data sources in Power BI Report Server
When Power BI Desktop connects to a MySQL data source, it is in import mode by default, and MySQL does not support Direct Query connection mode.URL:
Solved: Direct Query MySQL - Microsoft Fabric Community
Some users have mentioned that you can use another connector to connect to the Mysql data source and use Direct Query connection mode.
I can't replicate this operation due to my environment, so I can't tell if this approach is used for Report Server (based on user feedback, this approach should be tried for Power BI Service)
You might want to try using the ODBC connector (Power BI Reprot Server supports ODBC)3. About saving a report to Power BI Report Server Web Portal using Direct Query connection mode.
When you use Direct Query, you only need to set the data source credentials for the report on Report Server. There is no need to set up Scheduled Refresh (which is also not supported by Report Server in Direct Query mode).
If you open a report in Direct Query connection mode, it is very likely that you will get an error saying that the data source is not trusted; Report Server, as a local server, emphasizes security, so you will need the relevant data source credentials.
The following issue contains a list of issues that you may encounter in subsequent operations.
Adding a system environment variable may be a good idea.
URL:
Solved: dd - Microsoft Fabric Community
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.
Hi 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.
