Oct 20, 2010

PowerPivot Data Refresh and Excel Refresh

PowerPivot Workbook has 2 kinds of data sources, one is from its Pivot table cache, and the other is from PowerPivot Analysis Cubs. Then what  about Data Refresh? PowerPivot Data Refresh is to refresh Analysis Cubs from external data sources, and as part of this refresh process, the workbook is updated as well.

The credential used for data refresh can be: Unattended account, embedded window credential, or SSS


Notice, there is no window authentication listed above, which means the logon user credential is never used for data refresh. So obviously data in Analysis Service is not security trimmed.

Also notice, it is PowerPivot System Service which retrieves data refresh credential and then sends to Analysis Service, since both PowerPivot and Analysis Service are guaranteed running on the same server, Analysis Service can delegate this credential further to external datasource without need of kerberos delegation.

Not to confuse PowerPivot data refresh with Excel refresh. Excel refresh happens when users click slicer or manually do data refresh. The result is, its cached pivot table get refreshed from .abf file or AS cubs ultimately. PowerPivot functions as Excel data source in this case.

As both Excel Service and PowerPivot service are claim aware, it is claim token that flow from Excel to PowerPivot System service. From PowerPivot to Analysis Service which is not claim aware, it is window token that flows thanks to Claim to Window Token Service. But the most interesting thing is, PowerPivot use PowerPivot Service Application account, not logon user account to connect to Analysis Service:


This is why there is no need to configure security in PowerPivot Analysis Service, but the question is, what is the security enforcement at Analysis Service level even with the note "PowerPivot System Service immediately downgrade the connection"?

the answer is it doesn't need to be, since the data refresh is just to refresh data in AS cubs, and they don't need to be security trimmed. Excel Service will enforce security when it connects to AS Cubs to fetch data.

The follow-up question is, does Excel Service account need kerberos delegation to AS? The answer depends, if Excel Service and Power Pivot run on the same server as they should, there is no hop and Kerberos is not required, if Excel Service and Power Pivot run on different server, Excel Service account should be granted a delegation right to AS and logon users should have read access to AS Cubs if window authentication in Excel workbook is chosen.