Share via

You Can Use SQL Server "Denali" PowerPivot Models as PerformancePoint 2010 Data Sources

  • Article

Since SQL Server Denali hit the public download space, several people have tried using it to create PowerPivot models as data sources for PerformancePoint 2010. And why shouldn't they? PerformancePoint works just fine in the scenario where you specify the reference to a PowerPivot model in the data source connection string for an Analysis Services data source.

The Problem

But just about every one of those cutting-edged users has wisely pointed out that something fundamentally breaks when trying this same scenario with a Denali RC0-based PowerPivot workbook. There are a couple of reasons for this, which I address below. The error that manifests in the event log indicates that the existing, installed provider is too old. And that's true if you're running a standard SharePoint 2010 SP1 installation. The error most people have been seeing is this:

Exception details: Microsoft.AnalysisServices.AdomdClient.AdomdUnknownResponseException: Unsupported data format : application/vnd.ms-excel.12

The good news is that a workaround exists.

The Solution

The easiest way to make this scenario work is to install the ADOMD.Net (version 10.5) data provider on your SharePoint farm. You can get this by downloading it from the SQL Server 2008 R2 SP1 Feature Pack (make sure that you download the version appropriate for your server architecture). Version 10.5 of this provider is backward compatible with the new version 11.0. And because it's a minor version, it will overwrite the version 10.0 provider, which is the major version that PerformancePoint looks for at runtime. This latter notion explains the alert that the installer throws when you begin the installation. Go ahead and click, "Yes".

The Detail

I will use the example of a 1M farm in this case. SharePoint's pre-requisite installer will install both the 2005 and 2008 versions of ADOMD.Net. This ensures that Analysis Services data connections work smoothly out of the box. If you look in add/remove programs on your SharePoint server, you can see the provider versions. Version 10.1 is the one that PerformancePoint will use when connecting to the PowerPivot workbook when you use it as a data source.

When you install SQL Server Denali, it will install the version 11.0 ADOMD data provider. You can see this as well by looking at the list of assemblies on the server.

The problem is that there is no policy telling the request to use the latest version of the provider, and PerformancePoint is looking specifically for major version 10. So, in effect, version 11.0 is sitting there all alone and unused. However, when you install the ADOMD client version 10.5, things work because that version is compatible with the latest PowerPivot models, and PerformancePoint is still happy because it sees version 10, just like it expects to. Unlike major versions of the provider, which exist side-by-side, minor versions overwrite each other, which is why PerformancePoint grabs the right one even after you overwrite version 10.1 with version 10.5. So, after installing the SQL Server 2008 R2 SP1 update, when you look at the list in add/remove programs, you will see this:

 Once you have updated your provider, PerformancePoint should be able to connect to the PowerPivot workbook as you would expect.

There are a couple of parting notes here. First, notice that the name of the cube, as interpreted by PerformancePoint, is now called "Model". You may recall that it was called "Sandbox" for PowerPivot 2010 models. Second, most of the published documentation indicates that you need to add the PROVIDER property to the PerformancePoint connection string, like this: PROVIDER=MSOLAP;DATA SOURCE=https://contoso/Documents/PowerPivot_Sample.xlsx. This string is a little redundant; there is no need to set the provider property because the provider is adomd.net. And adomd.net will create the right data access object without the user having to specify MSOLAP to connect to the data source.

Kevin Donovan
Program Manager, Office BI

Comments

  • Anonymous
    December 21, 2011
    Perfect. At last I have my connection working!  It did require a iisreset after my installation to get this working properly.  Now just need to add a few measures to the PowerPivot file and we are rolling. Minor type in the Problem section - "Danali RC0-based". Happy holidays and thanks for the post!

  • Anonymous
    February 27, 2012
    I also wanted to point out that you may get errors along similar lines (e.g. "System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.AnalysisServices, Version=10.0.0.0...") when importing KPIs through Dashboard Designer. The 10.x AMO assembly update solves this issue.

  • Anonymous
    March 14, 2012
    I'm still getting the error. Any suggestions

  • Anonymous
    July 13, 2012
    Works great! but make sure you use the SQL2008 R2 SP1 version of "SQLSERVER2008_ASADOMD10.msi" and NOT SQL2008 R2 version. The mistake causes the following error: The Unattended Service Account "CONTOSOadministrator" does not have access to the server specified by the data source connection string. Data source location: intranet.contoso.com/.../Data Connections/7_.000 Data source name: ppx Exception details: Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException: A connection cannot be made. Ensure that the server is running. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.AnalysisServices.SharePoint.Integration, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. File name: 'Microsoft.AnalysisServices.SharePoint.Integration, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'   at Microsoft.AnalysisServices.AdomdClient.XmlaClient.WCFConnect(String dataSource, String dataSourceVersion, String& timeLastModified, String& databaseId, String& databaseName, String& loginName, String& serverEndpointAddress)

  • Anonymous
    July 15, 2012
    After installing ADOMD.NET  10.51.2500.0 version I also had to restart my server before it worked.

  • Anonymous
    February 12, 2013
    Same as Fred and Dan, worked great. I had to also use iisreset to get things working.

  • Anonymous
    February 12, 2013
    Also, this fixes issue connecting PPS 2013 to OLAP Cubes (Multi and Tabular).

  • Anonymous
    October 23, 2013
    I know this is an old thread, but I found something that did the trick for me. Take a look at blogs.msdn.com/.../specifying-your-adomd-net-data-provider-version.aspx. You can modify the web.config file of the PerformancePoint service to point to whichever version of ADOMD.NET that you like. In my instance, I installed the SQL Server 2012 version, made the change as described, and was able to connect. There was no need to do an IISRESET or reboot after making the web.config change.

  • Anonymous
    December 29, 2014
    Hi, I have SQL server 2014 on my machine with SharePoint 2013 Ent. On-Premises. so i installed the ADOMD.NET for SQL server 2014. it shows dll ver. 12 in GAC. also i made changes in web.config, that solved my issue to get analysis service database list. but while connecting to Power Pivote Excel file with  PROVIDER=MSOLAP;DATA SOURCE="http://ServerName/PowerPivot%20Gallery/AdventureWorksDW_PP.xlsx"  then its not populating cube drop-down. as well i have setup Power Pivot for share point and seem working fine for the same. Everything except Power Pivot is working fine. could you please guide how could i resolved this issue?