Microsoft Office Tutorials and References
In Depth Information
After you make your selection in Publish Options, you usually want to save the file to SharePoint
by using the Save As button in the same pane that you saw in Figure 11-8. It is very handy to
select the location you want by using the list above that button, which displays the last-used
paths along with a generic Browse For A Location option.
Note Publish Options are saved in the Excel workbook whether or not the file is saved to
SharePoint. In other words, if you save the file in your local documents folder after you choose
the item to publish and then plan to upload the document to a SharePoint document library
without using Excel, the Publish Options you set will still be considered by SharePoint.
Versioning and File Size
Every time you save or upload a document to SharePoint, you are placing that document
in a document library, which might have a setting that automatically creates a new version
every time a file is saved. In other words, older versions of the same file are retained in the
same document library. By default, this setting is disabled in a PowerPivot Gallery, but
it can be changed as it can be in any other document library. An Excel workbook
containing PowerPivot data might be hundreds of megabytes in size. In that case, you should
be careful about deciding to enable this feature in the document library in which you want
to store your Excel files because retaining all the versions of such a workbook might be
very expensive for the storage on the server.
Moreover, also consider that by default a SharePoint server has a limit of 50 MB for
the size of a file that can be uploaded. To change this limit, you have to contact your
IT department. That limit can be raised to a maximum of 2 GB.
PowerPivot Data Refresh
Each time you request to view an Excel workbook in the browser, you can request (through the
Data menu that you can see in Figure 11-2) to refresh data coming from external connections,
and you can recalculate the workbook on demand. However, refreshing data from an external
connection does not update data in PowerPivot tables. Conceptually, data stored in PowerPivot
tables are already an external connection from the point of view of an Excel PivotTable. If a
PowerPivot table got its rows from an external database, such as SQL Server, that table is not
refreshed just because the user requested refreshed data for a connection in Excel. To update
that table, you need to instruct the PowerPivot for SharePoint service to do that job for you,
and this requires a different approach and user interface.