Refreshing Excel File in PowerPivot from SharePoint

A couple of SharePoint-related questions from students taking my PowerPivot class that are worth sharing.

Question: Can I import data from and auto-refresh an Excel file deployed to a SharePoint library in PowerPivot?

Answer: Yes, if you use WebDAV and specify the UNC path to the file. WebDAV requires the Windows Server Desktop Experience feature to be installed and the WebClient service to be started:

Question: If major and minor (draft) versioning is enabled in the PowerPivot Gallery, is there a way to configure PowerPivot to create major versions only on refresh instead of minor versions?

Answer: This is not supported natively by PowerPivot because the snapshot process is not configurable. A possible workaround is to configure a SharePoint workflow to promote minor versions to major versions. As a side note, you should be careful with maintaining version history because the SharePoint content database will quickly grow. If you haven’t done so, consider configuring SharePoint to retain a limited number of versions.