Pbirs auto refresh MySQL

[eRic_LaU]

Dear All,

I have setup a dashboard in pbirs the dashboard load sucessfully, when i setup a schedule refresh I found no data were refreshed but the schedule status was "Completed Data Refresh"

I'm using MySQL as my data source and connect to the dashborad thr pbi desktop. Do i miss or i need to setup anything to arch the purpose?

Looking for help

[Poojara_D12]

Hi @eRic_LaU 

If your scheduled refresh in Power BI Report Server (PBIRS) completes successfully but does not update the data, the issue is likely due to PBIRS’s limited support for direct data refresh from MySQL. Unlike Power BI Service, which allows scheduled refresh for cloud and on-prem sources, PBIRS does not natively support refresh for MySQL. When you publish a PBIX file connected to MySQL, PBIRS relies on the connection settings used in Power BI Desktop (Report Server version) at the time of publishing, but it does not refresh the data directly from MySQL on schedule. Instead, the refresh process may complete without errors while retaining the old data. To resolve this, you can either manually refresh the PBIX file in Power BI Desktop and re-upload it or use an alternative method like extracting MySQL data into SQL Server or using an ODBC connection, which PBIRS supports for scheduled refresh. Another approach is setting up SQL Server Integration Services (SSIS) or Power Automate to regularly move data from MySQL to a location that PBIRS can refresh from. If automation is necessary, consider using a script or a linked server in SQL Server to act as a bridge between MySQL and PBIRS. Let me know if you need guidance on implementing one of these solutions!

[Anonymous]

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.

[Poojara_D12]

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.

[eRic_LaU]

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 

[Anonymous]

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 Server

If 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.

[Poojara_D12]

Hi @eRic_LaU 

If your scheduled data refresh in Power BI Report Server (PBIRS) shows as "Completed Data Refresh" but no data is actually updated, it could be due to a misconfiguration in the data source connection or credentials. First, ensure that the credentials for your MySQL data source are correctly set up in PBIRS and have the necessary permissions to access the database. If you're using an on-premises MySQL server, make sure an on-premises data gateway is installed and properly configured, as PBIRS will need this to refresh data from an on-premises source. Also, verify that the connection string in Power BI Desktop is correct and test it to ensure successful connectivity to MySQL. If the refresh still doesn't work, check the PBIRS logs for any errors during the refresh process, as they can provide further insights into the issue. Additionally, try a manual refresh within PBIRS to see if it successfully updates the data; if it does, the issue may be with the scheduled refresh configuration. By following these steps, you can identify and resolve the root cause of the problem with your data refresh.

[eRic_LaU]

Hi @Poojara_D12 

Thanks for your reply, The data source connection/credentials in pbirs was tested and connection OK.

MySQL data, pbirs & sql service all host at the same local network. Data gateway still required? If Yes How do i setup, If NO what i have miss to enable the refresh.

[Poojara_D12 · Accepted solution]

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.

[Vanchy_Liao]

Hi @eRic_LaU ,

if the PBIRS shows "Completed Data Refresh", it works fine without errors.

I think you probably have many database environments, so you have to check the connection string of your PBIRS and PBI report or to check the data source if the data exists at that moment.