The “Ferrari” of PowerPivot Books

October 26, 2010

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.

Marco’s early blog and forum posts, however, endeared him to me rather quickly.  His longstanding knowledge of MDX, combined with his enthusiasm, had rocketed him to a level of DAX understanding that was frankly…  daunting.  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 :)

This Aggression Will Not StandMarco’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 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

Your Brain On DAX I want to say this loud and clear:  When it comes to stretching their brains in abstract directions, which uber-advanced DAX requires, Marco and Alberto are WICKED SMART.  Like, off-the-charts, immeasurably, smart.  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).

Dogeared PowerPivot BookSo, with that in mind, let me show you the following picture, at right.

That middle section – that’s all of the pages I have dog-eared as ASAP Priorities to try out.  Those are also the same places where my brain got thoroughly scrambled.  And that’s a GOOD thing.  Scrambled brain is an excellent indicator of expanding your capabilities.  So I’m thrilled to drill into all of this :)

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.

5 Comments | Observations, Trends, and Wild Speculation, PowerPivot Links | Tagged: | Permalink
Posted by powerpivotpro

Review: Prof. PowerPivot for Excel & SharePoint

August 19, 2010

 
Click to view on Amazon

No more delays!

Awhile back I reviewed Mr. Excel’s PowerPivot book and vowed to post a review of the other major PowerPivot book to date, by Denny, Ron, and Siva (all from the SQL team at Microsoft). 

I actually finished this book awhile back, but things got pretty intense at Pivotstream (in a good way), delaying my writeup much longer than intended.

So, no more delays, time to dive in and tell you what to expect.
 

Focus on The PowerPivot System

In my review of Bill’s book, I said that there was no better primer for Excel pros making the transition to PowerPivot, and that remains true.  Bill took the approach of “this is the greatest thing to happen to Excel in over ten years, so Excel pros, listen up, you’ve got an arsenal of new tools to deploy.”  And I think that is precisely the right approach to take with Excel pros who are getting started. 

But there are MANY other aspects to PowerPivot, and a lot of ground left to cover in depth.

The boys from Redmond have VERY different backgrounds from Bill, and those backgrounds shine through in their book.  You could almost read their book and Bill’s book and not be sure they were discussing the same product… and I think that is a very good thing.

Cutting to the chase, the Red book (I’m gonna call it that for short) covers a lot of things that the Bill book intentionally leaves to others:

  1. How PowerPivot compares to and interacts with traditional BI systems
  2. What it takes to implement an entire PowerPivot SYSTEM in your organization
  3. The technical underpinnings of PowerPivot, both in Excel and SharePoint
  4. A quantity of hyper-detailed tips and tricks that can only come from insiders
  5. Integration with a wide variety of data sources
  6. Planning and deploying a PowerPivot for SharePoint farm
  7. Monitoring and maintaining your SharePoint deployment
  8. Also covers a LOT of the basics of SharePoint, useful for SharePoint neophytes

What the book is NOT

1) It is not the first book an Excel pro should pick up.  It covers too much, and comes from a perspective that might seem a bit foreign.  Start with Bill’s book if this is your background.

2) Like Bill’s book, it is only a light treatment of DAX.  Again, this is wise, because DAX really deserves its own book.  Those seeking a detailed DAX book should look forward to an upcoming title from Marco Russo.

3) It does not cover the real-world gotchas encountered in a full adoption of a PowerPivot system.  After a full year of applying PowerPivot to real-world problems – six months as a product team member in exile (in Cleveland), and the last six months putting the full system into action at Pivotstream, I can say that there are a number of “gotchas” that you have to look out for in PowerPivot v1.  They are not fatal – Pivotstream’s PowerPivot platform is very much thriving – but you only discover them, and learn to avoid/overcome them, via real-world practice. 

Anyway, for a just-released product, it’s not like we can expect ANY book to cover that kind of thing.  There’s probably room for a book like “PowerPivot in the Trenches” but someone else needs to volunteer, as I, um, rarely get out of my trench :)

