FileNotFoundException with Microsoft.AnalysisServices.Xmla

First published on MSDN on Dec 05, 2012

I ran across two cases that were hitting the following Exception within SharePoint trying to run the PowerPivot Management Portal.  This was located in the SharePoint ULS Log:

EXCEPTION: System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.AnalysisServices.Xmla, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.  File name: 'Microsoft.AnalysisServices.Xmla, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'     at Microsoft.AnalysisServices.SharePoint.Integration.Redirector.WCFTransport.Init()     at Microsoft.AnalysisServices.SharePoint.Integration.Redirector.WCFTransport.Connect(String dataSource, String applicationName, String& errorMsg)

The environment was setup in a similar way to the following – 3 SharePoint Services consisting on one WFE and two App Servers.

On the second App Server, PowerPivot was not installed, only Excel Calculation Services was configured (ECS).  The issue was originally intermittent.  To narrow this down, we turned off ECS on the App Server that had PowerPivot on it.  We were then able to hit the error every time.

Doing some searching on the error above provided some hits, but nothing that really seemed useful.  I then refocused on just trying to find out where the Microsoft.AnalysisServices.Xmla module comes from, as it didn’t appear to be installed on the PowerPivot server either – so it isn’t part of the base install.  This lead me to the following MSDN Documentation within Books Online:

Install the Analysis Services OLE DB Provider on SharePoint Servers
http://msdn.microsoft.com/en-us/library/ee210608.aspx

This included the following blurb under “Why you need to install the OLE DB Provider”:

The second scenario is when you have a server in a SharePoint farm that runs Excel Services, but not PowerPivot for SharePoint. In this case, the application server that runs Excel Services must be manually updated to use both the newer version of the provider, as well as install an instance of the Microsoft.AnalysisServices.Xmla.dll file in the global assembly. These components are necessary for connecting to a PowerPivot for SharePoint instance. If Excel Services is using an older version of the provider, the connection request will fail.

It’s always nice when I can find actual documentation that clearly states what is needed.  This at least met the scenario I was hitting and it indicated I had to have this installed.  Otherwise you get the error I was hitting.  However, that didn’t answer the question of where it comes from.  A little further down, I saw the following under “Install the SQL Server 2012 OLE DB Provider on a standalone Excel Services server”:

On the Feature Selection page, choose Client Tools Connectivity.

Click Management Tools - Complete. This option installs Microsoft.AnalysisServices.Xmla.dll.

For those not necessarily familiar with the SQL Product as a whole and are more on the SharePoint side of things, the Management Tools option is essentially SQL Server Management Studio and comes from the actual SQL Product Setup as a Shared Feature.

So, to get this, you would have to run SQL Setup and you only need to select this option.  You don’t actually need to install a full Instance of SQL.

So, for your environment, if you have a SharePoint App Server that only has ECS on it, and others that have PowerPivot 2012, you will need to install Management Studio on the ECS only boxes so that you can get the Microsoft.AnalysisServices.Xmla.dll assembly, along with following the other instructions in the document referenced above.  Of note, it does not appear that this was required with PowerPivot 2008 R2.

**** Update – 12/6/12 ****

I was also made aware that another option could be to run the sppowerpivot.msi that comes with Service Pack 1 for SQL 2012.  This is the PowerPivot add-in for SharePoint 2013.  This install will provide you some options and make selections.  You do not need to select the PowerPivot option.

The selection above will also install the SP1 build of Microsoft.AnalysisServices.Xmla.

Within the SP1 output, it will be in the 1033_enu_lp\<platform>\setup

You can also download the PowerPivot Add-in For SharePoint 2013 through the SQL 2012 SP1 Feature Pack .  The file is 1033\x64\spPowerPivot.msi.

Adam W. Saxton | Microsoft Escalation Services
https://twitter.com/awsaxton