The “Ferrari” of PowerPivot Books

October 26, 2010

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.

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 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.

PowerPivot Mind Meld: To Consult is to Train

October 14, 2010

"And then, Kirk, you will explain it to me"

“When you wake, you will know how to use the EARLIER() function.”

Last time, I trailed off and promised some heresy in this, my next post.

But I realized in the meantime that I left out some other important observations, and I don’t want those lost in the smoke if I start tossing grenades.  So, let’s get those out of the way first.

Does your org need PowerPivot Training, or do you need Consulting?

Traditionally, the role of consultant loosely translates to “I will perform the required tasks for you.”  (Or in the case of pure “talk” consultants, “I will tell you what *you* should do” – ironically, these people usually charge the most).

Trainers, by contrast, have a different role:  “I will teach you how to perform the required tasks yourself.”

To the customer, there are advantages to each, of course.  But with PowerPivot, given that it’s a self-service BI tool, training is a natural fit.

You should not equate “training” with “go sit in a classroom,” however.  In my experience spinning people up on PowerPivot, the best training involves a lot of “collaborative consulting.”

Hybrid:  Training Against Your Own Data and Biz Problems

My preferred approach:

  1. Cover the basics – there are some fundamentals that you just need to absorb, whether we are talking about PowerPivot for Excel or PowerPivot for SharePoint.  I have a few areas that I like to focus on specifically, rather than trying to cover everything.  The goal here is to get everyone on the same page for step 2, where the real learning takes place.
  2. Pick a business problem and dive in – once the basics are in place, we pick a business problem and start building a model.  Typically this means *I* am building the model, but unlike a consultant, I build it in front of you.  This is a means of explaining the principles learned in part 1, and fostering retention.  It also becomes the natural agenda:  I introduce new techniques and principles when they are needed, rather than up-front in some abstract format.
  3. Repeat on a few more problems – Pick another business problem.  Or a different approach to the first problem.  And dive in.  Repeat. 
  4. Backfill the remaining “patterns” – There are about 10-20 useful patterns that I use in my own work, over and over again.  Naturally, steps 1-3 cover a subset of those, but at some point it also makes sense to take a break and make sure the rest get covered as well.

The Changeover is the Proof

PowerPivot Changeover Invariably, at some point in this process, after we’ve covered enough ground and been through the cycle a couple of times, “it” happens:  The clients take the keyboard and mouse.  You almost don’t notice it, because it’s such a natural transition.  And at first, there is still a LOT of interaction between consultant/trainer and client, as I guide, explain, help troubleshoot, introduce new techniques as needed, etc.  

The movie goes on and no one notices  

But an hour or two later, I typically find myself sitting quiet for minutes at a time.  And that is how it is supposed to be!  You know your data, business rules, and needs better than anyone. 

If you (or your designated Excel pros) are not “driving” before your PowerPivot consultant/trainer leaves, something has gone wrong.

Summary of Why Hybrid is Better than “Pure” Consulting or Training

  1. Greater Relevance – PowerPivot offers a lot of power, for a lot of different situations.  You are not realistically going to sufficiently cover every aspect of PowerPivot in 1-3 days, nor do you need to. 

    Using your own data and problems is a natural way to focus the training on the features and techniques most relevant to your business today.  You will naturally learn more over time.

  2. Better Retention – it’s a fact.  Human beings are just much more engaged with things that directly affect them (like their own data, problems, and questions).
  3. Troubleshooting Skills and Agility – especially when you first start out, you will make mistakes.  Figuring out how to fix mistakes, or even just finding a better solution, requires constructive skills that you just don’t learn in a classroom.  

    When you have encountered even a handful of real-world problems, and participated in solving them, you are in a mindset that prepares you much better for the next unknown.  (This is very much like normal Excel or any other computer skill).

  4. The Artifacts are Templates – the natural product of hybrid training is a collection of working models that speak your business language.  These are by far the most powerful kind of templates and references for future work.  As a bonus, they also serve as “proof of value” for colleagues who come along later and ask if PowerPivot can help them.
  5. Validation of Value – A subtle but important point.  How do you know, after sitting through classroom training, how much you have really retained?  Similarly, if a consultant comes in and just builds something for you, how do you know you can extend it later?  If you train on your own data, however, and are part of the process, there is little room for doubt. 

    (Plus, I suspect that only PowerPivot pros who really know their stuff are willing to conduct a rolling, dynamic training approach like this, whereas the quality level of classroom trainers and consultants who work out of sight will vary quite a bit.)