What the book IS – Consistently readable and info-rich

1) Great Information Density, but High Readability – When I finished the book, I immediately dropped an email to Denny/Ron/Siva and told them how impressed I was with the balance between density and clarity.  As a de facto tech writer myself throughout my career, I can say that I struggle with this, and typically end up jamming in too much at the expense of readability, or a 50-page doc with so much whitespace that 20 pages would have covered it.  Perhaps you have noticed.

It’s hard to put my finger on how the book strikes such a nice balance – perhaps it’s the editing, or the layout style – but I consistently noted how MUCH was conveyed on each page, while at the same time, it felt like a light read.  That’s saying a lot considering the list above.

2) Benefits of Three Product Team Authors – When you include all of the folks from teams like SharePoint, Excel, SSRS and others that contributed to PowerPivot, it’s probably safe to say that the product reflects the efforts of 200+ people for several years.  No one human being could possibly span all of that, and it only gets harder if you weren’t directly involved in that process.

These three guys were working on the book for a long time, while the product was still in development.  They all had different areas of focus, both in terms of natural affinities and in terms of dividing up their assignments.  They then had a lot of time, and access to the people who were building the product, to refine the content.

And then they all rigorously cross-reviewed each others’ chapters – you can’t get away with slips and omissions when two of your peers are on you, and they have the same level of exposure and access that you do. 

The benefits of this authorship approach are evident in the book.  You’ll see what I mean – I always expect some “uneveness” in a tech book of this length because the talents and endurance of a single author are themselves uneven.  Chapter to chapter, the red book’s quality remains consistent.

3) History of PowerPivot.  The book is sprinkled with a number of sidebars titled “Inside PowerPivot” that relate some of the human side of how the PowerPivot project got started, and evolved over time.  While not actionable in a technical sense, other people that I’ve talked to about this book, without exception, the first thing they say is how much they enjoyed these sidebars.  That’s saying a lot given the two points above.

Overall Recommendation

If you fit any of the following descriptions, I rate this book as a must-read:

  1. Anyone leading or contemplating a PowerPivot deployment (the total system, as opposed to just viewing it as Excel 2010++)
  2. Excel pros who have read Bill’s book and want to expand their expertise – I suspect a lot of you will decide, correctly, that a full PowerPivot system makes your talents a lot more valuable and visible, and will find yourselves on point for test deployments
  3. BI or SharePoint pros who are ramping up on PowerPivot (or evaluating PowerPivot’s impact on their work)

4 Comments | PowerPivot Links | Tagged: | Permalink
Posted by powerpivotpro

Two Excellent PowerPivot Books

July 26, 2010

PowerPivot Books

Awhile back you may recall David Coe winning our XL Monkey Design Contest, the prizes for which were three unreleased (at the time) books:  two on PowePivot specifically, and one on Pivots in general.  Autographed by the authors of each:  Bill Jelen (Mr. Excel) for two of them, and Denny Lee, Ron Pihlgren, and Siva Harinath for the other.

Well, those books are all released now, and Bill/Denny have sent me the signed copies for delivery to David. 

Even better (for me), they each graciously included signed copies for a guy named Rob Collie.  So, PowerPivot books have supplanted Angry Birds as my pre-sleep nighttime routine for the past few days.

Humorous Aside:  Flattery will get you everywhere!

Shrewd promoters that they are, Denny and company had the wisdom to list me in the acknowledgement section of the book, even going so far as to list me first. 
 
PowerPivot Yoda 
PowerPivot Yoda says:  “Wise is the author who prominently thanks those with the capacity to promote.”

 
Mr. Excel takes this even further, with the first two words in the book (after About the Author) being “Rob Collie.”  He even thanks my wife Jocelyn!

All future PowerPivot authors, take note of this.  (Actually, all authors take note of this, regardless of topic, heh heh).

