The “Ferrari” of PowerPivot Books

Update: My Book is Ready

***UPDATE: My book, which explains the PowerPivot formula language (DAX), in down-to-earth fashion tailored to the Excel audience, releases November 6, 2012. I wrote it to fill the “DAX for Excel people” gap that existed between all of the previous books.  People have been asking me seemingly forever to do this, I finally got around to it.

Click here for reviews and ordering options.

Original Post…

A Respected Authority

A little background that is relevant:  when I left Redmond in July 2009, the DAX formula language only barely existed – the formula editor wasn’t done, nor were the many DAX functions.  So when I started the blog a year ago today, and dove into the Great Football Project, I started with the bare bones – importing data, creating slicers, etc.

When I decided to dive into DAX shortly thereafter, it wasn’t long before I discovered Marco Russo.  That name was familiar to my colleagues on the Analysis Services team, as he and Alberto Ferrari (and Chris Webb) had long been recognized as elite authorities and authors on Analysis Services.  But I was not a longtime AS team member, so the name was new to me.

No matter how complex the topic or the formula required, Marco knew what you needed.  In fact, he seemed *drawn* to the more complex stuff while I was still grinding out the fundamentals – Pareto/ABC analysis?  I had to go refresh myself on what that *meant* before I digested his post on how to *do* it in DAX 🙂

Marco’s posts were a valuable resource, especially as I learned my way around a new world of calculation power.  They were useful as a reference – “how do I do X?” – and probably even more so as inspiration – “hey, this guy knows a LOT about MY product that I DON’T! – this aggression will not stand!”

In all ways, exposure to Marco was an excellent force for advancement.  It was my first introduction to the power of community .  I was honored, over time, to have some back and forth blog posts and comments exchanged with Marco.  Shortly thereafter, when I launched the [link removed due to 404] PowerPivot FAQ, Marco was in the first wave of invitees to contribute.

The long awaited book arrives!

Now, about a year after those first collaborative education efforts in the community, the walking PowerPivot/SQL/AS encyclopedia known as Marco Russo is now available in a compact form factor, a 371-page book on PowerPivot, co-authored with his SQLBI.com co-founder, Alberto “V12” Ferrari.

I myself have been particularly anxious for the release of this book.  Mr Excel’s book and Denny’s book were excellent “zero to sixty” introductions and I reviewed them from the perspective of people ramping up on PowerPivot for the first time.  I have told many people that the Marco/Alberto book is the first one I am going to read primarily for my *own* benefit, and then review accordingly.

Positioning and Audience

My high level impression after reading the book jives with my expectations:  it is, at times, quite a bit more advanced than those prior books, which is a good thing for my purposes.

But it does NOT assume that you already know PowerPivot.  The book starts from scratch just like the other two books.  A good thing for the audience at large, for sure.  The first few chapters flow a lot like the first few chapters in the other books.

On net, I’d recommend this book to one of two audiences:  BI/SQL pros starting their PowerPivot journeys from scratch, and Excel/SharePoint pros who have read one or both of the other books (or have a decent amount of hands on experience already) and are ready for some more advanced techniques.

Assorted Highlights

Without giving away too much, here are some top level highlights:

1. The little “Note” boxes throughout are HIGHLY useful.  Most of them were familiar to me, but many were not (there are probably 150+ of these in the book, so even if you are familiar with 3/4 of them, that means there are about 40 gems waiting for you).  Simply scanning every Note in the book would be a very valuable exercise.
2. Coverage of database jargon and techniques that are both foreign and useful to Excel pros – good to see them cover this ground.
3. In-depth explanation of data modeling, aka “how to layout and relate your source tables for best results” (more on this below).  An excellent treatment of this largely uncovered (to date) topic.
4. Real-world recommendations that can only be gleaned from applying the technology in practice, like “don’t trust the relationship autodetect, create them yourself manually to avoid confusion.”
5. Delivers, 100%, on the promise of teaching me new things.  In fact, reading the book on the plane was quite frustrating – all I had was my netbook, which does not have PowerPivot on it (I know, an oversight on my part – but given the size of data sets I typically work with, not getting around to the netbook install is forgivable).
6. Some of my favorite tricks exposed!  I have a number of blog topics queued up that I never seem to get to, like how to use a slicer as a general UI device (without relating it to your other tables at all), and several of those little tricks are covered in this book.  Really, really cool stuff.

