Share via

Refreshing PowerPivot Data in SharePoint 2013

  • Article

In SharePoint 2013, Excel Services comes with numerous improvements. One of the most significant for PowerPivot is the ability to refresh data models interactively all the way from the original data sources. Unlike Excel Services in SharePoint 2010, which only queries data models but doesn’t refresh them interactively, Excel Services in SharePoint 2013 first sends processing commands to the Analysis Services server hosting the data model and then queries the data model to update the workbook when you click on Refresh Selected Connection or Refresh All Connections on the Data menu in the browser. Note, however, that interactive data refresh is only available for workbooks created in Excel 2013. If you try to refresh an Excel 2010 workbook, Excel Services will display an error message stating that older versions cannot be refreshed until the file is upgraded, as in the following screenshot.

 

So in SharePoint 2013, users have two options to refresh a data model—Interactive and Scheduled—but note that there are some differences regarding their dependencies. The following table summarizes the most important points.

Interactive Data Refresh

Scheduled Data Refresh

Available out of the box as soon as you have registered an Analysis Services server running in SharePoint mode in the Excel Services configuration.

Requires the deployment of the PowerPivot add-in for SharePoint 2013.

Only refreshes the data in the current user session but does not save the data back to the workbook.

Opens the workbook in a separate refresh session and saves the updated version back to the content database.

Interactive data refresh can use the identity of the currently logged-on user or stored credentials to connect to the data source.

Uses stored credentials.

Only works for workbooks created in Excel 2013.

Works for workbooks created using the SQL Server 2012 PowerPivot add-in for Excel 2010 or using Excel 2013. Note that workbooks created in Excel 2010 with the SQL Server 2008 R2 PowerPivot add-in must be upgraded at least to the 2012 PowerPivot format.

 

The fact that an interactive data refresh can use the identity of the currently logged-on user is particularly interesting because it is the default setting for data connections in Excel. The following screenshot shows the Excel Services authentication settings that influence how Excel Services performs an interactive data refresh against a given data source. In my case, this is a SQL Server connection. If your workbook uses multiple connections, you can configure authentication settings for each data connection individually.

 

There are three options in the Excel Services Authentication Settings dialog box. The first “Use the authenticated user’s account” causes Excel Services to perform the refresh under the identity of the currently logged-on user. The second “Use a stored account” expects a Secure Store Service (SSS) application ID, which Excel Services then uses to retrieve the user name and password in order to authenticate for the data refresh operation. The third “None” stands for “Use the Unattended Service Account of Excel Services” which corresponds to a Secure Store application ID registered in the Excel Services configuration.

Before diving deeper, let’s clarify one potentially confusing aspect about these authentication settings and that is that they determine the Windows identity that Excel Services and Analysis Services use for the data refresh, but they are not necessarily the credentials to establish the connection to the data source. Credentials in the connection string can override the authentication settings. For example, a SQL Server data source can use SQL Server authentication in which case Excel Services first applies its own authentication settings and then Analysis Services establishes the connection at which point the SQL Native Client uses the user name and password from the connection string to log on to the data source. The Excel Services authentication settings are effective if SQL Server uses Windows authentication and the connection string specifies integrated security (Integrated Security=SSPI).

So there are two separate phases: (a) Authenticate against Windows and (b) connect to the data source. The authentication settings, summarized in the following table, influence phase A. Connection string parameters influence phase B.

Authentication Setting

Description

Use the authenticated user’s account

Excel Services uses the Claims to Windows Token Service to transform the currently logged-on user’s SharePoint security token into an impersonation-level Windows security token. For this to work, you must start the Claims to Windows Token Service on the SharePoint application server running Excel Services. Moreover, you cannot use this option if the SharePoint Web application hosting the site of the workbook is using forms-based or SAML-based authentication for incoming requests. The authenticated user must be an authenticated Windows user. If Excel Services cannot determine the Windows identity, you will get an error stating that the data connection uses Windows Authentication and user credentials could not be delegated, as in the left screenshot below.

Use a stored account

Retrieves user name and password from a target application in Secure Store Service and performs a Windows logon.