Back to Serious:  Reviewing the Books

All of that fun stuff aside, I think I’ll briefly review these books here on the blog.

Since Bill’s book (the green one) arrived first, I’ve had time to read it already, so I’ll review that one first.

Excel People, Start PowerPivot Here

The arrival of Bill’s book is conveniently timed, since my last post was from an Excel power user who wanted content more tailored to his viewpoint and history.

My biggest overall conclusion after reading Bill’s book is that Excel users will be hard-pressed to find a better place to start their PowerPivot journey.  Bill is not a SQL guy and he is not an MS employee – he has been building spreadsheets in the wild since before Pivots even existed.  And for many years now he has made his living simply teaching others to get the most out of Excel.

That history and perspective shows through in the book.  Reading it is VERY different from reading any of the MS documentation on PowerPivot for instance – that MS content is excellent at describing PowerPivot and how to use it, it just isn’t written by a multi-decade Excel maestro, so it doesn’t tell Excel users, in detail, what will be familiar to them and what will be new.

Example:  the book contains a table listing all the pros and cons of PowerPivot-style pivots versus traditional Excel pivots.  I wouldn’t have come up with half of these differences despite my Excel pedigree, and I consider it the definitive list on the topic:

PowerPivot versus Traditional Pivots

Like a true Excel nerd, Bill even has a numerical Rating column, listing each pro/con as a positive/negative value, and then adds them all up at the bottom to generate +181 as the overall rating.  I wonder if Bill is like this at breakfast, comparing waffles to flapjacks using AutoSum?

(And yeah, I’m intentionally leaving the resolution poor – you’ll have to get the book, as I am not in the habit of republishing other people’s work like that).

Continues Throughout, Covers Every Aspect of PowerPivot

That perspective and experience is maintained cover to cover.  “Here ya go Excel pro, this is why you should care about feature X, when you should apply it instead of traditional Excel feature Y, and when you should stick with the traditional approaches.”

And it goes end-to-end through PowerPivot with this perspective, from data import, editing/cleaning, table relationships, DAX formulas of all types, pivot and slicer layout, formatting, workarounds galore, and touches on SharePoint at the end.  As I said, if you are coming to this from the Excel world, I think this is a great book for you.  It’s a quick, informative, and personable read.  Well worth the $23 at Amazon.

=IF(MOD([PageNum],3)=0,”Rip MS a New One”,”Wait til next page”)

Part of the personable thing:  Bill doesn’t spare MS when he dislikes something.  “Insane,” “crazy,” “hate” – these are a few of his favorite words.  In a few places he rips into decisions that were personally made by me, or by teams I led back at MS.  For instance, he hates the new Compact pivot layout introduced in Excel 2007.  Bill, I’m ready to duel over THAT one.  (Look for my upcoming blog post, The PowerPivotPro Went Down to Akron).

‘Pivotpro drove down to Akron
His fingers tightly grippin’ the wheel
He was looking to find
An Excel author unkind
To pivots’ excellent look and feel

What the book is NOT

Clocking in at 294 pages, this book doesn’t try to do everything, which I think is wise.  I don’t think any Excel pro wants to pick up, as a starting point, a 1200 page bible.  This book is an excellent intro and you will hit the ground running fast, but at some point later, you will eventually go looking for:

  1. An in-depth guide to high-powered DAX measures
  2. An in-depth guide to the implications of various table structures and relationships
  3. Performance-tuning reference
  4. A how-to reference for deploying PowerPivot for SharePoint
  5. List of best practices, tips and tricks, workarounds for Excel Services on SharePoint

Like I said, as an Excel pro, you are MUCH better off NOT trying to tackle those up front.  You can get incredible mileage out of PowerPivot without once touching those topics.  You will want to someday, but you don’t NEED to, so I highly recommend Excel pros pick up this book as their starting point.

9 Comments | PowerPivot Links | Tagged: | Permalink
Posted by powerpivotpro