none
Data refresh feature of excel Services and PowerPivot Data Refresh Issues

    Question

  • I am having issue getting the data refresh feature work from the excel workbook in the PowerPivot Gallery. I have configured the unattended Data refresh account and I set the authetication type to none in the excel workbook. The excel services application pool account has contribute to the sharepoint site and the unattended refresh acount is the farm account which has read permission to the SSAS external data source. initially if I upload to the powerpivot gallery I am able to refresh but after a while , I get this error

    " Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator"

    But the schedule PowerPivot Data Refresh is working fine. The credential property is set to use the data refresh account set by the administrator and the data source property is set to use use the credential contained in the workbook.

    I nedd help on how to get the data refresh work from the excel workbook in the powerpivot gallery.

    Thanks


    BI Developer

    Monday, March 26, 2012 2:03 PM

Answers

  • Hi Abioye,

    Excel Services cannot connect to or load the PowerPivot data. Conditions that cause this error to occur include the following:

    Scenario 1: Service is not started

    The SQL Server Analysis Services (PowerPivot) instance is not started. An expired password will cause the service to stop running. For more information about changing the password, see Configure PowerPivot Service Accounts and Start or Stop a PowerPivot for SharePoint Server.

    Scenario 2a: Opening an earlier version workbook n the server

    The workbook you are attempting to open might have been created in the SQL Server 2008 R2 version of PowerPivot for Excel. Most likely, the Analysis Services data provider that is specified in the data connection string is not present on the computer that is handling the request.

    If this is the case, you will find this message in the ULS log: “Refresh failed for ‘PowerPivot Data’ in the workbook ‘<URL to workbook>’”, followed by “Unable to get a connection”.

    To determine the version of the workbook, open it in Excel and check which data provider is specified in the connection string. A SQL Server 2008 R2 workbook uses MSOLAP.4 as its data provider.

    To work around this issue, you can upgrade the workbook. Alternatively, you can install client libraries from the SQL Server 2008 R2 version of Analysis Services on the physical computers running PowerPivot for SharePoint or Excel Services:

    Upgrade PowerPivot for Excel and PowerPivot Data

    Install the Analysis Services OLE DB Provider on SharePoint Servers

    Scenario 2b: Excel Services is running on an application server that has the wrong version of the client libraries

    By default, SharePoint Server 2010 installs the SQL Server 2008 version of the Analysis Services OLE DB provider on application servers that run Excel Services. In a farm that supports PowerPivot data access, all physical servers running applications that request PowerPivot data, such as Excel Services and PowerPivot for SharePoint, must use a later version of the data provider.

    Servers that run PowerPivot for SharePoint get the updated OLE DB data provider automatically. Other servers, such as those running a standalone instance Excel Services without PowerPivot for SharePoint on the same computer, must be patched to use the newer client libraries. For more information, see Install the Analysis Services OLE DB Provider on SharePoint Servers.

    Scenario 3: Domain controller is unavailable

    The cause might be that a domain controller is not available to validate the user identity. A domain controller is required by the Claims to Windows Token Service to authenticate the SharePoint user on each connection. The Claims to Windows Token Service does not use cached credentials. It validates the user identity for each connection.

    You can confirm the cause of this error by viewing the SharePoint log file. If the SharePoint logs include the message "Failed to get WindowsIdentity from IClaimsIdentity", the user identity could not be authenticated.

    To work around this problem, join the computer to the same domain as the PowerPivot server, or install a domain controller on your local computer. The second solution, installing the domain controller, will require you to create local domain accounts for all services and users. You will need to configure service accounts and SharePoint permissions for the accounts you define.

    Installing a domain controller on your computer is useful if your objective is to use PowerPivot for SharePoint in an offline state. For detailed instructions on how to use PowerPivot offline, see the blog entry for "Taking your PowerPivot server off the network" on http://www.powerpivotgeek.com.

    Scenario 4: Unstable server

    One or more services might be in an inconsistent state. In some cases, running IISRESET will resolve the problem.

    For more information, please refer to the following links(please read them carefully):
    http://msdn.microsoft.com/en-us/library/ff487856.aspx
    http://sqlserverpedia.com/blog/sql-server-bloggers/sharepoint-and-powerpivot-connections-fail-to-refresh-in-sql-server-2008-denali-ctp3/
    http://powerpivotgeek.com/2010/02/08/the-data-connection-uses-windows-authentication-and-user-credentials-could-not-be-delegated/
    http://weblogs.asp.net/coltk/archive/2009/08/17/data-refresh-failed-in-excel-services.aspx

    Thanks,
    Lhan Han

    Tuesday, March 27, 2012 9:07 AM