More PowerPivot Consulting Observations

October 12, 2010

One of the Greatest Movie Ending Sequences of All Time

“I’ve…  SEEN THINGS… you wouldn’t believe.”
-Roy Batty

Do you experience “lightning bolt” moments?  Instants where something just hits you so clearly that your perspective changes permanently?

Personally, I live for those moments.  They are rare, of course, but oh so worth it.

I recently spent a couple days in San Diego, setting up a client with a self-service BI system based around PowerPivot.  I came away with several new (or newly-strengthened) observations, a couple of which were delivered in that lightning bolt manner.

Sharp People + The Right Tools = A Lot Happens in Just 3-4 Hours

In order to understand the observations, you really need to know the context of what we were doing.

The lightning bolts hit late in my second and final day of working with these folks.  We’d spent about a day and a half doing hands-on training with their data sources – I find that more effective than canned training with sample data.  Of course, to cover enough ground in a short period of time, you have to jump around a lot.

So it wasn’t until the last few hours that we settled in and focused on building a single complete model, end to end.  In those last few hours, we did ALL of the following:

  1. Sketched out the goal – a particular report/model geared at a particular business purpose.
  2. Discovered flaws in the underlying SQL sources that needed to be corrected.
  3. Grabbed the DBA, explained the problems.  He fixed them in real time and gave us a new query to use.
  4. Successfully built the report envisioned in step 1 – a report that simply DID NOT EXIST anywhere at this company beforehand.
  5. Explored the report (by now I was mostly an observer, and they were driving).
  6. Determined that the report, which they had wanted for months, was not actually the report that they needed!  (Only after seeing and playing with the data was it clear that the answers were elsewhere).
  7. Sketched out a NEW model and report based on that new wisdom, and built it
  8. Explored the report using a variety of different visualizations.
  9. Confirmed some of their long-running hypotheses, and gained some new insights.
  10. Discovered mysterious anomalies in the data, investigated them, and explained them.
  11. Identified improvements that could be made on the data COLLECTION side (in their customer-facing systems) in order to enable an entirely new class of deeper insight.

Yes, ALL of that transpired in just 3-4 hours.  Rapid Iteration indeed.  In one afternoon we did what could take a year:  2 full iterations on their core model.  Plus #11 is just crazy – feedback into the operational side of the house in order to enable v3.

My Jules Winfield “moments of clarity” follow.

“Failure” is an Inevitable Part of the BI Process… so Fail Quickly

Look back at the list above.  Steps 1-5 are a cross-section of what happens in a traditional BI project:  Sketch requirements, execute, debug, deliver.  Of course, this all happened in about 90 minutes, as opposed to a few weeks or months.  I’m starting to get used to that.

But look at step 6:  How many traditional BI projects chew up weeks/months/money, deliver 100% what you asked for, but ultimately, tell you much less than you were hoping?

To implementers and consumers of traditional BI solutions, I bet that sounds a lot more familiar than you’d like.  After all, BI is as much a human process as it is a technology process.  Here’s the crux:

Requirements definition is only informed by what you know TODAY!  And since BI projects are by definition aimed at providing information that you currently lack, BI requirements definition always involves a certain amount of guesswork – much more guesswork, in fact, than we typically acknowledge.

So BI projects sometimes dramatically under-deliver relative to expectations, and through no fault of the players involved.  Of course, that’s little consolation when tons of time and money are down the drain.  After that amount of sunk cost, it’s awfully hard to pony up the time and money for Round Two isn’t it?

But in an effective Self-Service system with PowerPivot, Round One only took 90 minutes and four people!  We weren’t exactly discouraged – in fact we were EXCITED to have learned what we really needed to build :)

Side Effect:  Do Your Traditional BI Prototypes with PowerPivot

This is obvious and people have been saying it for awhile now, but just in case:  even if you are planning a traditional BI solution, do yourself a favor and implement Round One in PowerPivot.  Flush out all of these initial insights up front, refine requirements, and only then commit large chunks of time and money to the formal project.

…Continued Later This Week

This post is already running a bit long, so I will save the rest for the next post.

Plus, the most controversial and unapologetic observation is yet to come, and deserves its own headline.

Added a Search Box

October 11, 2010

Hard to believe, but I’ve been doing this blog for nearly a year now and NEVER offered anyone a way to search it.  Me = not so bright sometimes.

