More PowerPivot Consulting Observations

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