Marco Russo and Alberto Ferrari have written a book called Microsoft PowerPivot for Excel 2010: Give Your Data Meaning that I finally got around to reading.  It is great book with many good practical examples. If you are not already familiar with PowerPivot and DAX I highly recommend reading and walking through the examples in the book and then reading the authors BLOGs linked above.

This book changed my perception of the value of PowerPivot and its usefulness in the Microsoft BI stack.  I greatly respect Marco and Alberto and as a rule I try to read everything they have written.

Another great resource is the Analysis Services and PowerPivot Team Blog

From the book:

Since we first began thinking about Microsoft PowerPivot for Excel 2010, we have had to revise our thoughts a number of times. First we needed to be persuaded to look at the tool seriously.

A first impression of PowerPivot might mislead you. We made this wrong evaluation too, at the beginning of our acquaintance with PowerPivot. Do not make the same mistake. PowerPivot is powerful and useful, and it defines a new path for the era of self-service Business Intelligence (BI).

Microsoft SQL Server PowerPivot for Excel is a new technology aimed at providing selfservice Business Intelligence (BI). PowerPivot is a real revolution inside the world of data analysis because it gives you all the power you need to perform complex analysis of data without requiring the intervention of BI technicians. This tool, an Excel add-in, implements a powerful in-memory database that can organize data, detect interesting relationships, and give you a swift way to browse information. These are some of the most interesting features of PowerPivot.

¦ The ability to organize tables for the PivotTable tool in a relational way, freeing the analyst from the need to import data as Excel worksheets before analyzing the data.

¦ The availability of a fast, space-saving columnar database that can handle huge amounts of data without the limitations of Excel worksheets.

¦ DAX  a powerful programming language that defines complex expressions on top of the relational database. DAX allows you to define surprisingly rich expressions, compared to those that are standard in Excel.

¦ The ability to integrate different sources and almost any kind of data, such as information from databases, Excel worksheets, and sources available on the Internet.

¦ Amazingly fast in-memory processing of complex queries over the whole database.

Some people might think of PowerPivot as a simple replacement for the PivotTable, some might use it as a rapid development tool for complex BI solutions, and others might believe it is a real replacement for a complex BI solution. PowerPivot is a great tool for exploring the BI world and implementing BI solutions. It is not a replacement for a complex BI solution, such as the ones built on top of Microsoft Analysis Services, but it is much more than a simple replacement for the Excel PivotTable.

PowerPivot fills the gap between an Excel workbook and a complete BI solution, and it has some unique characteristics that make it appealing to both Excel power users and seasoned BI

analysts. This book examines all the features of PowerPivot.