If you use this option and specify an incorrect target application ID, such as a target application that your user account has no permissions to access, you will get an error stating that Excel Services could not access the specified application id from Secure Store Service. Excel Services retrieves the credentials defined for the target application in the context of the currently logged on user, so make sure your account and the accounts of all other users that are supposed to work with your workbook are mapped to the target application when configuring the Members list for the target application in the Secure Store Service.

None

This is similar to specifying a stored account, except that Excel Services now uses the target application registered under its Unattended Service Account. To specify an Unattended Service Account, display the Excel Services configuration settings in Central Administration, and then under Global Settings, in the External Data section, register the account credentials. For detailed instructions, see Use Secure Store with SQL Server Authentication (SharePoint Server 2013).

If you choose “None” as the authentication method and an Unattended Service Account has not been configured, you will get a corresponding error in the browser when you attempt to refresh the data.

 

 

More often than not, information workers just use the default setting “Use the authenticated user’s account.” While using the default is certainly convenient, it introduces dependencies in SharePoint and in Analysis Services. The reason for this is that Excel Services doesn’t just use the authentication settings to determine its own way to authenticate in Windows. It also uses these authentication settings to instruct Analysis Services to authenticate in the same way. You can see this if you run SQL Server Profiler on your Analysis Services server.

In the Profiler trace, look for a Batch command that Excel Services sends to Analysis Services in response to an interactive data refresh. It will include an ImpersonationInfo node, which specifies an Account name and a Password when using stored credentials (note that the actual password string is not included in the trace for security reasons), but the <Password/> node is there. This node is entirely missing when refreshing data in the context of the currently logged-on Windows user. The difference is that the Secure Store target application gives Excel Services a user name and a password, while the password is not retrievable for the currently logged-on user.

The following listing shows a Batch command example.

So what’s the big deal about the difference? If Excel Services can send Analysis Services a user name and password, Analysis Services can use the standard Windows Logon function to authenticate the user. If the password is unknown, Analysis Services must use Service for User (S4U) Kerberos Extensions to impersonate the user. S4U requires the right to Act as Part of the Operating System, which Analysis Services does not have by default because it is a very high-privileged permission practically equivalent to running as Local System. So, even with Windows authentication on your SharePoint Web apps and the Claims to Windows Token Service running your application servers, interactive data refresh won’t succeed until you grant the Analysis Services account the right to Act as Part of the Operating System. There is also the issue that S4U returns an impersonation-level Windows token, which requires Kerberos Constrained Delegation to be configured for Analysis Services to access a remote data source, as depicted in the following figure. If Analysis Services does not have the permission to Act as Part of the Operating System, it cannot impersonate the Windows user, and if KCD is not configured, it cannot delegate the identity of the currently logged-on user. In both cases, processing will fail and Excel Services will tell you that it was unable to refresh one or more data connections in the workbook, as shown in the right screenshot above.

 

Note that stored credentials do not have the dependencies on Act as Part of the Operating System or KCD because Analysis Services can perform a full Windows Logon. If you do not want to elevate the privileges or cannot configure KCD, your users must fall back to Secure Store target applications for interactive data refresh. Then, Analysis Services can run with a low privileged account in the SharePoint 2013 backend.

Creating and using Secure Store target applications is not very convenient for most information workers, it’s also hard to find the Excel Services Authentication Settings dialog box (in Excel, on the Data tab, click Connections, select the desired connection, click Properties, switch to the Definition tab, and then click on Authentication Settings), but there is no way around the S4U permission requirements. Either educate your users accordingly or ask them to use Scheduled Data Refresh because Scheduled Data Refresh always works with stored credentials since there is no interactivity in these scenarios. On the other hand, if your network is structured such that clients cannot directly access your Analysis Services servers in the backend, it should be OK to grant the Act as Part of the Operating System right and fully enable the interactive data refresh scenarios.

