none
PowerPivot for SharePoint data refresh does not work - errors in the log

    Question

  • I setup PowerPivot for SharePoint on a single machine, that includes Windows 2008R2, Domain controller, SharePoint, SQL Server, Excel 2010 and PowerPivot for Excel. My step by step guide for setup is here: http://powerpivot-info.com/post/66-step-by-step-guide-on-installing-powerpivot-for-sharepoint.

    When I review my log, I see error entry about every minute:

    EXCEPTION: System.UnauthorizedAccessException: Access to the port is denied.     at System.IO.Ports.InternalResources.WinIOError(Int32 errorCode, String str)     at System.Threading.Semaphore.OpenExisting(String name, SemaphoreRights rights)     at Microsoft.AnalysisServices.SharePoint.Integration.DataRefreshService.Execute() 4f7eb9c8-186a-4496-a76a-93af1d275504

    I tried to schedule data refresh for published workbook. But data refresh does not work. I schedule daily refresh at 6:30pm, but data was not refreshed.

    Could you please help me figuring out how to setup data refresh properly
    http://www.ssas-info.com http://powerpivot-info.com
    Friday, November 27, 2009 11:25 PM

Answers


  • Hi Vidas,

    I believe you’re hitting the problem because you’re using two different accounts: one to be SharePoint’s farm administrator and another to be SQL admin. Users who are using the same account won’t hit the problem.

     

    Please make sure that the farm administrator (SPAdmin in your case) to the SQLServerMSASUser$MACHINENAME$DBINSTANCE (in your case, SQLServerMSASUser$win2008r2-test1$GEMINIBI) windows user group. If the machine is also a Domain Controller, there won’t be a Groups tab in the Service Manage. You may need to go to the Domain Controller tab (roles).



    Hope that helps,
    Mariano

    Saturday, November 28, 2009 6:58 PM

