Freigeben über

Verifying the Excel Services Configuration for PowerPivot in SharePoint 2013

  • Artikel

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!

Comments

  • Anonymous
    March 29, 2013
    HiThanks for providing the detailed screenshots to trouble shoot the error. It is also worth to check the configurations steps for excel services which can be found www.techbubbles.com/.../configuring-excel-services-in-sharepoint-2013

  • Anonymous
    April 17, 2013
    Thanks for the detailed blog post. It helped me solve an issue with my SharePoint/PowerPivot integration. Apparently I forgot to apply SP1 to the PowerPivot instance :)

  • Anonymous
    April 27, 2013
    Hi, I'm running into the problem that I do have the CLR Exception, but the data model server is added correctly in  Excel Services (SQL Server 11.0.3128). I'm able to create a BIM that points to a separate  .Analysis instance. When adding a Excel document and then a BIM to a document library, I'll get the error as you described while testing the connection.Uncaught CLR exception crossing the Interop boundary: Microsoft.AnalysisServices.Streaming.ServerNotFoundException: There are no servers available or actively being initialized.    at Microsoft.AnalysisServices.Streaming.OnPremise.ServerPool.Microsoft.AnalysisServices.Streaming.OnPremise.IServerPool.GetAvailableServers()    at Microsoft.AnalysisServices.Streaming.OnPremise.LoadBalancer.<>c__DisplayClass1.<GetLoadBalancedOperation>b__0()    at Microsoft.AnalysisServices.Streaming.OnPremise.LoadBalancer.GetLoadBalancedOperation(Func1 onBegin) &nbsp; &nbsp;at Microsoft.AnalysisServices.Streaming.OnPremise.Database.Loading.Create() &nbsp; &nbsp;at Microsoft.AnalysisServices.Streaming.OnPremise.ServerPool.&lt;&gt;c__DisplayClass9.&lt;Microsoft.AnalysisServices.Streaming.OnPremise.IServerPool.CreateConnection&gt;b__8(Guid id) &nbsp; &nbsp;at Microsoft.AnalysisServices.Streaming.Internal.KeyedReferenceCountedEnumeration2.Node.<>c__DisplayClassc.<.ctor>b__a()    at System.Lazy1.CreateValue() &nbsp; &nbsp;at System.Lazy1.LazyInitValue()    at Microsoft.AnalysisServices.Streaming.Internal.KeyedReferenceCountedEnumeration2.Node.GetReference() &nbsp; &nbsp;at Microsoft.AnalysisServices.Streaming.Internal.KeyedReferenceCountedEnumeration<code>2.GetOrCreateReference(TKey key, Func</code>2 valueFactory) &nbsp; &nbsp;at Microsoft.AnalysisServices.Streaming.OnPremise.ServerPool.Microsoft.AnalysisServices.Streaming.OnPremise.IServerPool.CreateConnection(Guid imageId, Func1 allocationFactory)    at Microsoft.AnalysisServices.Streaming.OnPremise.Database.Loading.EstablishConnection()    at Microsoft.AnalysisServices.Streaming.OnPremise.Database.ConnectWithoutOperationSetup()    at Microsoft.AnalysisServices.Streaming.OnPremise.Database.Microsoft.AnalysisServices.Streaming.IDatabase.Connect(DatabaseConnectionProperties props)    at Microsoft.AnalysisServices.Streaming.Interop.StreamingServerPoolModule.Microsoft.AnalysisServices.Streaming.Interop.IStreamingConnector.Connect(SSPM_CONNPROPS in_connprops, SSPM_CALLBACKS in_Do you have any idea what to check/change in the configuration?SharePoint 2013 with PUSQL Enterprise with SP1 Installed the SharePoint integration on the WFE Cheers, Amancio

  • Anonymous
    April 28, 2013
    Hi Amancio,Check out the following article: blogs.msdn.com/.../verifying-the-sharepoint-2013-configuration-for-accessing-workbooks-as-a-data-source-from-within-the-farm.aspx.Specifically Step 3. Log on to your SharePoint machine using the Excel Services service account. Can you run this PowerShell script and connect to your SSAS server? The scripts in this blog article then should enable you to locate the problem.Cheers,Kay

  • Anonymous
    May 02, 2013
    Thanks for these Blog posts on BI.  It really takes a lot of the confusion out of installation.  But.. I do have a question concerning just getting Excel Services to work only to Refresh Data in a PIVOT Table that I have stored in SharePoint 2013 that is accessing a Cube created in Analysis Services 2012 on another server.  All the examples and installation instructions for BI say I need to create a separate instance for Analysis Services in SharePoint deployment mode and installing the PowerPivot for SharePoint.  I really don't think I will be using PowerPivot or PowerView day one in SharePoint I just want to access a Cube from within Excel Services and refresh the Data so I can use the Slicers.   Also, I don't want to install SQL Server 2012 Enterprise when I don't need PowerPivot or PowerView.  But, all the installations say that I need to install PowerPivot.  Can you set me in the right direction on what I really need to install to get this working.My environment initially will look like - WFE Application Server, Database Server with the Database Engine only this will be SQL Server 2012 STD.   Analysis Services Server for my cubes and the Database Engine for the Databases that the cubes will be built from.  This is also SQL Server 2012 STD.  Is this possible?

  • Anonymous
    May 05, 2013
    Hi Steve,Indeed, you do not need PowerPivot or Power View to work with workbooks that connect to an external Analysis Services data model. Just make sure that Excel Services has network access to the SSAS server, and depending on the Authentication settings in the workbook, that Excel Services has the rights to delegate Windows user identities to SSAS. This should be all it takes to get the slicers to work, and to make sure that the workbooks always provide recent data, you can set the Refresh on Open flag in the workbook. Excel Services will then refresh the workbook every time it is opened. It slows down the opening of the workbook somewhat, but you have the most recent data. There is no Scheduled Data Refresh facility available in this environment because that feature is built for PowerPivot workbooks.Hope this helps.Cheers,Kay

  • Anonymous
    September 07, 2013
    For Excel Services external data refresh and filters to work from Sharepoint 2013, does the data have to be in Analysis Server? I am running Sharepoint 2013 Enterprise and SQL server 2012. I have the data in SQL server 2012 database but I keep getting this same error. Thanks in advance.

  • Anonymous
    September 09, 2013
    The comment has been removed

  • Anonymous
    October 30, 2013
    I have two tier architecture, Server A with Web and app and Server B with DB.I have already applied all the sugestions made above but I still have the issue “There are no servers available or actively being initialized”. the Windows Firewalls are desactivated so I figure out that this is not the problem.What can I do? I have been two weeks struggling with this problem and I can't find a solution..ThanksPD: I did in one server solution and everything work perfectly so I guess the problem ocurrs because the two tier architecture

  • Anonymous
    October 30, 2013
    Hi Perpetillo,You have a SharePoint Server and a DB Server. Do you also have a SSAS Server in SharePoint mode? Check out the topologies in this article: blogs.msdn.com/.../introducing-the-bi-light-up-story-for-sharepoint-2013.aspxCheers,Kay

  • Anonymous
    October 30, 2013
    Hi Kay,Thanks for you prompt answer.My architecture it is exactly the one showed in the "Separate SQL Server " picture for the link that you suggested me, I mean , I have SSAS Server in SharePoint mode in my Server B (Database server) and I have registered it in the Excel Service Aplication.My SQL version is exactly:Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)Dec 28 2012 20:23:12Copyright (c) Microsoft CorporationEnterprise Edition (64-bit)so I don't think that it could be the problem.what is what I am doing wrong?Thanks,

  • Anonymous
    November 01, 2013
    Hi Perpetillo,It doesn't look like you are doing anything wrong. So you are saying in a single-server deployment it works but with separate servers it doesn't? But you can connect directly as you've followed the steps in the other article? The only thing I can think of is that the Excel Services service account doesn't have server admin permissions in SSAS.Have you configured an unattended account for Excel Services and given that one SSAS server admin permissions too?Other than that, I'm running out of ideas. Somebody in customer support might have to log at your logs to figure out what is going on...Cheers,Kay

  • Anonymous
    November 11, 2013
    I have a 3 tier SharePoint Farm with 1 WFE, 1 APP server and 1 SQL Server (this is the only server running Windows Server 2012 R2). I installed the powerpivot plugin spPowerPivot on all servers. I then ran the SQL Server 2012 BI version on the SQL Server to install Analysis Service Instance called PowerPivot which went smoothly. I added the Excel Services Service Account to the Admin permissions when installing. I tried to add the account by right clicking the PowerPivot instance, then Properties and then Security. However that did not work, it did not open the Properties window and threw a 4096 error. I then logged into my App server and added the PowerPivot instance to the Data Model but I don't see anything in my ULS log file with regards to Data Model, so running your Powershell commands returned nothing. What am I doing wrong?ThanksYoshi

  • Anonymous
    November 11, 2013
    Thanks Kay again,I found the solution. You gave me the clue. I delete the unattended account for Excel Services and create a new one following the proper steps and averything worked!!!.

  • Anonymous
    March 26, 2015
    Hi, I had configure Excel Services as supposed, configure the port for PowerPivot and when i do the server checks its show me "Checking Server Configuration (AS2012SP1)" without the powerPivot instance, please tell me what i am doing false. Thanks