At Simba, because we are in the business of MDX Providers that are typically accessed via Excel PivotTables, we sometimes get asked about Microsoft's new PowerPivot add-in to Excel 2010. Let's talk about regular PivotTables first before tackling PowerPivot. Excel can natively take a bunch of tables that you might have in internal worksheets and pivot them. This takes some expertise in Excel, but if the tables are like Business Intelligence (BI) fact and dimension tables, the result is like a BI (so-called) "cross-tab" report. It's nicely drillable and filterable, plus you can add, remove, and swap dimensions on axes. If the above tables are not pasted or imported into Excel, but are sourced from a 'connection', you can also refresh them.
However, Excel 2007 can only handle about 1 million rows, so this technique is somewhat restricted. Most of Simba's users just use Excel's MDX capabilities against an OLAP cube/source that is maintained by IT, and this gives a flexible enough and intuitive user experience to casual Excel BI users.
PowerPivot is an Excel 2010 add-in that has similar functionality to the raw pivot ability of Excel, but uses columnar compression so you can fit 100 million rows in a 2GB spreadsheet file. Such spreadsheet files might take a long time to load or refresh though. PowerPivot adds more menu elements to the Excel app that is hosting it, and has various automatic features and wizards to help you do the pivot operation. But this still requires understanding of pivoting itself, and may even require use of Data Analysis Expressions (DAX) language for creating sophisticated BI reports.
PowerPivot is a good tool for "power" Excel users, and puts more power in their hands to define very custom reports than deferring to a big IT OLAP system. However, PowerPivot doesn't address the usability challenge of "Pervasive BI". And “casual” users also may only need the lastest data for a narrow OLAP query, not a reload of all the relational data. Thus casual BI users may find that using Excel direct against a true OLAP source to be the most friendly and flexible BI reporting experience for their purposes.