Powerpivot for excel is a powerful 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" behavior. The data gets refreshed and accessed on the server, so even users with weak desktops can analyze 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…).