Refresh Now and Scheduled Refresh Not Working

bmoles · ‎01-24-2017

We have an Excel spreadsheet with few Pivot Tables reporting data from PowerPivot which data source is an Azure SQL database. The file is saved on a SharePoint Online library. This report was working fine for months however this afternoon it stopped refreshing.

Yesterday we created a copy of the before mentioned report (spreadsheet) and we added some columns and measures to the PowerPivot model. The connection string and the SQL queries to the Azure SQL database are still the same. We uploaded this new spreadsheet to the same SharePoint Online library and connected it on the PowerBI service. The "Schedule Refresh" and the "Refresh Now" features don't work. The reports cannot be refreshed from the PowerBI service.

This morning, the original report was refreshing and the new copy didn't. We compared their settings on the PowerBI service and found one difference: The original spreadsheet has "SqlServer" as "Data source credentials" while the new spreasheet has the server name of the Azure SQL database.

Original Report:

Modified Report:

In both cases the data source credential name is read only. We didn't set any one of them.

In addition to the above we have done the following test:

From the SharePoint Online library where the spreadsheet are stored we have copied the original one (to the same library) with a different name. No modifications at all have been done to this new copy. We have connected it on the same PowerBI service. We have noticed that the "Data source credentials" show the Azure SQL server name (the same as the modified report and different to the original report). The "Refresh Now" and the "Schedule Refresh" do not work either.

The original report stopped refreshing after the last test was done. Not sure whether it may be related.

These reports are being used daily by several employees who need them to be up-to-date. Any help would be appreciated.

Thanks,

Regards,

Benjamin

bmoles · ‎01-26-2017

After successfully testing that the report could refresh (with the "Refresh now" button) I realized that the PowerBI service doesn't allow to schedule a refresh if its data source's credentials are not previously updated which in turn prevent the report from refreshing again.

Previous tests were done with a spreadsheet which had the login credentials saved in it. This meant that there was no need to enter user and password to refresh the report.

We did additional tests and shaw that removing the credentials on the spreadsheet had the following effects:

Excel 2016 (on laptop) requests user and password to fresh data locally
If the spreadsheet is uploaded to OneDrive and connected from PowerBI service the message "Your data source can't be refreshed because the credentials are invalid. Please update your credentials and try again." appears as before. Withoug updating credentials we check the following:
1. The "Refresh now" on PoweBI finishes without errors and without asking for credentials.
2. Excel Online is also able to refresh the report without asking for credentials.
3. Scheduled refresh cannot be enabled.
After updating credentials the reports fail while refreshing (through PowerBI'S "Refresh now" and "Scheduled Refresh" and through Excel Online.

Can any one explain why Excel Online is able to refresh the report without being the credentials stored within while the local Excel cannot?

Thanks,

bmoles · ‎01-26-2017

Thanks Herbert for your answer,

After several tests we fixed the issue.

When the spreadsheet is connected to the PowerBI service its “Data source credentials” displays the following message:

Your data source can't be refreshed because the credentials are invalid. Please update your credentials and try again.

The message above is misleading because the report can refresh without updating credentials. In fact, updating the credentials is what prevents the report to refresh.

I think that this is why the original report stopped working. When we created a new copy (without any modification at all) and we connected it to the PowerBI service we also updated its data source credentials (following the message). I think that the PowerBI service linked both copies (the original and the new one) to the same data source and when we updated the credentials of the data source on the new copy the change affected as well the original report preventing both from refreshing.

The above information are suppositions based on our tests and observations. It may be good that the development team behind the product was aware of it for further investigation.