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:

A reminder to install the ADOMD.NET data provider

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 (11)

  1. Anitha says:

    I could not able to find PpsMonitoringServer under 15 hive.

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

  3. Eliot says:

    Hi Kevin

    Will modifying the web.config file in PpsMonitoringServer bounce iis?

    Furthermore, will executing this step resolve the following problem? Please let me know.

    Thanks!

    I am trying to execute the following action:

    Open New Excel Workbook

    To create a new Excel workbook from an existing workbook, you must already have Excel and PowerPivot for Excel on the local computer. Choosing Open New Excel Workbook starts Excel, opens a blank workbook (.xlsx) file, and loads PowerPivot data in the background as a connected data source. Only the data from the PowerPivot window in the original workbook is used in the new workbook. PivotTables or PivotCharts from the original workbook are excluded. The new workbook links to data in the original workbook. The data is not copied to the new workbook itself.

    I have my PowerPivot published to my PowerPivot Gallery. Yet, when I click the Open a New Excel Workbook and excel launches, I get the following Microsoft Data Link Error when attempting to test the Data Link Properties.

    Test Connection Failed because of an error in initializing the provider. The following system error occurred: Invalid Class String.

    Please advise.

    Eliot

  4. Dlozi says:

    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

  5. 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.

  6. Poomani Sankara. says:

    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."

  7. Derek says:

    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.

  8. Edwin says:

    Thanks this worked a treat… wish I found it 4 hours ago.

  9. Great This helped says:

    Great This helped. Finally you saved lot of my time 🙂

    Thank You

  10. Kong says:

    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"

  11. Hano says:

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