PowerPivot Schedule Refresh - A schedule cannot be enabled for a workbook with no external data sources

Question

First of all, here is a bit about my SharePoint installation

SpWeb = SharePoint front end

SpApp = SharePoint application server

SpDB = SharePoint Database

OOS = Office online server

Analysis Server = Server running Analysis services to host PowerPivot model

===============

For configuration I used following walkthroughs

https://docs.microsoft.com/en-us/sql/analysis-services/instances/install-windows/install-or-uninstall-the-power-pivot-for-sharepoint-add-in-sharepoint-2016?view=sql-server-2017 (Installation)

https://docs.microsoft.com/en-us/sql/analysis-services/instances/install-windows/configure-power-pivot-and-deploy-solutions-sharepoint-2016?view=sql-server-2017 (Configuration)

https://docs.microsoft.com/en-us/sql/analysis-services/instances/install-windows/configure-analysis-services-and-kerberos-constrained-delegation-kcd?view=sql-server-2017 (Configure Analysis Services)

All this seems absolutely fine, the powerpivot is loading the data in excel online and also I can see the models in Analysis services so that's all fine.

The only problem I'm having is schedule refresh is not working. When I go to Manage PowerPivot Data refresh, [Enabled] checkbox is always disabled with the red text reading "A schedule cannot be enabled for a workbook with no external data sources".

These errors are logged in SharePoint

• EXCEPTION: System.NullReferenceException: Object reference not set to an instance of an object.
  at Microsoft.Office.Excel.Server.WebServices.ExcelService.<>c__DisplayClass72.b__70(ExcelServiceInternal service, ProxyServiceResult1 result)
at Microsoft.Office.Excel.Server.WebServices.ApiProxy.RunProxyAction[TResult](Action2 proxyAction, ProxyServiceResult1 result, Int32 retriesRemaining)
at Microsoft.Office.Excel.Server.WebServices.ExcelService.EnsureWorkbookModel(String sessionId, Status[]& status)
at Microsoft.AnalysisServices.SPClient16.ExcelApi.Call[T](String fileUrl, ExcelServiceCall1 serviceCall)
  at Microsoft.AnalysisServices.SPClient16.ExcelApi.Call[T](String fileUrl, ExcelServiceCall`1 serviceCall, String methodName, Object[] parameters)
  at Microsoft.AnalysisServices.SPClient16.ExcelApi.EnsureWorkbookModel(String fileUrl, String sessionId)
  at Microsoft.AnalysisServices.SPClient16.WorkbookSession.ForcePing()
  at Microsoft.AnalysisServices.SPClient16.ASSPClient16Proxy.OpenWorkbookModel(String workbookPath, SessionLifetimePolicy lifetimePolicy)
  at Microsoft.AnalysisServices.SPAddin16.GeminiServiceApplication.GetWorkbookConnections(String workbookUrl, Int32& embeddedModelVersion, Boolean& hasEmbeddedModel)
• EXCEPTION: System.ServiceModel.FaultException`1[System.String]: The creator of this fault did not specify a Reason. (Fault Detail is equal to Object reference not set to an instance of an object.). (2 times)
• EXCEPTION: Microsoft.AnalysisServices.SPAddin16.DataRefreshException: Object reference not set to an instance of an object.
  at Microsoft.AnalysisServices.SPAddin16.GeminiServiceApplicationProxy.GetWorkbookConnections(String workbookUrl, Int32& embeddedModelVersion, Boolean& hasEmbeddedModel)
  at Microsoft.AnalysisServices.SPAddin16.DataRefresh.DataRefreshSchedule.LoadDataSourceList(SPFile file, Int32& workbookVersion)
  at Microsoft.AnalysisServices.SPAddin16.DataRefresh.DataRefreshSchedule.LoadSchedule(SPListItem item)

Not Sure what to do now, any help is appreciated.

UPDATE # 1

Ok so apparently it has nothing to do with the configuration. I've taken following steps to get it working in case anyone else is having the same issue.

1. Re-Install PowerPivot on all servers (i.e. SpApp and SpWeb)
2. Restart all server including office online (i.e. OOS)

All looks good now.

Answer 1

From the log you provided, it seems that Workbook Connections cannot be got, and there is an error for excel service.

Since Excel Services functionality is now part of Excel Online in Office Online Server, make sure you add Excel Services Capabilities to a SharePoint 2016 Farm correctly.