Specifying Your ADOMD.NET Data Provider Version

A while back, I wrote a post about how to get your PerformancePoint 2010 installation working with SQL 2012.That post talked about where to find the SQL Server 2008 R2 SP1 feature pack that contains, among other things, version 10.5.2500 of the ADOMD.NET data provider, which PerformancePoint needs in order to connect to any 2012 Analysis Services data source -- PowerPivot workbook models included. This works great for PerformancePoint 2010, but if you need the same functionality in your PerformancePoint 2013 instance, using SQL 2012, you will need major version 11.0 of the ADOMD.NET data provider. You can download that, by way of the spPowerPivot.msi, here.

The Situation

At this point, you might be asking yourself why you need to take the extra step to install the MSI when you can simply install the SQL BI components for SharePoint when you install an instance of SQL Server 2012 SP1. Without going in to too much detail, there are two reasons: First, SharePoint no longer bundles the provider in the prerequisite installer (as was the case in 2010). So, any data providers will have to be installed separately. This is why we added a friendly reminder after you provision a new PerformancePoint service application:

Second, if your SharePoint farm contains more than just one machine (not counting your SQL back-end), the recommendation is that you install the MSI on each machine in the farm running PerformancePoint Services, since for any given request, PerformancePoint could call from that box, requiring the ADOMD.NET provider in order to connect. In addition to these two things, the MSI gives you the ability to connect to PowerPivot workbooks as a PerformancePoint data source. Yes, this was available in PerformancePoint 2010, but in 2013, you need spPowerPivot.msi to accomplish the same thing (assuming that you didn't install the add-in from the SQL install). Incidentally, the MSI's installation instructions can be found here.

The Assembly Redirect

As with PerformancePoint 2010, in PerformancePoint 2013, we compile with major version 10 of the ADOMD.NET provider. So, if you need to use version 11 (and you will for your 2012 BI workbooks in SharePoint 2013), you need to tell PerformancePoint to do so. New in PerformancePoint 2013 is an assembly redirect that you can use for just that purpose. We added the redirect so that going forward you can use new provider versions from Analysis Services without having to upgrade SharePoint (e.g. PerformancePoint). This is particularly handy if your organization mixes and matches versions of SharePoint and Analysis Services. To see the redirect block that we added, on each server running PerformancePoint Services, navigate to \Program Files\Microsoft Office Servers\15.0\WebServices\PpsMonitoringServer. Open the web.config file. It should be located in the same directory as PerformancePointService.svc. At the very bottom of the file, you should see an <assemblyBinding> block contained within a <runtime> block, like this:

<runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.AnalysisServices.AdomdClient" publicKeyToken="89845dcd8080cc91" culture="neutral" />
        <bindingRedirect oldVersion="9.0.0.0" newVersion="10.0.0.0" />
      </dependentAssembly>
    </assemblyBinding>
</runtime>

Change the values in the <bindingRedirect> element from oldVersion="9.0.0.0" to oldVersion=10.0.0.0" and from newVersion="10.0.0.0" to newVersion="11.0.0.0". This will allow PerformancePoint to leverage the GAC'd version 11 ADOMD.NET assembly when making calls to Analysis Services 2012 (and prior versions). It will also allow you to use the latest PowerPivot models as data sources in PerformancePoint -- something our customers have come to really rely on.

Thanks for reading...

Kevin Donovan
Program Manager, Office BI

Comments

• Anonymous
  March 09, 2014
  I could not able to find PpsMonitoringServer under 15 hive.

• Anonymous
  March 10, 2014
  Anitha, Make sure you are running PPS 2013. The Web.Config file is installed there by default, otherwise your PPS instance would not work.

• Anonymous
  May 01, 2014
  The comment has been removed

• Anonymous
  August 06, 2014
  Hi Kevin I got SharePoint 2013 and SQL server 2014 installed on my server when changing the web.Config file. Should change it to the following? oldVersion="9.0.0.0" to oldVersion=10.0.0.0" and from newVersion="10.0.0.0" to newVersion="11.0.0.0" or to newVersion="11.0.0.0" to newVersion="12.0.0.0"? thanks

• Anonymous
  August 08, 2014
  You should just specify the prior and current versions of the Microsoft.AnalysisServices.AdomdClient -- whatever those are in your case. It won't hurt anything if you get the values wrong. Just keep working with it until you get the right ones. I'm guessing you will need to specify 11.0.0.0 and 12.0.0.0.

• Anonymous
  October 03, 2014
  HI, I am using SharePoint 2013 and SQL Server 2012. But still getting same error even after i have done the steps which you said, "An error occurred connecting to this data source. Please check the data source for any unsaved changes and click on Test Data Source button to confirm connection to the data source."

• Anonymous
  October 12, 2014
  After installing the ADOMD SQL 2014 feature pack item, I updated the binding redirect to oldVersion="10.0.0.0" and newVersion="12.0.0.0" That finally worked for me.

• Anonymous
  December 04, 2014
  Thanks this worked a treat... wish I found it 4 hours ago.

• Anonymous
  February 12, 2015
  Great This helped. Finally you saved lot of my time :) Thank You

• Anonymous
  April 25, 2015
  Derek, you're the real MVP!! I have SP 2013 on SQL 2014.. I had to update the binding to redirect to oldVersion="10.0.0.0" and newVersion="12.0.0.0"

• Anonymous
  August 02, 2015
  Hi Kevin. This article saved me a lot of time figuring out why my current reference kept defaulting to version 10, thanks so much.

• Anonymous
  October 24, 2016
  Great Article.. Saved a lot of time..