I have to thank whoever runs this site (http://thesharespace.info/?p=15) for a lifesaver today.
PowerPivot Scheduled Refreshes Are Where The Money's At
I am learning that Scheduled Refreshes of PowerPivot workbooks is one of the 2 primary value-adds of the PowerPivot platform. Its the key time saver.
But getting the PowerPivot data refresh to work successfully can be a real pain. One pain I ran into recently was with PowerPivot Workbooks (PPWBs) using SP Lists as data sources using RSS Feeds.
RSS (ATOM) Files Can Be Trusted Data Sources, Just Like Reporting Service Data Sources
If you want to create a PowerPivot Workbook which uses SP Lists as datasources with RSS "Data Feeds", you can store and secure the data feed (ATOM) files in a secured "Data Feed" Library just like you can shared data sources for reporting and excel services.
Pitfall Selecting RSS Data Feed Files Using the Excel PPVT Plugin
However, there is a serious pitfall with the 2008 R2 PowerPivot Excel Plugin when creating PPWBs and adding RSS Data Feed sources from Data Feed Libs; if you use the Browse feature of the PP Plugin to pick the data feed file from the data feed lib, the PPWB Plugin will save a webdav path in the PPWB, which the server will be unable to resolve because webdav requires the office client and desktop experience to function.
So scheduled refreshes will fail.
Copy Shortcut/URL To The Rescue
Instead, you must use the browser's Copy Shortcut feature to copy the http:// URI to the RSS Data Feed file and use that to create your data source connection in excel.
Lesson Learned: Dont Use Browse To Select Data Sources In Excel