PowerPivot for SharePoint 2010 – The Business User’s Perspective

re: PowerPivot for SharePoint 2010 – The Business User’s Perspective

Rob Collie — Sat, 31 Oct 2009 22:34:24 GMT

Direct link for the Great Football Project, where I am re-implementing a professionally-built, "traditional" AS cube using PowerPivot:

powerpivotpro.com/.../the-great-football-project

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.

re: PowerPivot for SharePoint 2010 – The Business User’s Perspective

Rob Collie — Sat, 31 Oct 2009 22:21:56 GMT

(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 :)

re: PowerPivot for SharePoint 2010 – The Business User’s Perspective

Colin Banfield — Sat, 31 Oct 2009 13:45:30 GMT

"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)?

re: PowerPivot for SharePoint 2010 – The Business User’s Perspective

Rob Collie — Sat, 31 Oct 2009 12:00:03 GMT

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.

re: PowerPivot for SharePoint 2010 – The Business User’s Perspective

Luis Azedo — Fri, 30 Oct 2009 06:19:57 GMT

Hi,

can you provide a code sample of the silverlight app that show the snapshots of excel ?

TIA

re: PowerPivot for SharePoint 2010 – The Business User’s Perspective

Michael Vardinghus — Thu, 29 Oct 2009 19:29:31 GMT

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 ?

re: PowerPivot for SharePoint 2010 – The Business User’s Perspective

Michael Vardinghus — Thu, 29 Oct 2009 19:26:38 GMT

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.

re: PowerPivot for SharePoint 2010 – The Business User’s Perspective

Joseph Chirilov — Thu, 29 Oct 2009 17:21:47 GMT

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.

re: PowerPivot for SharePoint 2010 – The Business User’s Perspective

Joseph Chirilov — Thu, 29 Oct 2009 17:21:37 GMT

re: PowerPivot for SharePoint 2010 – The Business User’s Perspective

Michael Vardinghus — Thu, 29 Oct 2009 13:20:22 GMT

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 ?