locked
Issue using PowerPivot workbook as a data source RRS feed

  • Question

  • I have a PowerPivot workbook hosted in a SharePoint PP gallery.  It's working great.  Using the PP Gallery capability "New Report", I created a new Excel spreadsheet that uses this PowerPivot workbook as it's data source.  That works great too.  However, when I try to access the new report spreadsheet from a machine that doesn't have PowerPivot installed, it fails with an error: "Initialization of the data source failed".    From that same machine, I can connect to cubes in SSAS, so it appears that the Provider for SSAS 10.0 is working fine.  Once I install PowerPivot on the machine, the report works perfectly.

    I thought I could use PowerPivot workbook as a data source in Excel without installing PowerPivot on all machines, since it just appears as another MSOLAP data source.

    Should I be able to use a PP workbook, stored in a SharePoint PP gallery as a data source within an Excel spreadsheet on a machine w/o PP installed?

    If I should be able to do this, any thoughts on what I need to do in order to get this working?

    Monday, April 23, 2012 4:57 PM

Answers

  • Hi Henry,

    I believe what you are seeing is that when you install PowerPivot you also get the updated MSOLAP provider, which is an upgrade from the one installed with Excel and knows how to read the AS database embedded in a PowerPivot workbook. When you uninstall PowerPivot, the updated MSOLAP provider remains (which should work just fine for back-compat scenarios).

    HTH,

    -Ron Pihlgren

    • Marked as answer by Challen Fu Wednesday, May 2, 2012 6:59 AM
    Wednesday, April 25, 2012 11:30 PM

All replies

  • Can't be done.

    See: http://msdn.microsoft.com/en-us/library/ee210625.aspx

    Sorry,

    David Hager

    Excel FMVP

    Monday, April 23, 2012 9:00 PM
  • Thanks, David.

    I'm not sure I understand the relevance of the reference, though.  So, either I am totally missing the point in the article above, or maybe I wasn't clear on what I'm doing. 

    I'm not using "Data Feeds" at all in this scenario.  It's a PowerPivot workbook, deployed to a PowerPivot Gallery on SharePoint.  I then created a new  report by selecting "Open New Excel Workbook" in the PowerPivot Gallery view.  This creates a new Excel workbook that's configured with a data connection that points to the original PowerPivot workbook using the MSOLAP provider.  It's my understanding that Excel should look at this as a "standard" MSOLAP connection and should allow me to create pivot tables, charts, etc. - that users w/o PowerPivot should be able to use.

    Does that make sense or am I way off base here?

    Henry

    Monday, April 23, 2012 10:19 PM
  • Hi Henry

    This is strange.  How are you connecting to the workbook through excel w/o PP?   are you connecting directly to the SharePoint 'sandbox' cube or are you doing it via the workbook URL? http://server/powerpivot gallery/book1.xlsx?




    Javier Guillen
    http://javierguillen.wordpress.com/

    Tuesday, April 24, 2012 2:39 AM
    Answerer
  • I am connecting to the PP workbook via the URL  Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=UtilizationReporting Process 4064ed72-7dba-471a-980b-613b8d015f39;Data Source=http://sharepointserver/powerpivotgallery/Report.xlsx;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Data Source Version=4/19/2012 10:07:29 AM

    I must have just misread the documentation on this.  I thought once you published the workbook to a PP Gallery on SharePoint, any Excel workbook could use it as a data source, since it was supposed to appear as an MSOLAP datasource.  Based on this thread, I'm guessing I was just wrong about that.   

    Thanks for the responses.

    Henry

    Tuesday, April 24, 2012 2:29 PM
  • Hi Henry,

    I don't believe you are incorrect.  You can connect to published powerpivot applications through the same OLAP provider you can connect to a regular Analysis Services cube.   In fact, not only you can connect via Excel, but through any other OLAP client like SSRS, PPS, Report Builder, etc.

    I just tested - again - connecting to a SharePoint PowerPivot 'sandbox' cube from an machine after uninstalling PowerPivot and everything works fine.  It looks, to Excel, like a regular SSAS cube.

    Have you tried manually generating the connection?  Going to Data -> from other sources -> analysis services.  From here, use the URL format I gave on the last post. Once you click next, you should see the cube available...




    Javier Guillen
    http://javierguillen.wordpress.com/

    Wednesday, April 25, 2012 1:03 AM
    Answerer
  • Thanks, Javier, I will try that approach with the manually generating the connection. 

    For the record, I did see similiar behavior that you describe.  If I uninstall PowerPivot, I was able to connect no problem.  On machines where I have never installed PowerPivot, however, it will not connect.  I've run through this several times on a Virtual PC image I have so that I can undo the changes and try again, and it behaves the same:

    Pre-PowerPivot - I get the error

    Install PowerPivot - works great

    Uninstall PowerPivot - works great

    Henry

    Wednesday, April 25, 2012 1:07 AM
  • Hi Henry,

    I believe what you are seeing is that when you install PowerPivot you also get the updated MSOLAP provider, which is an upgrade from the one installed with Excel and knows how to read the AS database embedded in a PowerPivot workbook. When you uninstall PowerPivot, the updated MSOLAP provider remains (which should work just fine for back-compat scenarios).

    HTH,

    -Ron Pihlgren

    • Marked as answer by Challen Fu Wednesday, May 2, 2012 6:59 AM
    Wednesday, April 25, 2012 11:30 PM
  • Thanks, Ron.  That's what I was slowly coming around to figurin out - thanks for speeding up the process.  So, the answer is - either install the updated MSOLAP provider on client workstations, or just bite the bullet and install PowerPivot?
    Wednesday, May 2, 2012 11:00 PM
  • This was a very old post but hoping you will answer me. Do you have Kerberos authentication setup? When I try to use the 'New Excel Workbook option' I just see an error.

    Thank you.

    Friday, August 16, 2013 5:11 PM
  • SatisKa,

    We've been back and forth on Kerberos, but at the time I was having this issue, we didn't have Kerberos configured.  The answer to our issue was installing PowerPivot on all clients in order to get the latest OLAP drivers.

    Thursday, August 22, 2013 6:37 PM