PowerPivot for the Data Analyst: Microsoft Excel 2010 
Author: Bill Jelen PowerPivot is an exciting free addin to Excel 2010  is this the book you need to get you started with it? Bill Jelen's Introduction starts with "I am a PowerPivot FanBoy" and he goes on to share the story of how he first heard about it and his enthusiasm for this addin for that lets you work with far more data than in Excel.
This is a handson 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 32bit and 64bit 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 addins 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 clientside addin. 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 addin to this addin! This book it is fastpaced 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.


Last Updated ( Wednesday, 25 May 2011 ) 