Verifying the Excel Services Configuration for PowerPivot in SharePoint 2013

In SharePoint 2013, Excel Services natively supports core PowerPivot functionality. This is an important improvement over previous releases. Without requiring an extra installation of PowerPivot components in the farm, users can interact with workbook data models in the browser. You only need to register a SQL Server Analysis Services (SSAS) server in the Excel Services configuration, as in the following screenshot, so that Excel Services can load and query the data models.

The specified SSAS server can reside anywhere in the local TCP/IP network but must exist in the same Active Directory forest as the SharePoint farm. The Excel Services service account must be granted server administrator permissions in the Analysis Services configuration. Furthermore, for SharePoint 2013 Preview, the specified server must run SQL Server 2012 SP1 CTP3 Analysis Services and the server must operate in SharePoint deployment mode. Note that SQL Server 2012 RTM Analysis Services and previous versions are not sufficient because these versions don’t support the new level of integration between Excel Services and Analysis Services.

When you register a SSAS server in Excel Services, it might take two or three minutes for the configuration changes to take effect. Of course, you can make the system apply the changes immediately by restarting Internet Information Services (IIS) using IISReset /NoForce or by restarting the Excel Services application in SharePoint Central Administration. I prefer IISReset because it’s uncomplicated. But what if Excel Services continues to tell you that data models cannot be loaded, as in the next screenshot? By following a few basic steps, you can quickly examine the Excel Services configuration and identify possible root causes.

Note In order to verify that Excel Services can load a workbook data model, upload and open a sample workbook in the browser, and then click on a slicer. Excel Services typically shows cached data without loading the data model when opening a workbook. Clicking on a slicer requires Excel Services to update the data cache, which in turn requires actually loading the data model.

As always in SharePoint, start troubleshooting by checking the ULS logs. You can use UlsViewer, which is a handy tool available at https://code.msdn.microsoft.com/ULSViewer, or use the Get-SPLogEvent cmdlet in the SharePoint Management Shell, or simply open the ULS log files in Notepad. If you use the Get-SPLogEvent cmdlet, start the Management Shell as Administrator and then set a filter for events from “Excel Services Application”, Category “Data Model”, and perhaps specify other criteria as well, such as the verbosity level, to narrow down the list of events that you must investigate. For example, I used the command Get-SPLogEvent | ?{$_.Area -eq "Excel Services Application" -And $_.Category -eq "Data Model" -And $_.Level -eq "Monitorable" } | Format-List to produce the output illustrated in the following screenshot.

The message in my example contains the error hint “Can't operate on empty server pool. ” which means that Excel Services doesn’t have a server to load the data model. The solution is straightforward. Just register a SSAS server in the Data Model Settings of Excel Services and the server pool is no longer empty. Make sure you specify the SSAS server name correctly. Don’t forget to include the instance name, which is typically called POWERPIVOT. So, a server named AS2012SP1 running Analysis Services in SharePoint deployment mode would be specified as AS2012SP1\POWERPIVOT.

Note As a best practice, do not use the label “localhost” or “.” in the SSAS server name. These labels refer to the local computer, which means that each Excel Services instance in a multi-machine farm would interpret the SSAS server name differently. Always specify the actual server name.

If you happen to mistype the server name, Excel Services writes a different error message to the ULS log, shown in the next screenshot. The message would state “There are no servers available or actively being initialized” which means that Excel Services knows about a SSAS server, but this server can currently not be located. The command Get-SPLogEvent | ?{$_.Area -eq "Excel Services Application" -And $_.Category -eq "Data Model" } | Format-List would include this ULS log entry in its results.

It is important to point out that a mistyped server name is only one of many possible causes of this error message. The server name isn’t necessarily incorrect. The server might just be down or rebooting for some reason. This could be a temporary situation that doesn’t require any configuration adjustments. Of course, it could also be a more permanent issue on the SSAS server, such as an incorrectly configured firewall blocking incoming connections. See if the ULS log contains additional hints. In the following screenshot, the error message states that the SQL Browser service is inaccessible. Opening the firewall solves this problem. For details about firewall configurations for Analysis Services, check out the article “Configure the Windows Firewall to Allow Analysis Services Access” at https://msdn.microsoft.com/en-us/library/ms174937.aspx.

Having fixed the firewall configuration, the next common source of problems revolves around the service account permissions in Analysis Services. As mentioned, the Excel Services service account must be granted Analysis Services administrator permissions. If this is not the case, you can find the following error message in the ULS Log.

The message “Check Administrator Access (AS2012SP1\POWERPIVOT): Fail. ” means that the Excel Services service account does not have the required permissions on the Analysis Services instance AS2012SP1\POWERPIVOT. If you grant the permissions in SQL Server Management Studio (SSMS), the administrator access check will pass, as hopefully will do all the other Analysis Services server checks that Excel Services performs at regular intervals. Here’s a successful sequence of all the server checks taken from the ULS log on a properly configured and functioning SharePoint 2013 application server:

  1. Checking Server Configuration (AS2012SP1\POWERPIVOT) ...
  2. Check Administrator Access (AS2012SP1\POWERPIVOT): Pass.
  3. Check Server Version (AS2012SP1\POWERPIVOT): Pass (11.0.2809.24 >= 11.0.2800.0).
  4. Check Deployment Mode (AS2012SP1\POWERPIVOT): Pass.
  5. Check Server Configuration (AS2012SP1\POWERPIVOT): Pass.

The ULS logs should give you all the information you need to verify the Excel Services configuration for PowerPivot, but investigating ULS logs on busy SharePoint servers can be tedious and time consuming. Running an ad-hoc PowerShell script in the SharePoint Management Shell is often an efficient and more interesting alternative. In my next blog post, I’m going to introduce such a PowerShell script to verify the SharePoint 2013 configuration for all shared services that might want to consume a workbook as a data source. Stay tuned!