Basic PowerPivot Question - Data Imported from 3 CSV Files - Upload to Sharepoint

[CSMcVey]

I have a Power Pivot workbook that has three data sources. All three data sources are local CSV files on my PC. I have imported the sheets, created the relationships, built a few pivot charts and inserted some slicers. Locally, the workbook works perfectly.

I then upload the file to a Sharepoint 2010 site in a regular document library. When I open the file it opens fine and I can see the data, when I click on a slicer I receive a warning that reads: 'Performing this operation requires using a query to refresh external data.... Do you want to query and refresh the data in this workbook'.

When I click ok I receive an authentication error.

My question is I don't understand why this is happening. Is there a way to save this file out to SP and have users interact with it without having to refresh the underlying data? Meaning b/c all of the data is stored in that file it doesn't need to refresh - it is ok that the users work with data that is as current as my most recent local update and upload.

 

[Mavericks334]

have you checked with you IT Team, whether powerpivot is installed on sharepoint, otherwise it wont work, you will only be able to see the data, but not make any changes, i faced the similar issue.

 

[CSMcVey]

Mavericks:

Our IT team has been unable to this point to configure Sharepoint so that PowerPivot will work on the site. They are working on this but in the meantime I was trying some creative solution to get this to work. My thought was b/c all of the data is local to the PowerPivot workbook I loaded, it would not try to refresh and would work in the interim. It looks like even though it relies upon local spreadsheets imported to the PowerPivot workbook when it's loaded to SharePoint when opened and a slicer is selected it will always try to refresh the data. Is this correct?

 

[NickyvV]

Well, if PowerPivot for SP is not working, then the workbook will definitely not work!
When clicking on a slicer the workbook will do an Excel Refresh, so the PowerPivot model (not the source data). But this PowerPivot model is also not available if PowerPivot for SP is not working correctly..

I borrowed the beneath picture from Rob's site to make things clear:

HTH

 

[CSMcVey]

Thanks to both of you guys - the diagram above is very helpful and I understand the issue now. Now need to get the Sharepoint Admins to turn on PowerPivot properly.