Warning:  This is Your Brain on DAX

Plus they have spent a professional lifetime up close and personal with the analysis services engine, as well as the team.  That presents a challenge at times, because I am not sure they realize how much they grasp and know intrinsically.

As a result, at times the book takes you from zero to sixty…  then jumps straight to one-fifty without bothering to acknowledge seventy through one-forty on the way 🙂

Typical experience for me when reading pages about 190+ follows:  “Yeah, I’ve done that… seen that, yeah, that makes sense… ok, never thought about that in that manner but I can see how that’s better…  WHOA what the hell just happened there…  geez I am not sure I grasp the implications…  I’m gonna need to try that on my own data when I get home in order to understand…”

…And That’s Why You Read This Book!

With the warning above out of the way, let me stress:  that power, and intelligence, is why you will want to read this book – if not now, then later.  And you will want to re-read certain sections.  And keep it handy as a reference when you encounter complex problems.

Let’s put it this way:  I know PowerPivot REALLY well.  From putting it in practice over the past year, I know a bunch of things that Marco and Alberto don’t know, in fact (although I will see them in a couple of weeks and swap stories, so that is short-lived, heh heh).

So, with that in mind, let me show you the following picture, at right.

All the new power that is hinted at…  it’s like PowerPivot v2 already shipped.  I am positive there are multiple things I am doing today that can be done more efficiently.  My pride is injured…  now I must go return the favor at next month’s conference in Seattle – Alberto/Marco, let’s make sure we have time to swap stories 🙂

Suggestions on how to get the most out of it

Here’s a few things I recommend as a survival guide for navigating the genius in these pages:

1. Learn CALCULATE, ALL, and maybe even SUMX (in that order!) before digging into the DAX chapters.  The book introduces those functions in an order that I think could be a bit overwhelming.  Master the simple (and amazing) power of those functions before diving into the deep end.
2. If you plan to use PowerPivot for SharePoint, get used to its limitations first – there are a number of features touched upon in the book that do not operate on PowerPivot for SharePoint (ex:  VBA, and Linked Table Refresh).  Since the book presents multiple techniques for every scenario, knowing up front what to ignore helps you focus on the techniques you will end up using a lot.
3. Test techniques with your data volumes – at Pivotstream we routinely use datasets with tens of millions of rows.  Things that perform well with smaller data volumes sometimes break down at industrial scale (ex:  Rank measures, Many-to-many measures).  Also, the book gives a lot of advice on calculated column techniques – at Pivotstream we have found that calc columns should rarely be used, and we move them all into SQL instead.  We get better db size as well as measure performance as a result.  At high volumes, you want your fact tables as narrow as possible, and all imported, never calculated in PowerPivot.  Of course, getting SQL changes isn’t always an option, so feel free to ignore this advice 🙂

Summary, and Relationship to Other Books

In short, this is by far the deepest treatment of DAX and modeling that you will find under one cover.  That’s true today, and very likely will remain true until these guys write their next book.    It’s the holy grail of modeling books.

The SharePoint content is thin, only about 15 pages, and it’s clear that Marco/Alberto have focused a lot of time on the modeling experience (which is good, I am glad they didn’t waste much effort there, choosing to stick to their strengths).  For SharePoint stuff, you should invest in the Denny book.

I don’t recommend it as the place to start with PowerPivot.  This is the book you read to *master* it.  Starting out, I recommend either Bill’s book or Denny’s book, or both, given the different perspectives of each.