Comments

  • Anonymous
    December 24, 2012
    It's a nice article ..Thanks KayUnkroth

  • Anonymous
    December 26, 2012
    Refreshing data models interactively is a BIG deal and a great new feature !

  • Anonymous
    January 21, 2013
    So when a user refreshes the model they are working on, does this spin up a new instance in AS? If 5 different users refresh the data, is that five new instances running?

  • Anonymous
    January 25, 2013
    Hi Jack,Excel Services has a threshold to avoid creating too many workbook and data model clones. If two users refresh their workbook version at the same time, Excel Services connects both of those to the same replica. I'm not exactly sure what the time interval is, but I think it's somewhere around 10 minutes. So, if these 5 users all refresh data in the same interval, you'll only see one copy. So there are some mechanisms in place to keep the number of clones low, but yes, data models do get cloned and sometimes they might get cloned more often than other times. If this causes your backend to run out of memory, decrease the workbook cache lifetime so that Excel Services unloads workbooks and data models sooner. I think a good value is 1 hour. It can be configured in the Excel Services service app.Hope this helps.Kay

  • Anonymous
    March 16, 2013
    is this going to work with legacy databases as well if I configure it excel services using legacy db does refresh work with that.thanks,browse for morewww.sqlservermanagementstudio.net/.../microsoft-sql-azure.html

  • Anonymous
    March 16, 2013
    Assuming “legacy databases” means PowerPivot workbooks created in Excel 2010, the answer is that you cannot refresh these workbooks interactively. Excel Services can only refresh 2013 workbooks with data models. The PowerPivot add-in for SharePoint 2013 provides support for Scheduled Data Refresh. With that, you can refresh Excel 2010 PowerPivot workbooks, provided you created them using the SQL Server 2012 version of PowerPivot. The old 2008 R2 version of PowerPivot cannot be refreshed in SharePoint 2013.

  • Anonymous
    April 10, 2013
    Hi Kay,if I want to perform interactive data refresh with excel 2013 workbooks, how do I configure KCD for identity delegation?referring to this part of the article:There is also the issue that S4U returns an impersonation-level Windows token, which requires Kerberos Constrained Delegation to be configured for Analysis Services to access a remote data source, as depicted in the following figure.thanks !

  • Anonymous
    September 11, 2013
    The comment has been removed

  • Anonymous
    April 04, 2014
    Currently Microsoft is supporting schedule auto data refresh for SQL Server but not for Analysis service cube. We can schedule auto data refresh for analysis services in Power BI by doing a work around.  Work around would be pulling the SSAS cube data by creating a linked server.I have tried auto data refresh for on premise SSAS cube by doing workaround using linked server with SQL server and it is successful. If you want more details like how to retrieve cube data, you can look into my recent post here..raghavmaddali.blogspot.com/.../power-bi-schedule-data-refresh-for.html.

  • Anonymous
    November 18, 2014
    Thanks for the nice explanation! I have also read the Microsoft BI Authentication and Identity Delegation Reference Guide: msdn.microsoft.com/.../dn186184.aspx which shows a different figure than you do in this blog post. If I understand everything thorough, the figure in this blog post is a bit misleading. If you look at “Figure 14 Impersonating a Windows user for data refresh” in the reference guide on page 29, you will see a call from Excel Services to the C2WTS which I don’t see in the figure in this blog post. If the default authentication setting “Use the authenticated user’s account” is used, this call to C2WTS should be required to translate the SharePoint claims authentication token to a windows token, of which only the username will then be send in the processing command.If this is true, the figure in this blog post is only correct if a stored or unattended account is used. In that case a windows token is directly available and therefore the C2WTS is not required.Please let me know if I’m wrong because I try to understand this processJorgsqlblog.com/.../jorg_klein

  • Anonymous
    November 19, 2014
    Hi Jorg,You're correct. In this figure here I was specifically zooming in on what you'd see from the perspective of Analysis Services, in other words, what you'd find in a SQL Profiler trace. I only mildly pointed out the C2WTS dependency in the text (...Claims to Windows Token Service running your application servers...) because I wanted to emphasize the SSAS issues. Going into the ECS details would have diverted the focus. But you are right, maybe I should have left the C2WTS in the figure here then it would have been clearer how ECS is getting the user name instead of just assuming that ECS "somehow" is getting this piece of information.Cheers,Kay

  • Anonymous
    January 14, 2015
    Why Cannot Microsoft come up with simple, elegant solutions ?? Why always the complexity is beyond me !!

  • Anonymous
    February 12, 2015
    I'm very disappointed microsoft couldn't make this easier.  Oh well... on to tableau