A couple of weeks back I was approached by PackT Publishing, asking me if I’d be willing to read and post a review of Creating Data Models with PowerPivot How-to, by Leo Taehyung Lee. As I’m always interested to see what others are saying in this space, I agreed.
The e-Book is 40 pages of content (after you discount the copyright, author profiles and other stuff that goes with every book), and is intended to cover off PowerPivot 2010 from install to basic usage. Being an author, and having written many how-to webpages, I can say that it’s a lot to cover in that few pages.
Overall, you get a pretty basic glimpse into PowerPivot. Topics covered include:
- Installing PowerPivot
- Installing SQL Express
- Importing a bunch of tables
- Making a basic Pivot
- Making a Pivot Chart
- Adding another table
- Creating a relationship
- Creating calculated columns
- Optimization
The PowerPivot install is pretty simplistic; it basically points you to Microsoft’s site and tells you to follow the instructions. I guess you can’t complain about that too much, although I think I would have highlighted the pre-requisites a bit more myself.
Honestly, I was more than a little surprised to see the installation of SQL Express in here. Again, it was a very short coverage, and all in an effort to get access to the AdventureWorks database. (I’ve never understood the fascination with AdventureWorks myself.) Of all the pieces covered in this e-Book, I seriously have to question this one.
PowerPivot connects to Access databases, whether Access is installed or not, so that would have been a far easier route to demonstrate connecting to databases in my opinion. Yes, I understand that corporate users don’t like Access, but that’s not the point. Using an Access file requires no extra software be installed, unlike SQL Express, and therefore leaves no unneeded software on your machine once you are finished with the e-Book subject. To my mind, it would have made much more sense to provide a downloadable Access sample database, or even push the user to download data from an online feed that didn’t require SQL Express installation.
Back to the actual PowerPivot specific stuff though… The Author’s approach to pulling in tables was quite interesting to me; he connects to the database and pulls in a whole bunch of them… way more than he needs. I could criticize that, but I won’t, as that is EXACTLY how an Excel pro builds a PowerPivot solution. Suck in as much data as possible, and then figure out what you need to link to get what you want. The reason why this is interesting to me is that I had a conversation at the last MVP summit with some SQL MVP’s and they were horrified at this development approach. SQL pros explore data first, publish as little as possible, so that the solutions are optimized from the beginning. Excel people generally only optimize when their file is too big to email, or when it gets so slow they can’t stand waiting for it to refresh any more.
Cleaning up these unused columns was covered, although to my mind there were some key points that probably should have been made. I would HIGHLY recommend that before any users starts pruning columns from their workbook that they save it first. Then I’d refresh all of my pivots every 2-3 columns I remove. Yes, it’s painful. Yes, PowerPivot is slow to refresh. But the first day you accidentally delete a column that has a dependency and catch it, you’ll be thankful. Rebuilding PowerPivot model logic sucks. (Trust me, I’ve been there.)
The only true technical issue I found in the book is related to the final statements regarding version compatibility issues. The Author states that “… Excel files created with the older version of PowerPivot will be accessible in the new version…” While that can be argued to as technically true, it is quite misleading. PowerPivot files created in 2010 must be upgraded to be used in Excel 2013, and that writes permanent changes to the model structure, preventing it from being used in 2010 again. I think that the author should have been much clearer in this regard as mixed version Offices will find no joy when trying to share/co-author PowerPivot files.
Overall, the e-Book gives a fairly decent intro level coverage to the topic. It starts assuming no PowerPivot knowledge at all, and I can certainly see many things in here that remind me of the way I navigated my way through the basics of the PowerPivot learning curve.
If you’ve never used PowerPivot before, this e-book will walk you through some of the basics to get you up and running. But be aware that PowerPivot is not easy, and you’re very quickly going to require a more in depth book to create good models, let alone master DAX (the real power in PowerPivot.)
If you have used PowerPivot, created a relationship between two tables and based a PivotTable off the related fields, then this book wont’ do anything for you.
To buy directly from PackT, you can do so from the following link: http://link.packtpub.com/MQIfoM
