Saturday, October 16, 2010

PowerPivot For SharePoint

Are you in Canberra this week? even if not, you may want to read this post.

This week I am presenting in the Canberra SharePoint User Group about PowerPivot for SharePoint. I have spent the weekend reading and testing this application - mostly trying to figure out how it is different from Excel Services.

Basically, powerpivot for excel is a powerfull database analysis add on to excel that creates connections to external databases, analyses the relationships for you and allows you to create great pivot tables and charts from this relational external data. This makes creating pivot tables from complex databases very simple.

PowerPivot for SharePoint is a service application that you install from SQL2008R2 features. This adds the ability to open the workbooks that were created by powerpivot on the browser, while still remaining connected to the external database. It also adds a "powerpivot gallery" list template that creates a special document library with a nice and smooth interface to allow the users to look for the pivot sheet they want to open, and it has the default "open in browser" behaviour. The data gets refreshed and accessed on the server, so even users with weak desktops can analyse huge databases (up to 100 million rows - depending on the amount of RAM the server has). A good high level overview can be found on the powerpivot blog on MSDN, installation instructions on MSDN and a demo and a lot of marketing information on the official powerpivot site.

Two thing you should know:
to allow the web application to authenticate to the database the pivot is connected to, you will probably need to activate the "Claims to Windows Token Service" service (which is by default off if you used the configuration wizard to configure for you). Otherwise, the power pivot will keep giving you authentication errors.
The last thing is a problem with the way the powerpivot application is deployed. Don't ask me why, but there is a problem with how it is deployed, as described in this powerpivotgeek's blog post. If you get an error when opening a powerpivot gallery list, check out the geek's post for the solution (simply deploy the solution to the web application...).

So, if you are in town, come say hello and watch me install, set up, and run powerpivot in the Canberra user group. If not, you have a chance to see me next week in the South East Asia SharePoint Conference in singapore.

No comments: