You may receive an error similar to the following when running a scheduled data refresh of a PowerPivot workbook in SharePoint:
The table with ID of '<TableID>', Name of '<TableName>' referenced by the 'Sandbox' cube, does not exist. An error occurred when loading the Sandbox cube, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.POWERPIVOT\OLAP\Backup\Sandboxes\PowerPivotServiceApplication\<GUID>.db\Sandbox.9825.cub.xml'.
You might see this error in the scheduled data refresh results on the PowerPivot Data Refresh History Page. You might also see this error in ULS logs of the SharePoint server running the PowerPivot system service after matching the Correlation ID of a generic error received when trying to view the Manage Data Refresh page for the PowerPivot workbook.
The error may be the result of a recent SharePoint update which could cause the file store data cache for the PowerPivot cube data to become obsolete.
The following steps can be used to resolve the error so the scheduled data refresh runs successfully:
1. Stop the SQL Server PowerPivot System Service on the SharePoint server running the PowerPivot service in SharePoint Central Administration > System Settings > Manage Services on Server.
2. Rename the following folder (the path may be slightly different on your system depending on the drive where PowerPivot was installed and the name of the PowerPivot service application):
C:\Microsoft SQL Server\MSAS10_50.POWERPIVOT\OLAP\Backup\Sandboxes\PowerPivotServiceApplication1
to
C:\Microsoft SQL Server\MSAS10_50.POWERPIVOT\OLAP\Backup\Sandboxes\PowerPivotServiceApplication1.old
3. Start the SQL Server PowerPivot System Service in SharePoint Central Administration.
4. Retest the scheduled data refresh of the PowerPivot workbook.
5. If the data refresh runs successfully, you can delete the PowerPivotServiceApplication1.old folder.
This issue is fixed in SharePoint 2013 SP1 (Released Feb 25th 2014):
Update center for Office, Office servers, and related productshttp://technet.microsoft.com/en-US/office/ee748587.aspx
Issue:
"Call to Excel Services returned an error" when performing a Scheduled Data Refresh.
In the ULS logs, you will see:
w3wp.exe (0x0A28) 0x0DB8 PowerPivot Service Data Refresh 99 High EXCEPTION: System.InvalidOperationException: Call to Excel Services returned an error. ---> Microsoft.AnalysisServices.SPClient.Interfaces.ExcelServicesException: We're sorry. We can't open the workbook in the browser because it uses these unsupported features: • Shapes or other objects ---> Microsoft.Office.Excel.Server.WebServices.ExcelServerApiException: We're sorry. We can't open the workbook in the browser because it uses these unsupported features: • Shapes or other objects at Microsoft.Office.Excel.Server.WebServices.ApiShared.ExecuteServerSessionMethod(Boolean hasSessionId, String sessionId, CoreServerSessionMethod coreWebMethod, String name, Boolean skipFeatureCheck) at Microsoft.Office.Excel.Server.WebServices.ExcelServiceInternal.OpenWorkbookInternal(String workbookPath, Boolean editingMode, String uiCultureName, String dataCultureName, Boolean newWorkbook, Boolean suppressRefreshOnOpen, Boolean openExclusive, Status[]& status) at Microsoft.Office.Excel.Server.WebServices.ExcelServiceInternal.OpenWorkbookEx(String workbookPath, String uiCultureName, String dataCultureName, Boolean exclusive, Status[]& status) at Microsoft.AnalysisServices.SPClient.ExcelApi.<>c__DisplayClassa.<OpenWorkbookEx>b__9(ExcelService svc, Status[]& status) at Microsoft.AnalysisServices.SPClient.ExcelApi.Call[T](String fileUrl, ExcelServiceCall`1 serviceCall) --- End of inner exception stack trace --- --- End of inner exception stack trace --- at Microsoft.AnalysisServices.SPClient.ExcelApi.Call[T](String fileUrl, ExcelServiceCall`1 serviceCall) at Microsoft.AnalysisServices.SPClient.ExcelApi.Call[T](String fileUrl, ExcelServiceCall`1 serviceCall, String methodName, Object[] parameters) at Microsoft.AnalysisServices.SPClient.ExcelApi.OpenWorkbookEx(String fileUrl, String uiCultureName, String dataCultureName, Boolean exclusive) at Microsoft.AnalysisServices.SPClient.ASSPClientProxy.OpenWorkbookModelForRefresh(String workbookPath, SessionLifetimePolicy lifetimePolicy) at Microsoft.AnalysisServices.SPAddin.DataRefresh.DataRefreshService.ProcessingJob(Object parameters)
This is a rather generic error message. However, we have seen several cases where the Scheduled Data Refresh fails because of Unsupported Features in the workbook.
We have a tool that you can use to remove these Unsupported Features.
Excel Services Compatibility Checker Download Page
http://blogs.msdn.com/b/cumgranosalis/archive/2007/06/29/excel-services-compatibility-checker-download-page.aspx
First install the "Excel Services Compatibility Checker Add-In". As you can see a tab called "Excel Services" will appear in the ribbon (may need to restart Excel). Now you can remove the Unsupported Features by choosing "Quick Fix". I do suggest creating a copy (back up) of the workbook. After you have removed the Unsupported Features, republish to the PowerPivot Gallery and run a Scheduled Data Refresh.
Excel Services Compatibility Checker Add in - Beta
http://blogs.msdn.com/b/cumgranosalis/archive/2007/06/29/excel-services-compatibility-checker-addin-beta.aspx
Other Resources:
Differences between using a workbook in Excel and Excel Services
http://office.microsoft.com/en-us/excel-help/differences-between-using-a-workbook-in-excel-and-excel-services-HA010021716.aspx
Excel Services part 12: Unsupported features
http://blogs.office.com/b/microsoft-excel/archive/2005/12/01/excel-services-part-12-unsupported-features.aspx
Excel Services in SharePoint 2010 Feature Support
http://blogs.msdn.com/b/excel/archive/2009/11/19/excel-services-in-sharepoint-2010-feature-support.aspx
Edit a workbook that contains features unsupported by Excel Web App
http://office.microsoft.com/en-us/web-apps-help/edit-a-workbook-that-contains-features-unsupported-by-excel-web-app-HA102540964.aspx
Again, this is somewhat of a generic message, so this may or may not resolve your issue, but it is definitely worth testing.
Other FIX: You may also see this error in the ULS logs:
w3wp.exe (0x0A28) 0x0AC8 PowerPivot Service Data Refresh 99 High EXCEPTION: System.InvalidOperationException: Call to Excel Services returned an error. ---> Microsoft.AnalysisServices.SPClient.Interfaces.ExcelServicesException: You don't have permission to save your workbook here. ---> Microsoft.Office.Excel.Server.WebServices.ExcelServerApiException: You don't have permission to save your workbook here. at Microsoft.Office.Excel.Server.WebServices.ApiShared.ExecuteServerSessionMethod(Boolean hasSessionId, String sessionId, CoreServerSessionMethod coreWebMethod, String name, Boolean skipFeatureCheck) at Microsoft.Office.Excel.Server.WebServices.ExcelServiceInternal.SaveWorkbookCopy(String sessionId, String workbookPath, WorkbookType workbookType, SaveOptions saveOptions, Status[]& status) at Microsoft.AnalysisServices.SPClient.ExcelApi.<>c__DisplayClass10.<SaveWorkbookCopy>b__f(ExcelService svc, Status[]& status) at Microsoft.AnalysisServices.SPClient.ExcelApi.<>c__DisplayClass13.<Call>b__12(ExcelService e, Status[]& status) at Microsoft.AnalysisServices.SPClient.ExcelApi.Call[T](String fileUrl, ExcelServiceCall`1 serviceCall) --- End of inner exception stack trace --- --- End of inner exception stack trace --- at Microsoft.AnalysisServices.SPClient.ExcelApi.Call[T](String fileUrl, ExcelServiceCall`1 serviceCall) at Microsoft.AnalysisServices.SPClient.ExcelApi.Call(String fileUrl, ExcelServiceCall serviceCall, String methodName, Object[] parameters) at Microsoft.AnalysisServices.SPAddin.DataRefresh.DataRefreshService.ProcessingJob(Object parameters)
To fix this, you will need to give the account running PowerPivot Full Control to the Web App under Policy for Web Application.
After upgrading your SharePoint 2010 PowerPivot server to be at SQL 2012 SP1 level you get data refresh failures and this error in the logs:
EXCEPTION: Microsoft.AnalysisServices.SharePoint.Integration.Redirector.RedirectorException: This request could not be redirected to a PowerPivot for SharePoint server. An internal error occurred. Please look in the SharePoint logs for more information or contact the administrator of this farm. --->
System.Data.SqlClient.SqlException: Procedure or function UpdateRunProcessTimeWindow has too many arguments specified.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, BooleanbreakConnection)
...
Cause:
It appears that the "PowerPivot Configuration tool" which needs to be ran after the SQL service pack does not update the PowerPivotServiceApplicationDB SQL database correctly. (We are still researching this)
This causes a mismatch between the updated DLL's and the database on the number of parameters to use on the UpdateRunProcessTimeWindow stored procedure.
Workaround:
The workaround is to manually update your PowerPivotServiceApplicationDB. Follow steps below:
The steps above will redefine the stored procedure so that it matches what the updated DLL's are looking for.
You may receive the following error when trying to view an Excel chart or graph on a website using Excel Services in SharePoint:
The maximum number of allowed sessions per user has been exceeded. The operation cannot be completed.
To fix this error, go to SharePoint Central Administration > Application Management > Service Applications > Manage service applications > Excel Services application > Global Settings. In the Session Management section, increase the value for Maximum Sessions Per User.
If this does not fix the error, you may be using anonymous authentication, for example when your site is on a public website. For the Maximum Sessions Per User setting, a user is an authenticated user. When anonymous access is enabled in SharePoint, all the users hitting the site are anonymous users, which maps to a single internal account that is authenticated to retrieve the data. So the maximum sessions setting applies to the same pool of sessions for all users.
If you are using anonymous authentication, you will see the following error in the SharePoint ULS log:
SessionManager.CheckAndUpdateSessionsPerUser: Throwing MaxSessionsPerUserExceeded SessionException. User=NT AUTHORITY\ANONYMOUS LOGON Count=25
There is a separate setting for anonymous users that is not exposed in the Excel Services settings in the SharePoint Central Administration GUI. The setting is called SiteCollectionAnonymousSessionsMax. To modify the setting, go to Start > Programs > Microsoft SharePoint > SharePoint Management Shell. Run the following command:Get-SPExcelServiceApplication | format-list *Look for the SiteCollectionAnonymousSessionsMax setting, It will most likely be set to 25 as this is the default setting. Also note the value of DisplayName which refers to the name of the Excel Services application. To change the session setting, for example to 500 sessions, run the following command, replacing DisplayName with the DisplayName value:Set-SPExcelServiceApplication -identity DisplayName -SiteCollectionAnonymousSessionsMax 500
You may need to restart IIS. After making this change, users will not receive the error and should be able to view the Excel chart or graph successfully.
There appears to be an issue with SharePoint 2013 Excel Services when using IE 11 (11.0.9600.16438).
As noted in the comments below, the issue is resolved by installing the October 2013 Cumulative Update for SharePoint 2013:
Description of the SharePoint Server 2013 cumulative update package (SharePoint server-package): October 26, 2013 http://support.microsoft.com/default.aspx?scid=kb;EN-US;2825647
See this blog for detail of patch order for Sharepoint server:http://blogs.technet.com/b/stefan_gossner/archive/2013/10/26/october-2013-cu-for-sharepoint-2013-has-been-released.aspx
Basically what is happening is that when IE 11 is used with SharePoint 2013 Excel Services, the buttons across the top of the screen do not work (see screenshot.) The areas outlined in RED do not work correctly. Regular slicers do work as expected.
Microsoft is currently investigating the issue, but there is a temporary workaround for the issue. The workaround for this issue at the moment is to set the emulation document mode in Internet Explorer 11 to something other than "Edge (Default)".
To apply the workaround:
Configuring Kerberos for Excel Services is a two step process; Information Gathering and Configuration.
Step 1: Information Gathering:
1. Account running "Excel Services Application Web Service Application": In this example: ECSSvc.
Central Administration > Security > Configure service accounts
2. Account running "Claims to Windows Token Service": In this example: Local System.
3. Account running the data source you are connecting to:
SQL:
Log on to the server running SQL > Start > Run > Services.msc and locate "SQL Server (MSSQLSERVER)", you will see the account in the "Log On As" column. In this example: SQLSvc.
Analysis Services:
Log on to the server running SSAS > Start > Run > Services.msc and locate "SQL Server Analysis Services (<Instance>)", you will see the account in the "Log On As". In this example: SSASSvc.
Since the "Claims to Windows Token Service" is running as Local System, we need to record the names of the SharePoint Servers running "Excel Calculation Services". In this example: PRIME13.
Recap:
Important Note:
If “Excel Services” is running on multiple SharePoint machines and the C2WTS is running as Local System, you will need to Constrain each SharePoint machine (Netbios Name) running “Excel Services” to the backend Service Account(s) (SQLSvc and/or SSASSvc).If you are running the C2WTS as a domain account. For example Contoso\C2WTSSvc, you will only need to Constrain that one account to the backend Service Account(s) (SQLSvc and/or SSASSvc).
Step 2: Configuration:
What we need to do now is add the correct Service Principal Name to the Service Accounts running SQL/SSAS and then use Kerberos Constrained Delegation.
Service Principal Names (SPNs):
Add these SPNs to the account running the data source your workbook is connecting to. Either "SQL Server (MSSQLSERVER)" or "SQL Server Analysis Services (<Instance>)":
Account Running SQL Server (SQLSvc):
MSSQLSVC/<SQLServer_Name>MSSQLSVC/<SQLServer_Name.FQDN
Example:
MSSQLSVC/SQLSvrMSSQLSVC/SQLSvr.contoso.com
Account Running SQL Server Analysis Services (SSASSvc):
MSOLAPSvc.3/<SQL_Server_Analysis_Services_Server_Name>MSOLAPSvc.3/<SQL_Server_Analysis_Services_Server_Name.FQDN>
MSOLAPSvc.3/SSASSvrMSOLAPSvc.3/SSASSvr.contoso.com
Important Note: If Analysis Services has a named instance other than ("MSSQLSERVER)" (this is the default Instance) you will need include that in the Service Principal Name. In the below example, I will use the instance name "TABULAR".
MSOLAPSvc.3/<SQL_Server_Analysis_Services_Server_Name:Instance>MSOLAPSvc.3/<SQL_Server_Analysis_Services_Server_Name.FQDN:Instance>
MSOLAPSvc.3/SSASSvr:TABULARMSOLAPSvc.3/SSASSvr.contoso.com:TABULAR
Important Note 2: If the Analysis Services Server has a Named Instance other than (MSSQLSERVER) (which is Default), like "TABULAR". You will need to add MSOLAPDisco.3 SPNs to the account running the SQL Browser service (Example: Contoso\BrowserSvc). “Disco” is short for “Discovery”. This needs to be set so the Browser Service can discover SSAS.
MSOLAPDisco.3/<SQL_Server_Analysis_Services_Server_Name>MSOLAPDisco.3/<SQL_Server_Analysis_Services_Server_Name.FQDN>
MSOLAPDisco.3/SSASSvrMSOLAPDisco.3/SSASSvr.contoso.com
Article: More Information regarding the MSOLAPDisco.3 SPNs can be found in the below article:
An SPN for the SQL Server Browser service is required when you establish a connection to a named instance of SQL Server Analysis Services or of SQL Serverhttp://support.microsoft.com/kb/950599
Kerberos Constrained Delegation (KCD):
Constrain Delegation between the account running "Excel Calculation Service" and "Claims to Windows Token Service" to "SQL Server" and SQL Server Analysis Services"
"Excel Services" to SQL:
In Active Directory Users and Computers > ECSSvc > Properties > Delegation Tab > Trust this computer for delegation to specified services only > Use any authentication protocol > Add… > Users or Computers… > SQLSvc> Check Names > OK > “Select All” Available Services > OK > OK.
"Excel Services" to "Analysis Services":
In Active Directory Users and Computers > ECSSvc > Properties > Delegation Tab > Trust this computer for delegation to specified services only > Use any authentication protocol > Add… > Users or Computers… > SSASSvc > Check Names > OK > “Select All” Available Services > OK > OK
"Claims to Windows Token Service" (if running as Local System): to SQL:
In Active Directory Users and Computers > PRIME13 > Properties > Delegation Tab > Trust this computer for delegation to specified services only > Use any authentication protocol > Add… > Users or Computers… > SQLSvc > Check Names > OK > “Select All” Available Services > OK > OK.
"Claims to Windows Token Service" (if running as C2WTSSvc) to SQL:
In Active Directory Users and Computers > C2WTSSvc > Properties > Delegation Tab > Trust this computer for delegation to specified services only > Use any authentication protocol > Add… > Users or Computers… > SQLSvc > Check Names > OK > “Select All” Available Services > OK > OK.
"Claims to Windows Token Service" (if running as Local System) to "Analysis Services":
In Active Directory Users and Computers > PRIME13 > Properties > Delegation Tab > Trust this computer for delegation to specified services only > Use any authentication protocol > Add… > Users or Computers… > SSASSvc > Check Names > OK > “Select All” Available Services > OK > OK
In Active Directory Users and Computers > C2WTSSvc > Properties > Delegation Tab > Trust this computer for delegation to specified services only > Use any authentication protocol > Add… > Users or Computers… > SSASSvc > Check Names > OK > “Select All” Available Services > OK > OK.
Please see my Visio Diagram. It may take a few seconds to load since it is large.
If this fails please see our other blog:
Tools and Techniques: Troubleshooting Kerberos in Excel Services and PowerPivot for SharePointhttp://blogs.technet.com/b/excel_services__powerpivot_for_sharepoint_support_blog/archive/2013/02/08/troubleshooting-kerberos-for-excel-services-and-powerpivot.aspx
For awhile now, we here at PowerPivot for SharePoint Support have seen a variety of Claims Identity/Windows Token/UPN errors. Primarily with PowerPivot failing to refresh a workbook in the browser.
But recently, I noticed another product presenting this error.
10/29/2013 10:11:28.57 w3wp.exe (0x1DA4) 0x33F4 SharePoint Foundation Claims Authentication bz7l Medium SPSecurityContext.WindowsIdentity: Could not retrieve a valid windows identity for NTName='Domain\User', UPN='User@Domain.com'. UPN is required when Kerberos constrained delegation is used. Exception: System.ServiceModel.Security.SecurityAccessDeniedException: Access is denied. Server stack trace: at System.ServiceModel.Channels.ServiceChannel.ThrowIfFaultUnderstood(Message reply, MessageFault fault, String action, MessageVersion version, FaultConverter faultConverter) at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc) at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout) at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation) at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message) Exception rethrown at [0]: at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) at Microsoft.IdentityModel.WindowsTokenService.S4UClient.IS4UService_dup.UpnLogon(String upn, Int32 pid) at Microsoft.IdentityModel.WindowsTokenService.S4UClient.<>c__DisplayClass1.<UpnLogon>b__0(IS4UService_dup channel) at Microsoft.IdentityModel.WindowsTokenService.S4UClient.CallService(Func`2 contractOperation) at Microsoft.SharePoint.SPSecurityContext.GetWindowsIdentity().
In the GUI, you will see the error "Cannot convert identity to windows token."
Resolution:
Any user that is trying to refresh data, needs to have a check next to Read under Authenticated Users when looking at AD > Security (note: may need to enable Advanced Features under View tab so you can see the Security tab in AD).
You may receive the following warning dialog box when opening a workbook using Excel Services in SharePoint:
Be careful. This workbook contains one or more queries that might be unsafe. Do you want to enable these queries?
To suppress this and other warnings from Excel Services, go to SharePoint Central Administration > Application Management > Service Applications > Manage service applications > Excel Services application > Trusted File Locations and click on the Address that contains the workbook.
In the External Data section, under Warn on Refresh, uncheck Refresh warning enabled and click OK.
Users who access the workbook will no longer be presented with a warning dialog box.
Now that we have a PowerPivot workbook in the Excel Rich Client, we are ready to publish that workbook to SharePoint.
In "What is Power Pivot for SharePoint? Part 1" I discussed the Data Model and how it is a database inside of Excel. When you publish/upload a regular Excel workbook to SharePoint, that file gets chunked up into blobs and stored in the Content Database. With a PowerPivot workbook the Excel workbook also gets stored in the Content Database, BUT the Data Model gets created and stored (after the initial (successful) refresh in browser) on the server running POWERPIVOT.
Note: In SharePoint 2010, the Analysis Service POWERPIVOT instance is going to be on a SharePoint server in the farm. In SharePoint 2013, we recommend using a SQL Server off the farm. Step by step install instructions here.
I am now going to touch on the three refreshes of PowerPivot 2013 and how they work.
1. Browser Refresh
When you open workbook and click a slicer, you are merely accessing the Data Model. In other words, (in this example) the PowerPivot workbook stored in a SharePoint Report Library is going our to the SQL Server running PowerPivot > Databases > PowerPivot20Test20Workbook and is pulling whatever data (fresh or stale) that is currently in that cube.
If you login to SQL Management Studio > Analysis Services > Servername\POWERPIVOT > Databases you will see the Data Model (in this case PowerPivot20Test20Workbook_ae1bfdf63a44395b27a908c0ac0855_1f7abf568bb14cd4bb070b5eb858911c_SSPM):
Troubleshooting: I have seen the refresh in browser fail periodically. This was caused by not having the correct ASOLEDB and ADOMD.Net drivers (located here: for SQL 2008 R2 SP1 & SQL 2012 SP1) on the SharePoint Servers running Excel Services and/or the Claims to Windows Token Service not running on these machines/lacking proper permissions.
2. Scheduled Data Refresh
In SharePoint 2010, running the Scheduled Data Refresh is the only way to get fresh data into the PowerPivot workbook (this is designed to run once daily during "after business hours"). A timer job runs, the Data Model is updated with fresh data and workbook is republished back to SharePoint. When you open the workbook and click on a slicer, the Browser Refresh occurs a you will see data from the time the Scheduled Data Refresh ran (data from the time the Scheduled Data Refresh ran that night). In 2013, you are not 100% dependent on the "Scheduled Data Refresh" (if your backend data source accepts Windows Credentials; SQL or Analysis Services) to see fresh data. In 2013, you can use the "Interactive Data Refresh" which I will touch on next. If you want to refresh from a data source that does not accept Windows Credentials (Oracle, IBM (in some cases) SQL, etc.) you will need to use the Scheduled Data Refresh in both versions of SharePoint (2010 & 2013).
For this functionality to be present is SharePoint 2013, you need to install the PowerPivot.msi on a SharePoint server.
Troubleshooting: For detailed instructions on how to set this up please follow this article. For help pulling from a data source that does not accept Windows Credentials, please see our blog.
3. Interactive Data Refresh
The "Interactive Data Refresh" is only available in SharePoint 2013. The "Interactive Data Refresh" passes the SharePoint user's credentials all the way to the backend and pulls real-time data into a PowerPivot workbook. To do this, you need to choose Data > Refresh All Connections.
You can pull data from a data source that accepts Windows Credentials (SQL & SSAS). For steps on how to set this up, please see our blog.
I find myself explaining PowerPivot to customers over and over. Most understand parts of how it works but not the whole story. I will touch on two parts; PowerPivot for Excel and PowerPivot for SharePoint.
Part 1: PowerPivot for Excel
PowerPivot for SharePoint is nothing without the Excel Rich Client and the PowerPivot Add-in. Pre-Excel 2013, the PowerPivot Add-in was a separate COM Add-in that you had to download. In Excel 2013, the PowerPivot Add-in ships with the product.
Before you can do anything in SharePoint, you need to create a PowerPivot workbook in Excel. To do this, open the PowerPivot tab choose Manage > From Database (choose your datasource, servername and database name).
You (potentially) will be pulling in a large amount of data into Excel. I have seen people with Excel (PowerPivot) workbooks 1GB in size (which is huge since .xlsx (Open Office XML) files are zipped). This large amount of data is essentially a database and you can see this in the workbook. To view the Data Model, rename workbook file extension (.xlsx to .zip), open the workbook > xl > model > item.data.
After you have pulled this data (essentially a database) into the PowerPivot environment, Excel can point to this database and build a PivotTable off it. In the PowerPivot environment choose PivotTable > PivotTable
Excel is now pointing at the database (Data Model) embedded inside itself. You can see for yourself via Excel > Data Connections > Properties. In SharePoint (when we get there), we will call this the "Refresh in Browser".
Keep in mind there is still the PowerPivot Connection that can also refresh. In SharePoint, we will call this the "Scheduled Data Refresh". You can view this connection in Excel > PowerPivot > Manage > Existing Connections (choose the PowerPivot Data Connection) > Edit > Advanced.
There is still one more refresh (in SharePoint 2013 & SQL 2012 SP1) called the "Interactive Data Refresh". I will touch on that later in the "What is PowerPivot for SharePoint? Part 2".
At this point, we should have a beautiful Excel (PowerPivot) workbook that has a Pivot Table and Slicers and looks something like the below sample.
Now that we have a beautiful Excel (PowerPivot) workbook that functions wonderfully in the Excel Rich Client, we are ready to publish this to SharePoint!
Please see "What is PowerPivot for SharePoint? Part 2".
If you come across this error when trying to perform a scheduled data refresh:
"A schedule cannot be enabled for a workbook with no external data sources."
This is because the Data Model cannot be created on the PowerPivot Server.
Your probably have two questions.
1. What is a "Data Model".
The Data Model is a cube created under the PowerPivotServer\POWERPIVOT instance:
2.Why can't/isn't the Data Model being created?
The reason why this isn't being created is because there is something wrong with the Excel Services "Data Model Settings".
You can find this be going to the Central Administration > Application Management > Manage Service Applications > Excel Services > Data Model Settings.
At this location, you need to enter the proper PowerPivotServer\Instance
Here is the proper way:
Here is a bad example (no need to have just the server name present):
Here is another bad example (PowerPivot) is misspelled:
I hope this helps.
Tom
You may receive the following error when trying to access a SharePoint workbook as a data source using PowerPivot:
Failed to connecto to the server. Reason: Invalid internet address.
This error happens when accessing a data source in SharePoint using a URL. It is caused by a limitation of the data provider when accessing a data source over a web connection. Converting the URL to a UNC path fixes this error. For example, if you are using the following URL in the data source connection:
http://spserver/reporting/report2013.xlsx
change it to the following UNC path:
\\spserver\reporting\report2013.xlsx
This will resolve the error and allow PowerPivot to successfully connect to the SharePoint workbook as a data source.
Another error you may receive when trying to access a SharePoint workbook as a data source is the following:
Failed to connect to the server. Reason: Failure creating file.
This error occurs when accessing the data source using the UNC path to the workbook while the WebClient service is in a stopped state on the client machine. The WebClient service is required to make WebDAV connections.
To start the WebClient service, in Windows go to Start > Control Panel > Administrative Tools and open Services. Right-click the WebClient service and select Start. If the Start option is grayed out, the service may be disabled. To enable the service, right-click WebClient and select Properties, and under Startup type select Automatic and click Apply. Then click Start and click OK.
You may receive one of the following errors when attempting to view an Excel workbook in SharePoint depending on your version of SharePoint:
"This workbook is larger than the maximum workbook size allowed to be opened in the browser."
"Couldn't Open the Workbook. Wow, That's a big workbook. Unfortunately, we can't open a workbook larger than 10 MB. You'll need to open this in Excel."
The reason for these errors is the size of the workbook that is being opened is larger that the maximum size of a workbook that can be opened in Excel Services. The maximum size can be configured in the Excel Services settings.
In order to change this value, open SharePoint Central Administration, and go to Application Management > Manage Service Applications > ExcelServiceApp > Trusted File Locations and click on the appropriate Address. Under the Workbook Properties section you will see a Maximum Workbook Size option. Set this value to the required file size in MB, and click OK to save the setting. To avoid the errors above, make sure the value is higher than the largest workbook size in SharePoint.
Another error you may see in SharePoint with large workbooks is the following:
"Sorry, something went wrong. The form submission cannot be processed because it exceeded the maximum length allowed by the Web administrator. Please resubmit the form with less data."
You may encounter the error when you try to publish a large workbook to SharePoint that exceeds SharePoint’s Maximum Upload Size. You can increase this value by going to the following setting in SharePoint Central Administration:
Application Management > Web Applications > Manage web applications > (Choose Web Application) > General Settings > Maximum Upload Size
Increase the value to a value larger than the size of the workbook causing the error. After saving the value, you should be able to upload the file successfully.
Additional Articles:
Error "Request timed out" when you try to upload a large file to a document library on a Windows SharePoint Services 3.0 sitehttp://support.microsoft.com/kb/925083
Configure Maximum File Upload Size (PowerPivot for SharePoint)http://technet.microsoft.com/en-us/library/ff487972.aspx
Issues Uploading Large Files To SharePointhttp://blogs.technet.com/b/praveenh/archive/2012/11/16/issues-with-uploading-large-documents-on-document-library-wss-3-0-amp-moss-2007.aspx
Since Excel Services inception (SharePoint 2007) users have been attempting to consume a SharePoint List in Excel Services. Unfortunately this is not supported. I will explain why and I will also provide a workaround using PowerPivot.
If you are unsure whether or not you are using a SharePoint list as a data source, you can verify this by opening the workbook in Excel > Data > Connections > Properties > Definition tab. If the connection contains Provider=Microsoft.Office.List.OLEDB.2.0;Data Source="":ApplicationName=Excel;Version 12.0.0.0, you are trying to consume a SharePoint List (also see screenshot).
This connection is created by (the below example is using SharePoint 2013) List > Export to Excel:
Below is Microsoft's documentation regarding Unsupported Features in Excel Services.
Unsupported Features in Excel Services
http://msdn.microsoft.com/en-us/library/ms496823(v=office.12).aspx
When you have PowerPivot for SharePoint installed you get the option to “Export as Data Feed” from your SharePoint lists.
When using PowerPivot, there are 2 connections. The first one is the Excel connection and the second is the PowerPivot Connection.
Much like the previous screenshot, you can view the Excel connection by opening the workbook in Excel > Data > Connections > Properties > Definition tab
You can view the PowerPivot connection via Excel > PowerPivot tab > Manage
Choose Existing Connections
Click Edit > Advanced > and you will be able to see List.atomsvc in Connection String.
Once the data is in the PowerPivot Add-in then you can create a Pivottable off that set of data.
Once your Pivottable is finished you now can publish this workbook back to SharePoint in the PowerPivot Gallery.
Once the workbook is in the Gallery you can click on Manage data refresh to setup a daily refresh schedule.
To test the connection go to the manage data refresh page and choose "Enable" & "Also refresh as soon as possible".
Important Note 1: I have seen cases where the refresh runs and eventually times out. If you see this, you may need to grant the account running SQL Server Analysis Services (POWERPIVOT) Read permissions to the SharePoint list you are attempting to refresh.
After this runs, you will see fresh data.
Important Note 2: The Scheduled Data Refresh is designed to run once a day during after business hours when server is experiencing the least amount of stress. Therefore you will only see refreshed data once a day (versus real-time like when using Excel Services directly).
Using SharePoint lists as data sources with Excel Services (SharePoint Server 2010)http://technet.microsoft.com/en-us/library/gg576960.aspx
Using a SharePoint list as a data sourcehttp://powerpivotgeek.com/2010/10/28/using-a-sharepoint-list-as-a-data-source/
Using SharePoint List Data in PowerPivot Whitepaperhttp://download.microsoft.com/download/B/E/1/BE1AABB3-6ED8-4C3C-AF91-448AB733B1AF/SharePointListDataInPP.docx
Even though this is the PowerPivot and Excel Services blog, I felt that adding a blog on this common PowerView error was worthy of a post.
Many times I have seen customers with the error:
"Power View
An error occurred while loading the model for the item or data source '<filename.extension'>. Verify that the connection information is correct and that you have permissions to access the data source."
If you expand the "Show details", you will see Claims errors, but what does that mean?
<detail><ErrorCode xmlns="http://www.microsoft.com/sql/reportingservices">rsCannotRetrieveModel</ErrorCode><HttpStatus xmlns="http://www.microsoft.com/sql/reportingservices">400</HttpStatus><Message xmlns="http://www.microsoft.com/sql/reportingservices">An error occurred while loading the model for the item or data source 'http://microsoft/sites/bidemo/PowerPivot/Tabular.bism'. Verify that the connection information is correct and that you have permissions to access the data source.</Message><HelpLink xmlns="http://www.microsoft.com/sql/reportingservices">http://go.microsoft.com/fwlink/? inkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsCannotRetrieveModel&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=11.0.3349.0</HelpLink><ProductName xmlns="http://www.microsoft.com/sql/reportingservices">MicrosoftSQL Server Reporting Services</ProductName><ProductVersion xmlns="http://www.microsoft.com/sql/reportingservices">11.0.3349.0</ProductVersion><ProductLocaleId xmlns="http://www.microsoft.com/sql/reportingservices">127</ProductLocaleId><OperatingSystem xmlns="http://www.microsoft.com/sql/reportingservices">OsIndependent</OperatingSystem><CountryLocaleId xmlns="http://www.microsoft.com/sql/reportingservices">1033</CountryLocaleId><MoreInformation xmlns="http://www.microsoft.com/sql/reportingservices"><Source>ReportingServicesLibrary</Source><Messagemsrs:ErrorCode="rsCannotRetrieveModel" msrs:HelpLink="http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsCannotRetrieveModel&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=11.0.3349.0"xmlns:msrs="http://www.microsoft.com/sql/reportingservices">An error occurred while loading the model for the item or data source 'http://microsoft/sites/bidemo/PowerPivot/Tabular.bism'. Verify that the connection information is correct and that you have permissions to access the data source.</Message><MoreInformation><Source>Microsoft.ReportingServices.ProcessingCore</Source><Message msrs:ErrorCode="rsErrorImpersonatingUser" msrs:HelpLink=http://go.microsoft.com/fwlink/? inkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsErrorImpersonatingUser&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=11.0.3349.0 xmlns:msrs="http://www.microsoft.com/sql/reportingservices">Cannotimpersonate user for data source 'TemporaryDataSource'.</Message><MoreInformation><Source>Microsoft.ReportingServices.ServiceRuntime</Source><Message msrs:ErrorCode= "rsClaimsToWindowsTokenError" msrs:HelpLink=http://go.microsoft.com/fwlink/? inkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsClaimsToWindowsTokenError&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=11.0.3349.0 xmlns:msrs= "http://www.microsoft.com/sql/reportingservices">Cannot convert claims identity to windows token. </Message><MoreInformation> <Source>Microsoft.SharePoint</Source><Message>Could not retrieve a valid Windows identity.</Message><MoreInformation><Source>mscorlib</Source><Message>Access is enied.</Message></MoreInformation> </MoreInformation></MoreInformation></MoreInformation></MoreInformation><Warnings xmlns=http://www.microsoft.com/sql/reportingservices /></detail>
If you want to analyze this more you can use Rodney Viana's tool to analyze the C2WTS:
Troubleshooting Claims to Windows NT Token Service (c2WTS) in SharePoint 2010 may be difficult if you don’t know where to start
http://blogs.msdn.com/b/rodneyviana/archive/2011/07/19/troubleshooting-claims-to-windows-nt-token-service-c2wts-in-sharepoint-2010-may-be-difficult-if-you-don-t-know-where-to-start.aspx
I ran Rodney's tool and saw:
Testing Service c2WTS+- Service c2WTS found+- Service c2WTS is running+- Path of service: C:\Program Files\Windows Identity Foundation\v3.5\c2wtshost.exe+- Config File: C:\Program Files\Windows Identity Foundation\v3.5\c2wtshost.exe.config+- Service Logon: SYSTEM\NT AUTHORITY----- start of config file ----<?xml version="1.0"?><configuration> <configSections> <section name="windowsTokenService" type="Microsoft.IdentityModel.WindowsTokenService.Configuration.WindowsTokenServiceSection, Microsoft.IdentityModel.WindowsTokenService, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" /> </configSections> <startup> <supportedRuntime version="v4.0" /> <supportedRuntime version="v2.0.50727" /> </startup> <windowsTokenService> <!-- By default no callers are allowed to use the Windows Identity Foundation Claims To NT Token Service. Add the identities you wish to allow below. --> <allowedCallers> <clear /> <add value="WSS_WPG" /> </allowedCallers> </windowsTokenService></configuration>----- end of config file ----Retrieving security groups/users allowed to use the service from config file+- WSS_WPGTrying to login .........Using current Windows Credentials***** c2WTS could not provide a valid Windows Token. Reason: WTS0003: The caller is not authorized to access the service.
Server stack trace: at System.ServiceModel.Channels.ServiceChannel.ThrowIfFaultUnderstood(Message reply, MessageFault fault, String action, MessageVersion version, FaultConverter faultConverter) at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc) at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout) at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation) at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)
Exception rethrown at [0]: at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) at Microsoft.IdentityModel.WindowsTokenService.S4UClient.IS4UService_dup.UpnLogon(String upn, Int32 pid) at Microsoft.IdentityModel.WindowsTokenService.S4UClient.<>c__DisplayClass1.<UpnLogon>b__0(IS4UService_dup channel) at Microsoft.IdentityModel.WindowsTokenService.S4UClient.CallService(Func`2 contractOperation) at c2WTSTest.Form1.button2_Click(Object sender, EventArgs e)
Now Verifying if user msft\test has rights on c2WTS+- User msft\test has no access to the service*** Analysis Complete ***
The first highlighted item tells me the account that was running the C2WTS: SYSTEM\NT AUTHORITY
The second highlighted item gives me the error: c2WTS could not provide a valid Windows Token. Reason: WTS0003: The caller is not authorized to access the service.
The error tells me that the account running the Claims to Windows Token Service lacks permissions/security.
Steps to resolve the issue.
I changed the Claims to Windows Token Service to a managed account and then applied the below changes (Note: you can attempt to apply the below changes to the existing account running the C2WTS):
a. Add the service account to the local Administrators Groups.
b. In local security policy (secpol.msc) under user rights assignment give the service account the following permissions:
i. Act as part of the operating system
ii. Impersonate a client after authentication
iii. Log on as a service
Restart IIS
After making this change the error went away.
When opening a workbook after a Schedule Data Refresh, the workbook does not open in Excel Services:
This is because after a Scheduled Data Refresh, the “Refresh data when opening the file” is checked. This is by design, because after a Scheduled Data Refresh, the workbook’s Data Model is updated with new information and the workbook is re-published to the SharePoint Library. To ensure that you are getting fresh data in the browser, the “Refresh data when opening the file” is checked. (I will get to the reason why it is spinning later under the Cause section).
If we uncheck “Refresh data when opening the file” and open the workbook in the browser, the workbook opens, but if we click on a slicer (a workbook refresh is triggered), the error “Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. Thefollowing connections failed to refresh: PowerPivot Data” is thrown:
This is caused by a Bug. When the Scheduled Data Refresh runs, a Process cube is created under the SharePoint Server running PowerPivot > Analysis Services > PowerPivot Instance > Databases. The Process cube should be deleted, but it is not. Therefore many (not sure I can call them “duplicate” since they have a unique GUID so I will use the term “multiple”) “multiple” Data Models are created and not deleted. When the workbook is refreshed, the workbook looks for the Data Model associated with that workbook under the SharePoint Server running PowerPivot > Analysis Services > PowerPivot Instance > Databases. As you can see there are many “multiple” Data Models and the workbook refresh either hangs (spinning when using “Refresh data when opening the file”) or fails when clicking on a slicer (error “Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. The following connections failed to refresh: PowerPivot Data).
To resolve this, you will need to upgrade all servers running PowerPivot to SQL SP1 (11.0.3000.0) and apply the Cumulative Update 4.
Microsoft® SQL Server® 2012 Service Pack 1 (SP1)http://www.microsoft.com/en-us/download/details.aspx?id=35575
Cumulative update package 4 for SQL Server 2012 SP1 http://support.microsoft.com/kb/2833645/en-us
FIX: PowerPivot database is not deleted from SSAS memory when PowerPivot database processing failshttp://support.microsoft.com/kb/2820918
The SQL Server 2012 builds that were released after SQL Server 2012 Service Pack 1 was releasedhttp://support.microsoft.com/kb/2772858
Much of the existing content for converting a PowerPivot workbook to a SQL Server Analysis Services (SSAS) tabular instance assumes that the reader is a developer. This post details the process of upsizing a PowerPivot workbook to SSAS from the point of view of a knowledge worker and or a SharePoint administrator.
This post assumes you are using PowerPivot as a data source, so no UI is present in the workbook hosting the PowerPivot model. The display of the data would happen from another workbook that connects to the PowerPivot workbook as a data source or from some other reporting tool like PowerView or Excel Services.
What is upsizing PowerPivot (aka convert PowerPivot to tabular model, aka convert PowerPivot to SSAS):
Upsizing PowerPivot is simply moving the solution from a SharePoint centric storage mode to a SSAS centric storage mode. The end result is that the PowerPivot model and data will not be stored in SharePoint. PowerPivot stores models in Excel Workbooks when using a SharePoint centric mode and stores the models in native SSAS files when using SSAS storage mode.
The key takeaway is that the same query engine is used regardless of where the model is stored, Excel client, SharePoint or SSAS.
Why upsize a PowerPivot workbook:
You may want to consider upsizing your PowerPivot workbook after it exceeds 100MB in size for the following reasons.
Issues to consider before upsizing:
Steps to upsize a PowerPivot workbook:
Congratulations you have now upsized your PowerPivot model to a powerful full featured SSAS database.
When opening an Excel workbook (.xlsx) in the browser the error "The workbook cannot be opened" is thrown:
This is caused by the account running Excel Services not having proper permissions to the Content Database. When you upload an Excel workbook to a SharePoint library, the workbook is stored as blobs in the Content Database. When you choose to open this in Excel Services, the account running Excel Services needs to retrieve and reassemble the workbook. If the account running Excel Services lacks proper permissions, "The workbook cannot be opened".
We have a KB on how to resolve this issue:
The Excel Services Application for SharePoint 2010 does not load or display workbooks
http://support.microsoft.com/kb/981293
In this blog, I will step you through this KB; where to collect data and explain what the two below SharePoint Management Shell Commands do:
$w = Get-SPWebApplication –Identity <URL of the Web application>$w.GrantAccessToProcessIdentity("<insert service account>")
You need to find the <URL of the Web application>. To do this:
1. Browse to the report library where the failing workbook is located and make note of the URL.
2. Central Administration > Application Management > Manage web applications compare the URL to the list of Web Apps. Find the correct Web App URL and add it to “URL of the web application”.
You now have the first command:
$w = Get-SPWebApplication –Identity http://tschauer
3. Central Administration > Security > Configure Service Accounts > from the first dropdown, select the application pool running "Excel Services Application" and add that account where is says “<insert service account>”.
You now have the second command:
$w.GrantAccessToProcessIdentity("Devtest\Excel_Services_Account")
So now you can run these commands by following the below steps:
1. Click Start, click All Programs.2. Click Microsoft SharePoint 2010/2013 Products.3. Click SharePoint 2010/2013 Management Shell.4. At the Windows PowerShell command prompt (PS C:\>), type the following command, and then press ENTER:
$w = Get-SPWebApplication –Identity http://tschauer$w.GrantAccessToProcessIdentity("Devtest\Excel_Services_Account")
When you run these commands you are:
1. Adding the account running Excel Services as a User to the Content Database (Security > Users).2. Giving the account running Excel Services the SPDataAccess Membership (Account > Membership)3. Giving the account running Excel Services Full Control to the Web Application (Via "Policy for Web Application")
With SharePoint 2010, you might run into an issue when attempting to create a new PowerPivot Gallery Document from PowerPivot gallery, like this:
If you encounter this issue, you may then see the following to errors:
The reason for the error is that SharePoint is looking for the template .xlsx file in the wrong location.
To resolve the issue:
Now the path to the template.xlsx folder will be correct, and you shouldn’t get any errors when creating new PowerPivot Gallery Documents from PowerPivot gallery.
You might see a scenario where data refresh with PivotTables or slicers or Data > Refresh All Connections are not working, and you are seeing an error in the browser like this:
You may also find a corresponding error in the SharePoint logs like this one:
“MossHost.TryGetWindowsIdentity: Current identity is ClaimsIdentity. We need to get a WindowsIdentity”
With PowerPivot workbooks viewed in the browser via Excel Services, in order for PivotTables or slicers or Data > Refresh All Connections to work, the C2WTS is utilized. If the C2WTS is being run by a domain account (Microsoft recommended best practice), then that domain account needs a few certain permissions on each of the SharePoint servers where it is running:
To access a server’s Local Security Policy, go to Start > Administrative Tools > Local Security Policy > Local Policies > User Rights Assignment
If you do add any of the permissions above for the account that is running the C2WTS, then you will need to restart the C2WTS on each of the SharePoint servers where it is running.
If the account that is running the C2WTS is LOCAL SYSTEM (not recommended), then no further permissions are required.
The following applies to SharePoint 2013:
1. Restarting ECS will force a wipe and reload of PowerPivot databases.
2. Any service account needing to interact with the PowerPivot data model needs to be an Administrator on the PowerPivot AS instance. This is less obvious when you are in a client application but consider these scenarios:
a. PowerView client (in Excel) > SQL Reporting Services service application needs to be an admin
b. Performance Point > Performance Point Service application needs to an admin
3. This table shows backward compatibility for workbooks in SharePoint 2013 and what service application performs a scheduled data refresh. Interactivity refers to the user-level interactivity allowed with the PowerPivot data model. Only Excel 2013 workbooks allow for full permissions, which equates to full back-end connection-based data refresh in browser (which means writing/updating the data model in PowerPivot-AS):
* Note: Data refresh schedules for PowerPivot 2012-Excel 2010 workbooks, the PowerPivot System Service account requires modify rights to the workbook in SharePoint as well as admin rights on the PowerPivot-AS server.
We have been seeing some issues with customers using PowerPivot workbooks as a source file in new Excel PowerPivot files. There are a variety of errors that can arise from this process, and this blog is intended to provide you with some troubleshooting steps to resolve the issue.
You may or may not be aware, but it is possible to use a PowerPivot workbook as a data source for other data applications. For instance, one can use Excel and connect to a PowerPivot file on a SharePoint site as a PowerPivot data source. There may be a file on SharePoint site named BIFinance.xlsx. When creating a new PowerPivot workbook, we could specify our data source as the following:
http://sharepointsite/PowerPivot Gallery/BIFinance.xlsx
Fig 1: Using PowerPivot workbook as a data source
In a nutshell, when the data source is the PowerPivot Mid-Tier hosted PowerPivot workbook, the connection goes through a Redirector service on the SharePoint server and is ultimately routed to the SQL Server Analysis Services PowerPivot instance.
For more information on how to use PowerPivot workbooks as a Data Source, please see the following video:
Using PowerPivot Workbooks as a Data Sourcehttp://technet.microsoft.com/en-us/sqlserver/dn151361.aspx
A few sample errors that may arise when attempting to connect to the PowerPivot workbook as a data source:
Possible scenarios that might cause this process to fail:
The steps above should resolve most of the errors that are seen when attempting to use a PowerPivot workbook as a data source within Excel PowerPivot.
I have troubleshot many Kerberos cases over the years and here are the best techniques and tools that I have used over the years.
1. Kerberos Event Logging (KB here):
Add the following registry value to each machine in the farm that receives Kerberos Traffic:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parameters
Registry Value: LogLevel Value Type: REG_DWORD Value Data: 1
To see the results, open Event Viewer > Windows Logs > System
You will now see Kerberos Errors in Event Viewer:
You will see a variety of errors such as:
KDC_ERR_S_PRINCIPAL_UNKNOWN: This means one of two things:
1. You are missing the listed SPN and need to add it to the correct account.
2. There is a Duplicate SPN effecting Kerberos Delegation, to analyze the Delegation use the tool listed below, DHCheck).
KDC_ERR_ETYPR_NOT_SUPP: This too means one of two things:
1. Delegation in SharePoint 2010 only supports Constrained Delegation, I have seen where one account is Constrained and another is not, this causes Delegation to fail and this error is thrown.
2. This is a bug with the Kerberos.dll (see my other blog).
2. DHCheck (DoubleHopCheck):
6366.DHCheck.zip
This tool is great, it will inform you if the account is Trusted for Delegation, the SPNs Registered to the Account, and the SPNs the Account is Constrained too. Oh, and most importantly Duplicate SPNs.
A file named "results.txt" will be placed in the C:\temp\ folder, when you open it, it will look like the below:
Distinguished name..............: CN=Account01,OU=SharePoint_Servers,OU=SharePoint_Enterprise,OU=Domain Servers,DC=AD,DC=Microsoft,DC=comAccount type....................: ComputerUser Account control............: 16781344(DEC) 1001020(HEX)Account Trusted for delegation..: FalseAccount sensitive for delegation: FalseConstrained delegation is enabled for:MSOLAPSvc.3/Server01:InsanceMSOLAPSvc.3/Server01.FQDN:InsanceRegistered Service Principal Names:HTTP/Server01HTTP/Server01.FQDNDuplicate SPN found: HTTP/Server01Account01,CN=Account01,OU=Users01,OU=IT,OU=ABC,OU=AdminUnits,DC=,DC=Microsoft,DC=comAccount02,CN=Account02,OU=Users02,OU=IT,OU=123,OU=AdminUnits,DC=MSFT,DC=Microsoft,DC=com
To use this tool:
1. Rename the attachment from dhcheck.txt to dhcheck.vbs and save it on the Application Server (root of C:).
2. Open a command line window and browse to the same directory as dhcheck.vbs (root of C:) enter the below information into the command line and press enter.
cscript dhcheck.vbs Account1 Account2 Account 3 > c:\temp\results.txt
*The accounts in Red will be the account running Excel Services, Claims to Windows Token Services, SSAS and/or SQL (any accounts you want to collect Delegation information on).
I have worked with many customers who are seeing refresh failures in the Excel Web Access Web Parts in their TFS Dashboard.
In most cases, this is caused by not having a Secure Store Service ID set up for Team Foundation Server and that SSS ID has not been added to the Enterprise Application Definition. Follow the steps below to configure your environment.
Step One:
Set up a Secure Store Services ID. Here are the steps to create a Secure Store Services ID:
Central Administration > Manage Service Applications > Secure Store Service > New
Target Application Settings:Application ID: tfsDisplay Name: tfsContact E-mail: AdministratorTarget Application Type: GroupTarget Application Page URL: NoneNext
Field Name: Windows User NameField Type: Windows User NameMasked: No
Field Name: Windows PasswordField Type: Windows PasswordMasked: YesNext
Target Application Administrators: AdministratorMembers: example: “SecureStoreUsers” (This will be an AD group that you create. Many companies will create certain groups for certain resources. Example: One company may create an Application ID called "SSSAccounting" and have an AD group called "Accounting" that they make a member.)Next
Select the SSS ID "tfs" > Set Credentials:Windows Name: (this account needs to have access to the backend data).
Step Two: Add the Enterprise Application Definition:
Note: For all the workbooks to be updated, you need to wait about 30 minutes. I am not sure what Timer Job runs to update there workbooks. Here is the best documentation I could find.
Team Foundation Server Timer Job for SharePoint Products
Project portals may contain Excel workbooks that have a connection to the Team Foundation Server data stores. The administrator of a Team Foundation Server instance can change the reporting configuration for the instance. For example they could enable/disable reporting, change the location of the SQL Server Analysis Services cube or change the Enterprise Application Definition for sites within a given SharePoint Web application.
To keep these connections pointing to the correct location or to keep the Excel Services Authentication Settings matching the Enterprise Application Definition, a timer job for SharePoint Products runs on each SharePoint Web application that contains project portals. The timer job periodically scans the portal sites in the Web application and updates the workbook details if it finds that the connection or authentication details have changed.
If you are adding additional workbooks to a project portal and want their connections to be updated by the timer job then you should place them in the same document library as the Excel workbooks for Team Foundation Server. Note that the name of the connection in the workbook must be "TfsOlapReport" if it is to be updated by the timer job.
Customizing Team Foundation Server Project Portalshttp://msdn.microsoft.com/en-us/library/ff678492.aspx
I have had several cases where a customer will have Kerberos properly configured but refresh in the browser fails.
In the UI, the error will appear.
"Unable to get the required information about this cube. The cube might have been reorganized or changed on the server. The PivotTable was not refreshed."
IMPORTANT: I have also seen the standard “Data Refresh Failed” error:
There are a variety of places you can look to see if this is indeed an error with the Kerberos.dll:
1. UDL (Universal Data Link)2. ULS Logs3. Event Viewer4. Network Trace
There is one common theme you will notice across all these results, NOT SUPPORTED.
1. Test a UDL to the datasource from the server running Excel Services.
*Since the OS is 64 bit, you will need to call it a certain way (see below):
On the server you are running Excel Calculation Services on please perform the following:1. Right click on your desktop (or wherever you are having the problem) and create a new text doc.2. Rename it to .udl (make sure you do NOT have on "hide known extensions") Save > Close > Place this on the Root of C:. Execute the command below from a command line or Start/Run: C:\Windows\syswow64\rundll32.exe "C:\Program Files (x86)\Common Files\System\Ole DB\oledb32.dll",OpenDSLFile C:\test.udl3. Double click, choose Microsoft OLE DB Provider for Analysis Services 10.0 on the first tab (Provider).4. On the 2nd tab (Connection) enter the AS Server name (or AS servername\instance for a named instance) in the Data Source box. Leave Location empty. For the server name enter the shortname/netbios name.5. On the 2nd tab (Connection) choose "Use Windows NT Integrated security".6. Now go to the 4th tab (All) and scroll down to highlight SSPI. Click on "Edit Value" and enter Kerberos as the Property Value.7. Go back to the 2nd tab (Connection) and choose "Test Connection".
If you see the error, "Test connection failed because of an error in initializing provider. The following system error occurred: The function requested is not supported.", you need to upgrade the kerberos.dll (see KB 969083).
2. ULS logs.
If you see the below error in the ULS Logs, you need to upgrade the kerberos.dll (see KB 969083).
"OLEDBConnection::InitConnection: The following system error occurred: The function requested is not supported."
3. Event Viewer (turn on Kerberos Event Logging here, reproduce the error in the browser, then look in Event Viewer > Windows Logs > System):
If you see the error "KDC_ERR_ETYPE_NOTSUPP" in the Event Viewer, you need to upgrade the kerberos.dll (see KB 969083).
4. Network Trace.
If you see the error "NT Status: STATUS_NOT_SUPPORTED" in a Network Trace, you need to upgrade the kerberos.dll (see KB 969083).
To resolve all the above errors, apply the Hotfix in the below KB to the machines receiving Kerberos traffic (including the database; SQL & SSAS).
A Kerberos authentication fails together with theerror code 0X80090302 or 0x8009030f on a computer that is running Windows Server 2008 or Windows Vista when the AES algorithm is used
http://support.microsoft.com/kb/969083
A real easy thing to do is look at the Kerberos.dll in C:\Windows\System32\kerberos.dll > right click > Properties > Details
It needs to be at least 6.0.6002.22138. If it is not, apply the above Hotfix.