1

I have a spreadsheet with a powerpivot data model in sharepoint 2013 sp1 with sql 2012 sp2 for the power pivot instance. The power pivot data refresh works fine when scheduled, when the spreadsheet has just a blank workbook. I then created a basic pivot table using 5 different fields in the query and saved it. After this is done, the data refresh fails saying call to excel services returned an error. It is an oracle data source and the query takes about 17 minutes to run.

When I look in the Sharepoint logs the error says "EXCEPTION: System.InvalidOperationException: Call to Excel Services returned an error. ---> Microsoft.AnalysisServices.SPClient.Interfaces.ExcelServicesException: ECS failed with non-zero return status. First error is name='ExternalDataRefreshFailed'; message='An error occurred while working on the Data Model in the workbook. Please try again.'; severity='Error' "

Any idea on how to troubleshoot this?

Improve this question
1
  • can you look into SharePoint logs? the message you see now is too generic. You need yo narrow the problem down. Commented Apr 25, 2015 at 6:50

1 Answer 1

Reset to default
0

So the issue was that the spreadsheet got too big for excel services to open it once the pivot table was created. The default value for max spreadsheet size in excel services is 10MB.

You know that this is happening to you if you get this error when trying to view a spreadsheet in the browser, and you have to open a spreadsheet in Excel locally instead.

Excel Services Error

Whenever you schedule a PowerPivot data refresh what essentially happens, as far as I understand it, is that SharePoint opens the spreadsheet for you in the browser (Excel Services) and hits refresh. If excel services is set to not open spreadsheets larger than 10 MB, then SharePoint can't open the spreadsheet and do the refresh.

You can increase the maximum workbook size in Central Administration.

  1. Open Central Administration -> Application Management -> Manage Service Applications

  2. Select your Excel Web Services Application

  3. Go to Trusted File Locations
  4. Choose the trusted file location of the file you are trying to open, most likely this should be ‘http://’.
  5. Click on the drop down context menu under ‘http://’ and select ‘Edit’.
  6. Scroll down the page to ‘Maximum Workbook Size’, increase the limit as required.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.