All replies

  • Through SharePoint central admin site I found "Gemini Data Refresh Timer" job that is scheduled to run every minute. Probably this job is triggering this error.

    Data refresh schedule that I am creating is never run. THere is no message in PowerPivot dashboar about this job either.

    What else can I change/check?

    Thank you,

    Vidas Matelis
    http://www.ssas-info.com http://powerpivot-info.com
    Saturday, November 28, 2009 1:25 AM
  • Hi Vidas,


    Can you check the frequency of the IT Dashboard refreshes? Please go to the Central Administration page > Monitoring > IT Operations Dashboard Processing Timer Job.

    The default value is Daily, and not every minute. But that's not why it's failing anyway.

    Can you also check if the Microsoft "Geneva" Claims to Windows Token Services is running (at the services.msc).

    Also, still at the Services Manager (services.msc), can you check who is the user running the SharePoint 2010 Timer Job? Does it belong to the administrator group?


    Thanks,
    Mariano
    Saturday, November 28, 2009 1:53 AM
  • Mariano,


    As you said, IT Operations Dashboard processing timer job is scheduled to run once a day at 3am

    Microsoft Geneval claims service is running and credentials are set to "Local System" account
    Windows SharePoint Services Timer V4 is running under VD\SPAdmin account - that is SharePoint administrator and also for different reasons was added to Domain Admins group.

    Anything else should I check?

    Thanks.

    Vidas Matelis

    http://www.ssas-info.com http://powerpivot-info.com
    Saturday, November 28, 2009 3:09 AM
  • Hi Vidas,


    My bad. You can find it at Central Administration Page > Monitoring > Review Job Definitions > PowerPivot Management Dashboard Processing Timer Job.



    Thanks,
    Mariano

    Saturday, November 28, 2009 3:17 AM
  • Mariano,

    I found following jobs:

    Power Pivot Configuration Timer Job, scheduled weekly
    Power Pivot Health Statistics Collector Timer job, scheduled Minutes

    Gemini data refresh timer job, scheduled - minutes


    Also, I did find  IT Operations Dashboard processing timer job , scheduled once a day.

    By the way, your ULS viewer is better - I'll use it from now. Thanks for suggestion!

    Vidas Matelis
    http://www.ssas-info.com http://powerpivot-info.com
    Saturday, November 28, 2009 3:26 AM


  • Glad you liked it. :)


    One more question. What kind of installation did you perform? I mean, did you chose to create a New Farm or did you chose to Join Existing Farm during the SQL Server Analysis Services with SharePoint Integration setup?


    Thanks,
    Mariano
    Saturday, November 28, 2009 4:25 AM
  • Mariano, 

    Every single step of my installation is described here: http://powerpivot-info.com/post/66-step-by-step-guide-on-installing-powerpivot-for-sharepoint
    I created new farm.

    Vidas Matelis


    http://www.ssas-info.com http://powerpivot-info.com
    Saturday, November 28, 2009 4:37 AM
  • Vidas: Could you please summarize for us what is working and what is not? On earlier thread, when you reported the access is denied error, you said "it" was working (back on 11/25).

    For example, when you bring up a workbook with a slicer in it, can you click on a slicer and have it change the numbers? It isn't enough to just view the workbook, because that just uses the pivot cache. You need to either refresh all connections or click on a slicer. I would like to see if this error is occuring on all access or just data refresh.

    When you try to view the Mgmt Dashboard, are you receiving an error?
    Dave Wickert (MSFT)
    Saturday, November 28, 2009 6:01 AM
  • Also, could you give this a try and tell us what happens.
    Log into the farm admin account (you made it a domain administrator, right? if I remember a previous thread)
    Startup SSMS and make an SSAS connection to .\GEMINIBI instance.
    Can you open up the list of databases (you will likely see it empty unless you were successful in the slicer question above)?
    Any errors?

    Thanks.

    _-_-_ Dave


    Dave Wickert (MSFT)
    Saturday, November 28, 2009 6:22 AM
  • Dave,

    Summary of what is working:
    - I can publish PowerPivot report to Excel

    - I can open PowerPivot report in Excel Sercvices

    - I can internact with PowerPivot report in Excel Services. I am able to do that since I made SharePoint administrator account as member of "Domain Admins" group. To confirm - I can apply filters and numbers do change. Also, I can see that as soon as I start to interact with published report, new SSAS database is created in GeminiBI SSAS instance. One issue here - if I publish PowerPivot report with slicers, but in Excel Services publishing screen choose to publish just object PivotTable, then slicers are not visible in Excel services.

    - I can see preview of PowerPivot report in gallery.

    What does not work:
    - When I open PowerPivot report in Excel services and choose "Data"->"Refresh All connection", it appears everything run through as there is no error. But new data from source SQL Server is never picked up.
    - When I schedule PowerPivot refresh from PowerPivot gallery - no data refresh happening. I do not even see that refresh is executed. And data is never updated.
    - I have data refresh errors in the log.

    Could there be something simple that I have not done to my server? For example, I did not define "Trusted data connection libraries" or "Trusted File location" for Excel services. Could that be an issue? I checked these parameters, but I can see included location "http:/" which to me means all server.
    I simply created PowerPivot report with connection to SQL Server database on the same server.

    I do see PowerPivot management dashboard - no errors.

    Do you need to see more entries from SharePoint log? For example all entries with substring "Exception". Here are some I picked:

    SafeControl load exception:Microsoft.Office.Access.Server.Application, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c Exception: Could not load file or assembly 'Microsoft.ReportingServices.SharePoint.UI.WebParts, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified


    Exception instantiating UserProfileManager, exception is Microsoft.Office.Server.UserProfiles.UserProfileApplicationNotAvailableException: No User Profile Application available to service the request. Contact your farm administrator.   
     at Microsoft.Office.Server.Administration.UserProfileApplicationProxy.GetRawPartitionID(SPServiceContext serviceContext)   
     at Microsoft.Office.Server.UserProfiles.ProfileManagerBase..ctor(SPServiceContext serviceContext)   
     at Microsoft.Office.Server.UserProfiles.ProfileManagerBase..ctor(SPServiceContext serviceContext, Boolean ignorePrivacy)   
     at Microsoft.Office.Server.UserProfiles.UserProfileManager..ctor(SPServiceContext serviceContext, Boolean IgnoreUserPrivacy, Boolean backwardCompatible)   
     at Microsoft.Office.Server.UserProfiles.UserProfileManager..ctor()   
     at Microsoft.Office.Server.UserProfiles.UserProfileService.InitProfileManager()

    System.Web.HttpException: The file '/_layouts/Gemini/WsaUpload.ashx' does not exist.  
     at System.Web.UI.Util.CheckVirtualFileExists(VirtualPath virtualPath)   
     at System.Web.Compilation.BuildManager.GetVPathBuildResultInternal(VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile)   
     at System.Web.Compilation.BuildManager.GetVPathBuildResultWithNoAssert(HttpContext context, VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile)   
     at System.Web.UI.SimpleHandlerFactory.System.Web.IHttpHandlerFactory2.GetHandler(HttpContext context, String requestType, VirtualPath virtualPath, String physicalPath)   
     at System.Web.UI.SimpleHandlerFactory.GetHandler(HttpContext context, String requestType, String virtualPath, String path)   
     at System.Web.HttpApplication.MaterializeHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()   
     at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

    System.Runtime.InteropServices.COMException: The file you are attempting to save or retrieve has been blocked from this Web site by the server administrators.  
     at Microsoft.SharePoint.Library.SPRequestInternalClass.GetFileAndMetaInfo(String bstrUrl, Byte bPageView, Byte bPageMode, Byte bGetBuildDependencySet, String bstrCurrentFolderUrl, Int32 iRequestVersion, Boolean& pbCanCustomizePages, Boolean& pbCanPersonalizeWebParts, Boolean& pbCanAddDeleteWebParts, Boolean& pbGhostedDocument, Boolean& pbDefaultToPersonal, Boolean& pbIsWebWelcomePage, String& pbstrSiteRoot, Guid& pgSiteId, UInt32& pdwVersion, String& pbstrTimeLastModified, String& pbstrContent, Byte& pVerGhostedSetupPath, UInt32& pdwPartCount, Object& pvarMetaData, Object& pvarMultipleMeetingDoclibRootFolders, String& pbstrRedirectUrl, Boolean& pbObjectIsList, Guid& pgListId, UInt32& pdwItemId, Int64& pllListFlags, Boolean& pbAccessDenied, Guid& pgDocid, Byte& piLevel, UInt64& ppermMask, Object& pvarBuildDependencySet, UInt32& pdwNumBuildDependencies, Object& pvarBuildDependencies, String& pbstrFolderUrl, String& pbstrContentTypeOrder)   
     at Microsoft.SharePoint.Library.SPRequest.GetFileAndMetaInfo(String bstrUrl, Byte bPageView, Byte bPageMode, Byte bGetBuildDependencySet, String bstrCurrentFolderUrl, Int32 iRequestVersion, Boolean& pbCanCustomizePages, Boolean& pbCanPersonalizeWebParts, Boolean& pbCanAddDeleteWebParts, Boolean& pbGhostedDocument, Boolean& pbDefaultToPersonal, Boolean& pbIsWebWelcomePage, String& pbstrSiteRoot, Guid& pgSiteId, UInt32& pdwVersion, String& pbstrTimeLastModified, String& pbstrContent, Byte& pVerGhostedSetupPath, UInt32& pdwPartCount, Object& pvarMetaData, Object& pvarMultipleMeetingDoclibRootFolders, String& pbstrRedirectUrl, Boolean& pbObjectIsList, Guid& pgListId, UInt32& pdwItemId, Int64& pllListFlags, Boolean& pbAccessDenied, Guid& pgDocid, Byte& piLevel, UInt64& ppermMask, Object& pvarBuildDependencySet, UInt32& pdwNumBuildDependencies, Object& pvarBuildDependencies, String& pbstrFolderUrl, String& pbstrContentTypeOrder)



    Thank you,

    Vidas Matelis


    http://www.ssas-info.com http://powerpivot-info.com

    Saturday, November 28, 2009 2:37 PM

  • Hi Vidas,

    I believe you’re hitting the problem because you’re using two different accounts: one to be SharePoint’s farm administrator and another to be SQL admin. Users who are using the same account won’t hit the problem.

     

    Please make sure that the farm administrator (SPAdmin in your case) to the SQLServerMSASUser$MACHINENAME$DBINSTANCE (in your case, SQLServerMSASUser$win2008r2-test1$GEMINIBI) windows user group. If the machine is also a Domain Controller, there won’t be a Groups tab in the Service Manage. You may need to go to the Domain Controller tab (roles).



    Hope that helps,
    Mariano

    Saturday, November 28, 2009 6:58 PM
  • Mariano,

    Thank you - that fixed the problem. Now when I  schedule data refresh, I can see it succeeding as I see new data in my published workbooks. Also data refresh error is gone from the logs.

    Thank you very much for your help!

    Vidas Matelis

    http://www.ssas-info.com http://powerpivot-info.com
    Saturday, November 28, 2009 7:51 PM
  • Hi Mariano

    I am facing similar issue with the slicer. I can open the workbook and view the data. However, when I try to apply filters it gives me and error

     

     "An error occurred during an attempt to establish a connection to the external data source. The following connections failed to refresh:".

     

    I have added the farm account into the suggested windows group.

     

    I even tried to create unattended execution account in secure store, but it didn't help. could you please let me know if I am missing anything

     

    Thanks

    Ram

    Wednesday, October 13, 2010 2:07 AM