PowerPivot with SharePoint Diagnostics
There are a lot of moving parts to PowerPivot. Here are some common causes of PowerPivot not working within SharePoint:
No associated Service Application
I list this first, because I spent way too much time finding this one. There is a Service Application created in support of PowerPivot. If your web application doesn’t have a proxy association to this Service Application, you will get a generation connection error when trying to view a PowerPivot spreadsheet within Excel Services.
Data Connection Library trust
If you use an external (ie ODC) data connection in a library, that library needs to be trusted.
Trusted file location
The location where the PowerPivot spreadsheet is located needs to be trusted.
Install the appropriate SSAS OLAP library
For SQL Server 2008 R2 SP1, the correct library is MSOLAP.4, for SQL Server 2012, the correct library is is MSOLAP.5. If you open the ODC data connection referenced by your PowerPivot spreadsheet, at the beginning you will find a reference to this library.
Ensure SSAS is running under a domain account
There are two solutions, and both need to be deployed. Here’s the PowerShell:
Add-SPSolution –LiteralPath “C:Program FilesMicrosoft SQL Server110ToolsPowerPivotToolsConfigurationToolResourcesPowerPivotFarm.wsp” Install-SPSolution –Identity PowerPivotFarm.wsp –GACDeployment -Force $centralAdmin = $(Get-SPWebApplication -IncludeCentralAdministration | Where { $_.IsAdministrationWebApplication -eq $TRUE}) Add-SPSolution –LiteralPath “C:Program FilesMicrosoft SQL Server110ToolsPowerPivotToolsConfigurationToolResourcesPowerPivotWebApp.wsp” Install-SPSolution -Identity PowerPivotWebApp.wsp -GACDeployment -Force -WebApplication $centralAdmin |
If you want to check the deployment status, try this command:
Get-SPSolution "powerpivotfarm.wsp" | Format-List |
As background, there is a deployment job triggered with deploying the solution. This job is managed under the Timer Job Service, which you can check on:
Get-SPTimerJob -Type Microsoft.SharePoint.Administration.SPSolutionDeploymentJobDefinition | Format-List |
Next, go into Farm Solutions in Central Admin, and deploy both solutions. What do these solutions do?
The Powerpivotfarm.wsp solution does the following:
Adds Microsoft.AnalysisServices.SharePoint.Integration.dll to the global assembly.
Adds Microsoft.AnalysisServices.ChannelTransport.dll to the global assembly.
Installs features and resources files, and registers content types.
Adds library templates for PowerPivot Gallery and Data Feed libraries.
Adds application pages for service application configuration, PowerPivot Management Dashboard, data refresh, and PowerPivot Gallery.
The Powerpivotwebapp.wsp solution does the following:
Adds Microsoft.AnalysisServices.SharePoint.Integration.dll resources files to the web server extensions folder on the Web front-end.
Adds PowerPivot Web service to the Web-front end.
Adds thumbnail image generation for PowerPivot Gallery.
Ensure OLE DB providers are available
If you are running SQL Server 2008 R2 SP, then the OLE DB provider should have been installed when SharePoint was installed. You can find it in the GAC called “microsoft.analysisservices.xmla.dll”. GAC is in C:windowsassembly
SQL Server 2008 R2: MSOLAP100.dll, within the data connection string it is called MSOLAP.4, and the version (when you examine properties of the file in the GAC) is 10.50.1600 or later)
SQL Server 2012: MSOLAP110.dll, within the data connection string it is called MSOLAP.5, and the version (when you examine properties of the file in the GAC) is 11.00.0000 or later)
Ensure the OLE DB provider is trusted by Excel Services
Check within Central Admin, Manage Service Applications, Excel Services, Trusted Data Providers lists MSOLAP.4 and/or MSOLAP.5, depending on which version of SQL Server you are using.
If it is not listed, click Add Trusted Data Provider, in the Provider ID, type MSOLAP.5.
For Provider Type, ensure that OLE DB is selected. In Provider Description, type Microsoft OLE DB Provider for OLAP Services 11.0.
Ensure CTWTS is started
The Claims to Windows Token Service is required to communicate and authenticate seamlessly with SQL Server. This service has to be started. In Central Admin, go to Services on Server, and start it up.
Ensure CTWTS is running as LOCAL
The Claims to Windows Token Service needs to run using the LOCAL builtin account. You can see this by starting services.msc on each SharePoint server.
Ensure Secure Store Service is configured
This Service Application needs to be configured (it has its own database), running, associated with your web application, with an Application ID defined that maps to an AD account. I prefer configuring as a “Group” entry. You’ll need to enter credentials for this Application ID, and also grant permissions to the users who will reference the Secure Store Application ID, which is set within the data connection for the Authentication configuration.
Ensure Excel Services is running and associated
PowerPivot makes use of Excel Services. This Service Application must be configured, and associated with your web application. The data connection library (if used) and file location must be trusted. Excel Services should also be configured to allow PowerPivot workbooks of sufficient size to render. Note I have seen a bug in early versions of SharePoint 2013 where increasing the maximum workbook size does not allow larger workbooks to render.
Ensure XLSX renders in Excel Services
This one is a real gotcha” if you are running SharePoint 2013 with an Office Web Apps farm. By default Office Web Apps renders spreadsheets, and cannot handle external data connections. if the URL contains “_layouts/15/WopiFrame.aspx” then the spreadsheet is being rendered by Office Web Apps. The tricky part is that other than the URL, Office Web Apps rendered spreadsheets look virtually identical to the Excel Services rendered equivalent. Under Excel Services the URL would instead contain “_layouts/15/xlviewer.aspx”. The fix for this is a single PowerShell command:
New-SPWOPISuppressionSetting -Extension XLSX -Action View |
Ensure Analysis Services Account can act as O/S
The Service account running the PowerPivot dedicated Analysis Services on the SharePoint server can fail to refresh unless the following steps are taken:
- Run “secpol.msc”
- Click Local Security Policy, then click Local policies, and then click User rights assignment.
- Add the service account.
Check the ULS
When all fails, the ULS can be a lifesaver. While often cryptic or hyperventilating about some minutia, it still can very clearly indicate the source of a problem
Want to talk?
Drop us a line. We are here to answer your questions 24*7.