Sharing workbooks using PowerPivot Gallery

Now that you have downloaded the PowerPivot for Excel November Technology Preview at PowerPivot.com, you have probably built a really interesting BI application right there in the workbook.  Importing and mashing up data from different sources, building calculations with business logic using DAX, and then creating rich interactive views in Excel with pivots, charts and the awesome new slicers.  Surely there are others on your team who would get a lot of benefit from using this workbook in their daily jobs?  This post is about what happens when you move from doing self-service BI on your desktop, to sharing a team BI application using PowerPivot for SharePoint.

Make your workbook look great in SharePoint

It’s pretty easy to share a workbook with your colleagues using regular SharePoint document libraries.  But, after all the trouble you went to build a cool and useful BI application on your desktop, we wanted to make sure that the experience of the consumers of your work was really great.  When you install PowerPivot for SharePoint, you get a new kind of document library called the PowerPivot Gallery which is designed to highlight your workbook and let people use it for slicing and dicing right there in the browser without having to download it and open in Excel.

You can save your workbook to a PowerPivot Gallery using the regular Excel features for publishing to SharePoint, or just open a browser and choose Upload from the ribbon on the SharePoint site.  Once the workbook gets uploaded, PowerPivot for SharePoint will figure out the thumbnails and display them in the gallery view.

Using the workbook in the browser

As you move the mouse over the thumbnails, you can see a preview of the information.  If you click on a thumbnail, it will open the sheet in your browser using Excel Services, which is the SharePoint service that powers this experience.  Now you can start using the slicers to do analysis, which is really great for users who aren’t Excel power users but can figure out how to use web applications.

Other views

The PowerPivot Gallery shows the Gallery view by default, but like most SharePoint document libraries you can choose to see the information in different ways.  One way is to change back to the regular document library view.  To do this, use the Library ribbon tab and select All Documents from the Current View menu.

You can also choose one of the two other Silverlight views that we include, Theatre and Carousel.  The default view that is displayed when users go to PowerPivot Gallery is the Gallery view, but you can change that as well as the sort sequence by choosing Modify View from the Library ribbon tab.

Creating new reports based on your workbook

One of the best things about having PowerPivot for SharePoint installed is that the PowerPivot workbooks that you publish can be used as data sources for other reports and workbooks.  Just by sharing in SharePoint, you can point Report Builder or Excel to the URL of the workbook and build new reports in your favorite Analysis Services client tool.

If a consumer of your workbook decides that they need a different view of the data in your workbook, they can easily create a new report from that data source by choosing “New Excel Workbook” or “New Report Builder Report”.  This will add a new file to the gallery and launch Excel or Report Builder.  When you save the document, the thumbnail for the report or workbook will be calculated and users of the gallery will get the same cool experience as for the original workbook.

Troubleshooting the icons in the gallery

When we are calculating the thumbnails for the gallery, we rely on the fact that the workbook can actually be displayed by Excel Services.  If there is some problem with this process, then we show an error icon instead of a thumbnail as shown below.

The usual issue is that the workbook is not displaying properly in Excel Services, so the most common way to troubleshoot this is to click on the icon and see what error is being displayed in the Excel Services view.  If you fix the problem and republish the workbook, then the thumbnail will be recalculated.

If you are a system administrator and want to look at the details, there is a LOG\Gemini.log file in the SharePoint folder on the server with full details of what is happening.

What’s next

The other great feature that you get when you publish your workbook to SharePoint is the ability to schedule a regular data refresh.  Look for a follow-up post on this.