Anyway, there it is now, on the right.  Give it a shot – there are over 180 posts in the archives, lots of topics covered that you may have missed.

Congratulations Kasper!

October 7, 2010

You Do Not Talk About PowerPivot Club

“You wanna switch seats?”

OK, I’ve been slow to post about this with everything going on, but some of you have already heard that Kasper has some exciting news.

You know that job at MS that I vacated back in February?  Kasper is filling it.


When I met Kasper in person for the first time this summer, down at TechEd, I already knew how sharp and enthusiastic he was.  But in person, surrounded by all the hints of what MS BI and SharePoint were planning for the next versions, I saw something else:  Kasper has TONS of energy for building new software – passion for doing the sorts of things that you don’t get to do unless you work somewhere like MS.

You can’t teach someone to have that fire.  You either have it, or you don’t.  And Kasper definitely has it.  I remarked casually, mostly to myself, that it was a shame he lived in Holland, because he belonged at MS.

Kasper recognized that, too, because about a week after he got back to Holland, he told me privately that he was going to interview for my old spot :)

It’s funny how things work out.  The guy that co-authors this blog with me, from another continent, is coming over to work on PowerPivot and other BI stuff.  Sometimes life is cooler than anything you can make up.

Kasper, you are going to do an amazing job in your role.  And I am jealous of how many cool people you will be getting to interact with on a daily basis.  Looking forward to seeing you at PASS next month.  Hoops rematch?

Below is Kasper’s original post, from his personal blog:

By Kasper de Jonge, repost from PowerPivotblog.

I’m very proud and pleased to announce that I’ll be joining Microsoft as Program manager for the SSAS team as of November 1st. This means some big changes for me, first of all I’ll be moving from the Netherlands to Redmond to work at MS HQ.

In Redmond i’ll be joining the SSAS team to work on the next versions of PowerPivot and SSAS. I really love the concept of Self service BI and of course PowerPivot, this will give me the opportunity to help shape the future of Self Service BI. The funny thing is that I actually will fill the job opening that was created by Rob leaving the SSAS team.

What will this mean for my blogging?
Fortunately, nothing. This blog is a personal blog, paid for and maintained by me. I will try to stay very active not only on the current version but also in beta’s. We’ll see where I can find the time to blog. I love the sql/bi community so I want to keep that up while working for Microsoft, at what capacity we’ll have to find out.

What is a Program manager?
A program manager is a lot of functions combined in one, gathering information from partners, MVP’s and you guys, write functional specs, a project manager, working with the dev team, validating the product after the design and evangelize the product, and a bunch of other tasks, for more info see this blog post: How to be a program manager.

I’m looking forward to the exciting time ahead and be your voice on the product team!

Illustrated guide to Excel Services Chart Rendering

October 4, 2010

  “Hey, will this chart render unchanged in Excel Services, or will it look different?”

MY STOCK ANSWER:  “Hmm, I forget, let’s try it out”

I hate giving that answer.  So today, I ran a little test.  Simple workbook, 12 charts that are all flavors of fancy.  Not an exhaustive test, but a reasonable sample.  Here are the 12 charts in Excel client (desktop).  Click for a larger version.

12 Charts in Desktop Excel

Green Circle = Renders 100% identical in Excel Services
Red X = Renders less than 100% identical (sometimes small differences, sometimes large)

Now, here are the same 12 charts rendered in Excel Services:

Same 12 Charts, As Displayed on SharePoint (Excel Services)

Again, note that being flagged with an X isn’t the kiss of death – I just didn’t want to bog down in “grading” the differences.  Sometimes the changes are big (top left chart) and sometimes the changes are astoundingly small (bottom middle chart).

Either way, having an illustrated reference is going to be helpful for us, so I thought I’d share it.  Feel free to submit questions/comments/other examples.  I may expand this reference over time.

Top 5 Ways PowerPivot Helps Excel Pros

October 1, 2010

Sorry for the zero posts so far this week.  I have many things to catch up on after a couple days of consulting in San Diego (which was a FABULOUS experience).

I have a lot to talk about next week here on the blog:

  1. Something big and amazing is happening in Kasper’s world – many of you have already heard but I think it deserves some special highlighting :)
  2. Recent observations from the PowerPivot consulting front
  3. Screenshots and video of PowerPivot for SharePoint on iPad
  4. Some new observations about slicer performance

In the meantime, today the official Excel blog put up a guest post from me that I think you may find interesting: