You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
This article continues the series on PowerPivot that started with an overview as well as an introduction to PowerPivot for Excel 2010. We’ll now focus on the support PowerPivot provides for collaboration.
Frequently, business users need to share applications they create on their desktops with a workgroup. As it turns out, SharePoint provides a great platform that is critical for realizing the overarching goal of extending business intelligence technology to an organization:
Let’s take a quick stroll through a few of the features that PowerPivot for SharePoint enables for business users.
SharePoint users are very familiar with a flat list of documents such as this:
For our target audience, the library contents are frequently workbooks that users can better associate with visually. In addition, we want to make publishing PowerPivot workbooks inviting, fun, and representative of the care our users put into their spreadsheets. As such, PowerPivot provides several skins that can be applied on a document library. Here’s one where each container is a workbook and each thumbnail is a snapshot of the actual worksheet contained within the workbook:
Here’s another one that is more optimized for using larger real estate:
Both of these views use Silverlight to provide smooth scrolling across snapshots.
Irrespective of the skin, the bitmap shown is a real snapshot captured whenever the document is changed in SharePoint.
Selecting any of the snapshots launches the appropriate application. Selecting the snapshot of a worksheet, for example, will lead users directly into Excel Services with the right worksheet selected by default:
Remember that the workbook contains PowerPivot data within it. Performing any action that requires access to that snapshot – explicitly forcing a refresh, clicking on a slicer, retrieving a list of possible values to filter on, etc. – will transparently cause PowerPivot for SharePoint to extract this embedded data, load it into SQL Server Analysis Services running on the SharePoint farm, and service the query request from Excel Services. Thereafter all queries for that workbook, whether from the first user or any others, are satisfied using a high performance database engine. PowerPivot for SharePoint continues to manages the lifetime of this database and will automatically unload it if it hasn’t been used for a while.
There are, of course, many more details about this implementation but for this post focused on business users, it is important to note that the only difference end users see is that the first connection to the database may take slightly longer than once the database has been loaded, and of course, that they don’t need to download the complete document on their machine, or even have Excel installed on the machine, to benefit from what someone else in their workgroup produced.
Frequently workbooks contain PowerPivot snapshots that need to be refreshed periodically. Instead of requiring users to manually download the workbook to their desktop, refresh the data set and upload an updated copy of the workbook, PowerPivot for SharePoint allows business users to setup a data refresh schedule per workbook:
If PowerPivot contains data from different data sources, users can specify a schedule for each of those data sources independently as well. This is useful in the case where operations data needs to be refreshed more frequently than reference data, for example.
Some PowerPivot users will spend significant time in importing, cleaning and shaping the data for their use. Others in their workgroup may want to reuse this effort. Today, one option available is to save a copy of the original workbook to retain the data and logic and delete what’s unnecessary. This unfortunately duplicates the data and breaks a link from automatically receiving future updates to the original spreadsheet. If that is too painful, users can choose another approach, of putting everything in one spreadsheet, with different sheets reserved for different users. Both of these options are less than ideal.
The mechanism that PowerPivot for SharePoint uses for extracting embedded data from workbooks and making it available for queries from Excel services is also available for any other application that connects to Analysis Services. i.e. as a user, I can create two different scenarios:
This was a quick overview of the features PowerPivot for SharePoint 2010 introduced in the August CTP. Hopefully you got a taste of how the PowerPivot team looked at this space holistically and the usefulness of our first release. As usual we welcome your comments either on this blog or on the PowerPivot blog itself, a site that contains many deeper articles on this topic.
Can you give some comments on the PowerPivots relationship with Analysis Services server cube platform ? Will the model from PowerPivot be always in memory or later distributed ? Or will PowerPivot in some cases eliminate the use of Analysis Services server environment ?
Michael: PowerPivot uses Analysis Services internally. The actions in PowerPivot for Excel are essentially modeling an Analysis Services cube behind the scenes and loading it with data based on user actions. In this mode there is no explicit service to manage, Analysis Service engine is running as a DLL within Excel. In the case of PowerPivot for SharePoint, Analysis Services servers are running on the SharePoint farms and will load the databases when necessary.
The PowerPivot database will be loaded in memory once it’s in use. When the database is loaded by PowerPivot for SharePoint, it will be unloaded based on its usage and the need for resources. Instead when it’s in use by PowerPivot for Excel, the database remains in memory until the workbook is shut down.
But how do you see this scenario - an excuse my still limited knowledge about Powerpivot:
I am a poweruser inside organisation and create powerpivot and publish to sharepoint. I need a hiearchical dimension with certain member properties on a level in that dimension.
Is that type of design possible in PowerPivot or would i then have to look toward Analysis Server solution ? I am interested in the borderline between Powerpivot and server solution.
Or maybe by creating a powerpivot the Analysis Services Server environment is auomatically created without using Business Intelligence develeopment studio ? So power pivot is a wizard for not having to create this ? But can it then later be modified directly in server if features are missing in power pivot ?
can you provide a code sample of the silverlight app that show the snapshots of excel ?
Michael - I'm on the PowerPivot team at MS. Some of the features you mention, like predefined hierarchies and member properties, are not supported in PowerPivot v1, but I could see us adding them quickly.
And no, PowerPivot models can't be edited in BI Studio. For SQL 11, we may revisit that. But for now, the way I look at it is, PowerPivot models that become very important in an organization are candidates for IT to absorb, and re-implement in BI Studio.
More good news: 1) it will be easy to identify those models, thanks to PowerPivot usage tracking on SharePoint and 2) Once the business users build it out in PowerPivot, iteration on requirements (a huge part of the cost of building cubes today) essentially goes away thanks to the existing PowerPivot model serving as a refined prototype.
I'm doing a running series of posts on my website that you also might find interesting. Check out the Great Football Project in particular.
"More good news: 1) it will be easy to identify those models, thanks to PowerPivot usage tracking on SharePoint and 2) Once the business users build it out in PowerPivot, iteration on requirements (a huge part of the cost of building cubes today) essentially goes away thanks to the existing PowerPivot model serving as a refined prototype."
Is the expectation then that most PowerPivot models will not draw data from a data warehouse or data mart (where decisions about dimensions and measure groups have already been made)?
(Posting again because it appears my last two posts did not go thru)
PowerPivotPro is where I am posting the football project. My name is hyperlinked to that site, above.
Colin - no, Data Warehouses remain crucial in the PowerPivot world. Clean, business-standard data is just as important as ever. In some ways, more so. And my hope is that IT will have more time to provide that kind of data when freed from some of the reporting tasks transferred to biz users via PowerPivot. (I posted an article and some comments on this topic on the PowerPivotPro site as well).
The lack of member props and hierarchies is more of a prioritization/resources thing on the PowerPivot team. #1 focus for this release, by far, is on building a successful environment for the Excel users, who are familiar with columns, tables, and reports - not measures, dimensions, hierarchies, and re-useable data models (which at first might strike the Excel users as a bonus side effect).
I love the idea of, in the future, adding optional features in the PowerPivot ribbon for defining hierarchies and member props, just to name two. It's just the first release after all :)
Direct link for the Great Football Project, where I am re-implementing a professionally-built, "traditional" AS cube using PowerPivot:
The series is really just getting started, so it's a good time to start "watching." :) A lot of the questions I see here will be covered in detail, and I'll be answering questions as I go.