Publishing
Publishing a PowerPivot for Excel solution to SharePoint could hardly be easier. After the environment is established, deploying a PowerPivot solution takes one of two major paths. The first of these is conveniently located within Excel 2010's File option off the ribbon. The second is in SharePoint 2010, as an option of the PowerPivot Gallery's Document ribbon.
Working in Excel, publishing to PowerPivot for SharePoint is a simple matter of using the File menu. From an existing file, open PowerPivot for Excel solutions, as illustrated in Figure 7-1, and choose the “Save & Send” File menu option, which will enable options for Email, Web, and SharePoint. Choosing Save to SharePoint will show the most recent SharePoint servers used, similar to a recent files listing.
Figure 7-1. Excel Save & Send menu
In your development environment, it is unlikely existing servers will be listed. Select the “Browse for a location” option by double-clicking it. Doing so will render what appears to be a typical file location dialog, in which you may enter the URL of the desired SharePoint server. Pressing the right-arrow button immediately following the URL (indicated in Figure 7-2.) may generate a prompt for your credentials. Enter your domain credentials and you will be able to browse to the PowerPivot Gallery, the intended destination for the workbook.
Figure 7-2. Browse SharePoint server location
Note You will not be prompted if your current logon account is the domain account for your development environment and the development environment is in a Local Intranet Internet Explorer security zone.
Successful validation of your credentials will result in a dialog similar to Figure 7-3. From this window, choose the PowerPivot Gallery, optionally name the file, and click the Save button. After the contents of your worksheet are physically transported up to the SharePoint server, you will have published your first PowerPivot for SharePoint solution.
Note If you should experience an error dialog concerning the maximum allowed file size, refer to Chapter 6 instructions on configuring the SharePoint web application (maximum file upload size) and the Excel Services configuration (maximum file size in megabytes).
Figure 7-3. SharePoint browse for location
This workflow is fine when we are concerned with publishing to SharePoint a single file. However, we have the option, via the SharePoint Documents menu Upload selection, to move multiple files in a single batch upload request.
Publishing a file using the SharePoint Documents ribbon is very similar to the Save & Send operation from Excel. However, as we know the destination of the file (the location from which the Upload Document menu was invoked), we only have to select the file (or files) to upload from the workstation. To begin, navigate to the PowerPivot Gallery of your development environment, choose the Documents menu, and then choose Upload Document from the New section of the ribbon. This navigation is illustrated in Figure 7-4.
Figure 7-4. Upload Document menu
From the menu, there will be options to upload a single document, or multiple documents. The behavior of the single upload is similar to the save to SharePoint, except the user will determine which file (from what location) is uploaded. The really interesting behavior is the Upload Multiple Documents option. Selecting this option will present a dialog similar to Figure 7-5. Simply drag files onto the shaded area of the control (or browse), and press OK when finished. A batch upload of your PowerPivot for SharePoint solutions will begin. Assuming no file size or permissions issues, the dialog will vanish at the conclusion of the upload.
Figure 7-5. Multiple file upload
Revision Control
Moving the files from the workstation to a SharePoint server's PowerPivot Gallery is only part of the PowerPivot solution publishing story. Because the ancestry of a PowerPivot Gallery includes the lineage of a SharePoint document library, there are some interesting features available to PowerPivot developers. The greatest of these is the revision control built into SharePoint. As I have mentioned in Chapter 5, the primary unit of work in PowerPivot is an Excel file. Revision control of the Excel file gives you, the developer, a solution-level “undo” in the event of an unanticipated side effect to a workbook change.
Out of the box, revision control is not configured. However, implementing this feature is a simple task for a user with adequate SharePoint permissions. From the PowerPivot Gallery's Library ribbon, choose the Library Settings menu item, as illustrated in Figure 7-6.
The Library Settings page is a feature-rich page showing of all the potential settings related to a document library. In the General Settings area of the Document Library Settings page, choose the “Versioning settings” hyperlink. This will render a page similar to Figure 7-7. From there, you can activate version history by selecting either the “Create major versions” or “Create major and minor (draft) versions” radio buttons.
If you are working solo on any of your PowerPivot files, you can safely ignore the Require Check Out option at the bottom of the page. However, if there is any chance two people could be editing a single workbook at the same time, from within PowerPivot for Excel, this is a frustration-reducing feature. In order to save your changes to the Library Settings, press the OK button at the bottom of the page.
Figure 7-7. Versioning Settings
The chief difference between the two settings is how drafts (revisions between major, published revisions) are handled. Both methods, though, allow for users to recall other revisions to the file, from the SharePoint user interface. For instance, after implementing version history, and making a change to one of the PowerPivot workbooks on my development environment, there is a “version history” menu selection possible from each file. Selecting this option will render the published versions of the file, in a format similar to Figure 7-8. Each entry will have the modification date, who executed the check-in, and if comments were added during the check-in process. Each revision is then available for restoration (replacing the most recent revision with a historical one), viewing, or deletion.
Figure 7-8. Version History
PowerPivot Gallery View Settings
The PowerPivot Gallery includes four different view settings in order to best communicate to your PowerPivot for SharePoint users the content of the library and the PowerPivot solutions. The three graphical representations (Gallery, Theater, and Carousel) require Microsoft Silverlight as part of the presentation tier. As you open a PowerPivot Gallery from within a virtual machine environment, you may be prompted to install Silverlight, in order to use the default view setting of Gallery.
The default view setting can be changed from the Document Library Settings page. Additionally, users have the ability to change their view of a library via the Library Tools, Library, Current View setting.
The overall goal of the graphical library options is to enhance the user's ability to recall and reuse a workbook based on visual memory of the contents. To that end, each of these options presents a preview of the workbook contents in a unique way.
Each of the graphical library view settings has a common set of icons for management of workbook refresh schedules as well as using a PowerPivot for SharePoint workbook as a SQL Server Reporting Services data source.
Gallery
The Gallery view, illustrated in Figure 7-9, contains a row for each workbook in the document library, with a large preview image of the currently selected sheet and smaller preview images of each of the other available sheets within the workbook.
As depicted, there are at least two workbooks in the example PowerPivot Gallery: Top Ten Products and NCAA Dashboard. Each of these workbooks is represented by a row in the Gallery view. The currently selected sheet in NCAA Dashboard is labeled Offense, and an image of the contents is previewed to the left of the workbook name.
Figure 7-9. Gallery view
Theater
The Theater view is a completely different visual metaphor than the Gallery view. Instead of a row per workbook, the Theater view lists all sheets, contained in all workbooks, in the document library across the bottom of the view. Only by the workbook name in the bottom left area of the view does the user know by moving from one sheet to another that workbook boundaries have been crossed. To scroll through the sheets, left and right arrows allow for navigation to sheets not currently on the screen. For comparison, Figure 7-10. contains the same worksheets as the foregoing Gallery view.
Figure 7-10. Theater view
Carousel
Perhaps the most graphically appealing and crowd-pleasing visual for demonstration purposes is the Carousel view. A sheet-based metaphor similar to Theater, this view allows users to manipulate an imaginary rotator to navigate the sheets in the document library. Figure 7-11 illustrates the same document library contents, in the Carousel view.
All Documents
The All Documents view is a plain-text listing of all files (available to the user) in the PowerPivot Gallery. This is a library view feature of all SharePoint document libraries. As you will see later in this chapter, the All Documents view can be very useful in environments without Silverlight.
PowerPivot Gallery Permissions
Just like other documents stored within SharePoint, it is possible to create a set of permissions to restrict use of the PowerPivot solutions. Because the PowerPivot Gallery comprises all of the features of a SharePoint document library, it is possible to create an elaborate governance implementation using permissions at the Site Collection, PowerPivot Gallery, folder (within the Gallery), and the individual file. I mention this here only to make you, the PowerPivot solution developer, aware of the grain at which SharePoint can control access to your solution files. Establishing the SharePoint governance plan for your site is quite outside the scope of this book, though.