Power Automate scheduled refresh for dataset on Premium Workspace not refreshing

[andre_lara]

Hello, 

I have created a Power Report which connects to a MySQL datasource by using a SQL Query to fetch specific columns within my database. This report and its dataset live on a premium workspace within my company's intranet, previously I went and manually refresh the dataset to the report since it is suppose to have a daily refresh, to automate this action I recure to the Power Automate cloud workflows, which have built-in interactions with Power BI and set a recurrence workflow to: refresh the dataset from the workspace at an specific time per CST, run a query against it to generate a csv table and store it on a public sharepoint folder. Once I manually test the workflow it succeeds and allows me to apply it accordingly, however when it runs on the scheduled timeframe I can see that is retrieving an error message on credentials: 

Even upon manual refresh, when I go to the published report on the workspace and update the visuals it obtains the last dataset obtained prior to the failed refresh. 

There is a similar thread already around the refresh failure on scheduled recurrences: Error on scheduled refresh; Dynamic 365 was used to gather all datasets per the idea's-owner inputs, so the suggested solution for the thread will not solve my issue since the datasource values at the advanced editor are defined as next: 

let 
Source = MySQL.Database("mysql-string.domain.com", "DB_MySQL", [ReturnSingleDatabase=true, Query="SELECT (...)")
in
Source

I have check the connection credentials to the MySQL database and everything is working if no scheduled refresh is implemented, thus I'm able to manually refresh the dataset and reload the visuals. 

What could be possibly the problem for the schedule to not authenticate the credentials?

[v-xiaoyan-msft]

Hi @andre_lara , 

In Power BI, go to the settings of the dataset that’s causing the issue. Under ‘Data source credentials’, make sure that the correct authentication method is selected and that the credentials are correct.

If your data source is on-premises, you might need to use a data gateway for refreshes. The gateway serves as a bridge between your on-premises data and Power BI.

Make sure that the connection string in your Power Query is correct. Any changes in the database location or name could cause issues with the refresh.

If you’re using Power Automate to automate the refresh, make sure that the flow is set up correctly. Any issues with the flow could cause problems with the refresh.

Best regards.
Community Support Team_Caitlyn

[andre_lara]

Hello @v-xiaoyan-msft, 

Thanks for the inputs, the MySQL Data source I'm consuming is actually a AWS Aurora container, being this cloud-based it doesn't fall under the on-premise source types. 

I have checked the settings for the dataset linked to the report, the "data credentials" options is disabled for interaction (see below image for reference): 

I also navigated to the report lineage and the data source does contain a defined gateway:

Moreover the connection string to the Database is correctly defined since I can fetch data from it without problem when manually requested, I can do that in both the Power BI Desktop App and the Cloud instance as well, previously I had even implemented a Power Automate button to manually trigger the dataset refresh from the published instance of the Report (on the corresponding workspace) and it successfully refreshed the dataset. 

The problem resides within the scheduled recurrence, whether if its generated from Power Automate or Power BI itself. I even tried to apply a refresh recurrence directly on Power BI addressing the desired dataset but the system doesn't allow me to create one (see image on infra), that is why I incurred on the Power Automate workflow. 

I'm unsure on how to resolve this problem. Thanks in advance for the follow-up. 

[andre_lara]

Hello @v-xiaoyan-msft, 

Following up from my previous exchange, I verified the datasource settings directly on the Power BI Desktop to update the Privacy level to Organizational, I then tried and enter a On-premise gateway and now I'm able to see the refresh schedule for the dataset directly on Power BI! 

I will be monitoring the dataset refresh but it seems like the issue should be solved, thanks. 

Kind Regards. 

[v-xiaoyan-msft]

Hi  @andre_lara ,

Thank you for sharing, for other users with similar experiences you can check the comments below. This thread will now be closed.

Best regards.
Community Support Team_ Caitlyn