CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

PowerPivot : When versions get mixed up…

PowerPivot : When versions get mixed up…

  • Comments 3

I worked a case today where they were trying to get SQL 2012 Reporting Services installed in a SharePoint environment that also had SQL 2008 R2 PowerPivot installed.  This, by itself, is fine and wasn’t really causing the problem.  When they tried to open the Excel Workbook from SharePoint that had the PowerPivot data in it, we saw the following error:

image

We found the following within the SharePoint ULS Log for the Excel Calculation Services category:

ExternalSource.ExecuteOperation: We exhausted all available connection information. Exception: Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInfoException: Exception of type 'Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInfoException' was thrown.    
at Microsoft.Office.Excel.Server.CalculationServer.ConnectionInfoManager.GetConnectionInfo(Request request, String externalSourceName, Int32 externalSourceIndex, Boolean& shouldReportFailure)    
at Microsoft.Office.Excel.Server.CalculationServer.ExternalSource.ExecuteOperation(Request request, ExternalSourceStateInfo externalSourceStateInfo, ExternalSourceStateInfo prevExternalSourceStateInfo, Int32 index, ConnectionInfoManager connectionInfoManager, ExternalDataScenario scenario, DataOperation dataOperation, Boolean verifyPreOperationConnection), Data Connection Name: PowerPivot Data

What the error above is indicating is that it was trying to establish a connection from the defined data connections within the Excel Workbook. One of these connections is the PowerPivot Connection for the PowerPivot data stored within the workbook itself.  This connection/Data source is called “PowerPivot Data”. When we looked at that connection, we found the following:

image

You can get to this information by going to the Data Tab within Excel and clicking on “Existing Connections”. Then Right click on “PowerPivot Data” and edit the connection properties. Then go to the Definition tab.

The key here is the MSOLAP.5 Provider. This provider is the PowerPivot 2012 Provider. However, within the SharePoint environment, we had the SQL 2008 R2 version of PowerPivot. These versions are not compatible. Also, the MSOLAP.5 provider does not exist because we hadn’t installed it.  It comes with the PowerPivot 2012 install. So, the error above is really saying that it couldn’t find the MSOLAP.5 provider. Which in this case is true.

This is all about aligning the PowerPivot version of the workbook with the PowerPivot version of the Server. I think what may have caused this situation is that when you go to http://powerpivot.com and click on the “Download PowerPivot” button, it takes you to the SQL 2012 PowerPivot Add-in for Excel.

We have two options at this point:

  1. Upgrade the SharePoint environment to the 2012 version of PowerPivot
  2. Downgrade the Excel Add-in to the 2008 R2 version for PowerPivot and recreate the Excel Workbook.

SQL 2012 PowerPivot Add-in for Excel: http://www.microsoft.com/en-us/download/details.aspx?id=29074

SQL 2008 R2 PowerPivot Add-in for Excel: http://www.microsoft.com/en-us/download/details.aspx?id=7609

 

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

Leave a Comment
  • Please add 8 and 7 and type the answer here:
  • Post
  • What does the SSRS 2012 installation have to do with the failing connection to SSAS Powerpivot? Looks to me the it was built locally in Excel with 2012 version of the addin. When you then deploy it to a PowerPivot 2008 R2 server in SharepOint it won't work any more as it can contain incompatible features as well as another MSOLAP provider as you have encountered.

  • RS didn't really have anything to do with the PowerPivot issue, just the fact that that was their overall deployment of getting RS 2012 working in an environment with PowerPivot 2008 R2.

    You are correct that they had built the Workbook locally with the PowerPivot 2012 version of the Add-in and then deployed it to a server that was running the 2008 R2 version.

  • Hi,

    mine also same case created complex charts in excel sheet, but i have installed 2012 power pivot add in to my excel,

    can I downgrade my excel sheet develped"? re creating again is dificult..i tried to change the provider name.. it does not work.. any tricky solutions you have??

Page 1 of 1 (3 items)