Author: Bill Jelen
Publisher: Que, 2010
Pages: 336
ISBN: 978-0789743152
Aimed at: Excel users working with huge datasets
Rating: 4
Pros: Lots of advanced tips and workarounds
Cons: Niche topic
Reviewed by: Janet Swift

PowerPivot is an exciting free add-in to Excel 2010 - is this the book you need to get you started with it?

This is a hands-on treatment of PowerPivot and to get the most out of it you do need to follow it step by step. Chapter 1 walks you through the preliminaries of using PowerPivot - and that's not as straightforward as you might imagine. First you need Excel 2010. This entails choosing between the 32-bit and 64-bit version so there's some advice about which to opt for. If you already use Excel 2010 you may be surprised that you haven't come across PowerPivot - but it's not on the Office DVD and you have to go to some effort to finding and installing it - which this chapter guides you through.

The benefit of PowerPivot is that it allows you to work with huge datasets. So the next problem is obtaining sample data - and this problem is solved in that data can be downloaded from the author's MrExcel.com website. Again this isn't entirely straightforward and involves importing data in CSV format from a text file. It may not be the easiest way to work with data but it is realistic and if you follow the steps by the end of the chapter you will have merged data from two sources to produce an asymmetric report that would not have been possible using pivot tables.

Chapter 3 looks at both the benefits and the drawbacks of PowerPivot. It starts with the plus points: creating a single Pivot Table from multiple sources, using massive amounts of data and fitting more data into memory, building asymmetric pivot tables and a list of more technical ones. It then sets out the downsides starting with losing Undo - which it explains is a disadvantage of add-ins in general. While Jelen has a long list of shortcomings on the whole he presents workarounds and so this chapter ends up being pretty positive.

Chapter 4 covers the various methods of getting data into PowerPivot. It looks at working with existing Excel data, SQL Server, and then goes back to importing from a text file - this time using filters to load a subset of the same data we encountered in Chapter 2. It then mentions working with data from ATOM Feeds which it points out are commonly encountered when working with SharePoint - and of course SharePoint has its own PowerPoint capability. 

Chapter 5:Creating and Managing Relationships is where we come to the "massive benefit" of PowerPivot - being able to create pivot tables from multiple tables. It is also the first point at which we encounter PowerPivot's new formula language DAX which, as explained in Chapter 6 stands for Data Analysis expressions and handles various operators and 135 functions, some of which are similar to Excel functions while others are completely new. In Chapter 6, which is about adding calculated columns in the PowePivot data sheet, Jelen looks at 71 DAX functions while others are introduced in Chapter 10: Using DAX for Aggregate Functions and Chapter 11: Using DAX for Date Magic.

On the whole readers of this book are likely to have already worked with Excel's Pivot Tables and in this case they will find themselves on familar ground in the next two chapters: Chapter 7: Building Pivot Tables and Chapter 8: Cool Tricks Native to Pivot Tables which gives Jelen an opportunity to demonstrate some clever capabilities. 

Then in Chapter 9: Cool Tricks New with PowerPivot he shows how to work with the multiple pivot table layouts that let you do things such as building a report with two pivot charts. This is followed up by the two chapters that introduce the "massively powerful" DAX measure you can add to pivot tables.

Chapter 12:Named Sets, GetPivotData and Cube Formuals returns to techniques that are available in Excel's Pivot Tables and again demonstrates some advanced features including some for report formatting. However, PowerPivot adds some powerful reporting structures and Chapter 13:Final Formatting: Making the Report Not Look Like Excel shows how to produce an impressive and informative report that looks like a dashboard.

Up till this point the book has covered the free Excel client-side add-in. Microsoft also has a server version and the final chapter briefly looks at the benefits of upgrading to it - mainly to access its Reports Gallery. The book concludes with an Appendix that lists some PowerPoint resources - including an add-in to this add-in!

This book it is fast-paced and is on a mission - to get those who are already Excel Power Users to adopt PowerPivot. This makes it a niche book - but one that will appeal to the right reader.