lunedì 23 aprile 2012 16:57
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?
Tutte le risposte
lunedì 23 aprile 2012 21:00
Can't be done.
lunedì 23 aprile 2012 22:19
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?
martedì 24 aprile 2012 02:39
martedì 24 aprile 2012 14:29
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.
mercoledì 25 aprile 2012 01:03
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...
mercoledì 25 aprile 2012 01:07
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
mercoledì 25 aprile 2012 23:30
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).
- Contrassegnato come risposta Challen FuModerator mercoledì 2 maggio 2012 06:59
mercoledì 2 maggio 2012 23:00Thanks, 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?