cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bmoles Frequent Visitor
Frequent Visitor

Refresh Now and Scheduled Refresh Not Working

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:

24-01-2017 16-32-38.png

  • Modified Report:

24-01-2017 16-36-07.png

 

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

4 REPLIES 4
v-haibl-msft Super Contributor
Super Contributor

Re: Refresh Now and Scheduled Refresh Not Working

@bmoles

 

I tried with several excel spreadsheets with pivot table in it, I always has “SqlServer” in Data source credentials.

We should only have Azure SQL server name while we connect to Azure DB directly.

 

Best Regards,

Herbert

Vicky_Song Established Member
Established Member

Re: Refresh Now and Scheduled Refresh Not Working - Status changed to: Needs Info

 
bmoles Frequent Visitor
Frequent Visitor

Re: Refresh Now and Scheduled Refresh Not Working

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.

bmoles Frequent Visitor
Frequent Visitor

Re: Refresh Now and Scheduled Refresh Not Working

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:

  1. Excel 2016 (on laptop) requests user and password to fresh data locally
  2. 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.
  3. 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,

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 220 members 2,445 guests
Please welcome our newest community members: