none
Excel 2013 SharePoint 2013 ... Not able to refresh a published workbook

    Question

  • When I try to refresh the data in my published workbook (which contains a powerpivot model) I get an error

    An error occurred while working on the data model in the workbook. please try again.

    Here is what I have done to configure powerpivot refresh on my SP2013 farm.

    1. configure secure store service application.

    2. Stored a set of credentials which can connect to my SQL Server. 

    3. Granted rights to everyone on these credentials.

    4. Created a Data Connection Library

    5. Created an ODC file in Excel which connects to the SQL Server and uses the Secure Store Credentials

    6. In the Central Admin, went into settings of Excel Services Web Application and Added a trusted data connection library and pointed it to the library created in step 4.

    7. Started Excel 2013. Clicked on Insert -> PowerPivot Table

    8. Use an external data soruce -> choose connection -> Browse for more -> http://mysharepoint/sites/mysite/myconnectionlibrary (created in step 4) and then selected the ODC file 

    9. Selected few tables and made sure that in excel I am able to drill up and down. and also play with my power pivot table with no problems

    10. Created relationships and made sure that all totals etc are being calculated right.

    11. Saved the file in a SharePoint PowerPivot Gallery.

    12. Closed Excel and went into my gallery using IE.

    13. The workbook opens fine and shows the data initally.

    14. But if I click on any (+) sign to expand anything it gives error

    An error occurred while working on the data model in the workbook. please try again.

    We are unable to refresh one or more data connections in this workbook. The following connections failed to refresh: ThisWorkbookDataModel


    val it: unit=()

    Wednesday, May 22, 2013 8:36 PM

Answers

All replies