Error when clicking "Manage PowerPivot Data Refresh"


  • Hi,

    When I am clicking on "Manage PowerPivot Data Refresh" I am getting an error message: An unexpected error has occurred.

    In the LOG file the Area: SSAS Mid-Tier Service, Category: Data Refresh throws me an error message:

    EXCEPTION: System.ServiceModel.ServerTooBusyException: The HTTP service located at http://sharepoint:32843/8d496069f61f43d89d4d0fff18e53c57/SSASMidTierService.svc is too busy.

    The same error occurs when I try to Manage the PowerPivotServiceApplication, but I also noticed several others: 

    PF_CHECK_ERROR returned 'critical hresult error' 0x80004005 ; Stack Trace:NA 71128b69-62a6-4cc1-a680-066bf0a28b0d
    PF_CHECK_ERROR returned 'hresult error' 0x80040e41 ; Stack Trace:NA 71128b69-62a6-4cc1-a680-066bf0a28b0d
    ConnectionManager.GetConnection: Failed to create new connection

    Refresh failed for 'Data' in the workbook 'http://sharepoint:32695/PowerPivot Management/8d496069-f61f-43d8-9d4d-0fff18e53c57/1033/Server Health.xlsx'

    Check whether "http://sharepoint:32695/PowerPivot Management/8d496069-f61f-43d8-9d4d-0fff18e53c57/PowerPivot Management Data.xlsx" is in the farm

    and... again...

    EXCEPTION: System.ServiceModel.ServerTooBusyException: The HTTP service located at http://sharepoint:32843/8d496069f61f43d89d4d0fff18e53c57/SSASMidTierService.svc is too busy.


    The thing is it was working fine month ago... I tried to remove the Application Pools for PowerPivot, and remove the the Service Application itself. I redeployed the PowerPivot solution and configured everythin again from scratch and nothing has changed. I tried to do IISRESET. I was even trying to do this:

    $sts = Get-SPServiceApplication | ?{$_ -match "PowerPivot"}

    Excel Services seems to be working fine, I can preview Excel files. When I try to launch the workbook containing PowerPivot connection I am receiveing another message: An error occurred during an attempt to establish a connection to the external data source. The following connections failed to refresh: PowerPivot Data. File displays it's content but slicers aren't working as well.

    When I type in the browser: http://sharepoint:32843/8d496069f61f43d89d4d0fff18e53c57/SSASMidTierService.svc I get Service Unavailable :(

    I am doing everything step by step as described here: About month ago I was able to run everything smoothly...

    Thanks for any help!




    lunedì 23 gennaio 2012 15:52


  • OK I think I have solved it... but it was hot...


    But first let me tell you what also happened. As an act of desperation I tried to retract all powerpivot solutions and guess what... Central Administration stopped working :/ -> Service Unavailable... IISReset and restart didn't help...!  To sum up... it was all working... then Manage Data Refresh Stopped working and after some troubleshooting Central Administration stopped working and ofcourse PowerPivot was not working at all, I couldn't even create PowerPivot Service Application after a while it hanged :P So below steps are good if everything seems to fail.

    And then...

    1. I went to IIS -> Application Pools and started: SharePoint Central Administration and SharePoint Web Services Root -> Central Admin is online again :)

    2. Then I retracted everything -> System Settings -> Manage Farm Solutions -> powerpivotfarm.wsp and powerpivotwebapp.wsp.   I retracted both.

    3. I've changed the name of the C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebServices\PowerPivotSystemService, there are some config files and this bad SSASMidTierService.svc file (Solution deployment will create them once again.)

    4. Then I also cleaned up the Application Pools. In SharePoint 2010 Management Shell you can type

                -Remove-SPServiceApplicationPool PowerPivotAppPool

    5. Then server restart.

    6. Then I redeployed powerpivotfarm.wsp and powerpivotwebapp.wsp as previously mentioned SSASMidTierService.svc was recreated.

    7. Then once again step by step starting from Other Services required

    8. One thing that I think is crucial as well is to use the same account when creating SQL Server Power Pivot Service Application you're specifying Managed Account... this should be the same account that is later used when configuring Secure Store Target Application -> when you're specyfing Target Application Administrators.

    9. Everything worked like a charm. I can now schedule Power Pivot Data refresh and Unattended account is working fine. I don't know what excatly worked but definetely "deleting" the SSASMidTierService.svc could have impact or setting the same account for all AppPools and SecureStore Admins for account details retrieval or all mentioned above.

    I wish you never had an issue like above.



    lunedì 23 gennaio 2012 20:39