Share via

PowerPivot : When versions get mixed up…

  • Article

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 https://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: https://www.microsoft.com/en-us/download/details.aspx?id=29074

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

 

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

Comments

  • Anonymous
    July 29, 2012
    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.

  • Anonymous
    July 29, 2012
    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.

  • Anonymous
    August 08, 2013
    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??

  • Anonymous
    October 22, 2014
    You Sir, are a lifesaver!! That was the last of many obstacles to enable excel services to refresh external data. I already tried last year and ended up installing analysis services in SharePoint mode where everything just worked fine. With the mean limitation that you can't actually use your ssas tabular cube but have to rebuild everything in PowerPivot. I've found the same cryptic error message in the logs and was about to give up again. I gave it a shot and googled for the exception. Reading your blog I had my doubts that this was really my issue. But then I changed the connection string in my odc file from MSOLAP.6 to MSOLAP.5 and... IT WORKS! Thank you so much! :-)

  • Anonymous
    November 17, 2017
    Once utilized your internet site responsible for, It’s about time yet another website. Boy, ended up being We improper. It’s a terrific web log of course. I’m so contented I stumbled upon that, I’ll unquestionably returning before long.knock off van cleef arpel ring http://www.womenclove.net/