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.
Comments
Anonymous
December 02, 2009
This looks pretty interesting. I'm trying to wrap my mind around what's going to be possible. Here's a use case that I'd like to know whether it's going to be supported:the user is a publisher with a public-facing site (not a restricted extranet)the products used would be Excel 2010 and Sharepoint Foundation hosted by a third-party (i.e. managed hosting) the intended use is to embed browser-based PowerPivots (presumably javascript/AJAX?) and PowerPivot Galleries (apparently Silverlight) on a public website. The core need is cross-browser support, IE-only won't jibe with a general audience. Does that make sense and is it an intended (or at least possible) use for these new products? Can the data be stored in an Excel spreadsheet on the server (assuming there's relatively little data) or do you have to use a SQL server in the mix no matter what?Right now we're using SP WSS 3.0 internally but the lack of cross-browser support for its Office-related features has ruled it out for public use.Anonymous
March 10, 2010
Do you have to have analysis services hooked up to all the powerpivots in order to browse them in sharepoint? or can i just have a powerpivot that runs off of a regular database...It seems a big limitation if you have to put every data source in analysis services just to have a report available in sharepoint...Anonymous
March 19, 2010
Eric,As far as I can tell from my sandbox set-up I'm running, PowerPivot models are hosted in SSAS on deployment to Sharepoint. There is no other alternative... PowerPivot for Sharepoint "IS" effectively a Sharepoint-integrated SSAS engine.As discussed at length, locally the models run "in-memory", but given the multidimensional way the data is generally modelled, I guess it made more sense to host in an SSAS instance as opposed to a regular DB instance (a sort of OLAP versus ROLAP trade-off).Regards,WillAnonymous
September 22, 2010
The PowerPivot Silverlight looks like very nice eye-candy.Does this webpart/page also work as a PerformancePoint - Dashboards gallery (with generated thumbnails)? Or does alternatively any Silverlight gallery for Dashboards exist?Best regards,JanAnonymous
July 07, 2014
Can the data be stored in an Excel spreadsheet on the server (assuming there's relatively little data) or do you have to use a SQL server in the mix no matter what? http://www.kurtoglucelik.com <a href="http://www.kurtoglucelik.com">çelik halat</a>