PowerPivotPro

“You keep using that word, ‘Intelligence.’  I do not think it means what you think it means.”

-Average BI Consumer

Wrapping up an old thread

Awhile back, I wrote parts 1 and 2 of “PowerPivot Consulting Observations” (part one here and part two here) in which I hinted at some potentially controversial conclusions…  but then I trailed off into other topics.  I have not forgotten, however, and it’s time to close the loop.  Let’s dig in shall we?

Revoke the License to the Word “Intelligence?”

Basically, my conclusion is this:  whoever coined the term “Business Intelligence” is a genius, because the term sparks the imagination.  It gets people excited about high-tech, hyper-sensitive equipment in the hands of inquisitive, focused, and nimble knowledge workers.

I’ve written about this many times before, of course, but the theme never gets old for me because it seems so absolutely central:  BI, as experienced over the past 10+ years, simply does NOT deliver on that vision.

Here are some adjectives that apply to most folks’ experience with traditional BI:  Slow.  Disciplined.  Iterative.  Consolidated.  Managed.  Controlled.  Foundational.  Structured.

“Intelligent” never makes the list.  Ever. 

And so, traditional BI, I think it is time to stop calling you “Intelligence.”  I’m absolutely serious that “intelligence” is a misleading label, and sets expectations impossibly high relative to what traditional methods and toolsets can deliver.

What SHOULD we call it though?  Data Stewardship?

I don’t really have any hope of changing the industry lingo – Traditional BI will still keep its “I” no matter what a few of us say, but as a thought experiment, I certainly think it is a valid exercise.  Because remember, I DO very much value the work that goes into traditional BI projects.  I just think the word “intelligence” is misplaced, and that completely miscasts PowerPivot, and undersells what it can deliver.

So, as a means of provoking discussion, I’ll throw out “Data Stewardship” as a potential replacement for the term “Traditional BI.”  Or is “Information Discipline” better?  Remember, the goal is not to demean traditional BI, but to distinguish it from the experience of things like PowerPivot (and, I suspect, the experience we will get with BISM in the next release of SQL).

I mean, once you’ve seen PowerPivot do its thing, you realize the two worlds couldn’t be more radically different.  With PowerPivot, business unit workers who normally glaze over at BI sit forward in their chairs.  They are engaged.  They are excited.  They want to dig deeper, they want to LEARN.

And, they want to engage with the folks who manage all of the critical business data.  They want to partner with them.

That is, of course, how it should be.  I just think that the nomenclature we currently use, putting both disciplines under the same umbrella of “BI,” does no one any favors.

Alternate Approach:  Recast PowerPivot as “NOT BI?”

Another idea:  since “BI” has meant information discipline for so long, and is therefore an entrenched term, maybe we should just describe PowerPivot differently?

Officially, Microsoft refers to PowerPivot as “Managed Self-Service BI,” which woefully understates its value.  Taking the adjectives from above, it often comes across as “You mean I can be slow, iterative, and deliver underwhelming business value ALL ON MY OWN?  WAHOO!’”

Back when Ariel Netz was Group Program Manager of the Analysis Services team (the PowerPivot team), he proposed calling PowerPivot “BI 2.0” – I liked that then, and I still like it now.

Any other ideas?  I’m just pretty sure that “Self-Service BI” doesn’t cut it.

Summing Up:  True Discipline vs. True Intelligence

Whatever we ultimately end up calling these two skillsets/investments, I think the core difference comes down to this:

Traditional BI is primarily the art of minimizing mistakes, and eliminating the paralysis that stems from confusion.  Whatever numbers are produced, you can be sure they are “the truth” according to all corporate policies and systems.

PowerPivot BI is the art of developing a much clearer business picture, and in near-real time.  It is the art of generating a much better, more informative truth. 

It isn’t going to deliver as much discipline as traditional BI of course, but still a LOT more than traditional Excel does.  Plus with BISM, PowerPivot models will be directly importable into the “heavy duty” toolset, and can therefore be seamlessly “taken over” and absorbed into the 100% managed, aka traditional, space. 

Perhaps we can think of PowerPivot BI as a “scout” for traditional BI, where scouting is an everyday, constantly evolving exercise, and traditional BI is more of a slow-churning background process that is always digesting the most core and relevant findings from the scouts.

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

Which problem do you prefer, when thirsty -
Too Much Bottleneck (left) or Too Little Structure (right)?

Answer:  Neither is preferred of course!

I’m part of an email distribution list that involves a bunch of BI pros, as well as a number of the PowerPivot engineers back in Redmond.  Last week on that list, I asked a question that I knew would draw some fire:

   “How many measures can I safely put on one table in PowerPivot?”

There were 2 flavors of response:  technical on one hand, and “um, is that really a good idea?” on the other.

Technical answer:  no practical limit to # of measures

The official technical answer was good news:  the number of measures defined on a table in PowerPivot should not have any real impact on performance, unless you are using all of them in a single query of course.  Measures that aren’t part of the current report won’t drag you down, in other words.

(I am told this is in contrast to the traditional Analysis Services product, which scanned all measures at once.  That prior approach is a good thing when you are using a large percentage of your measures in a single query, but a bad thing when you are using only a small percentage.)

So I immediately dug in and built a model that has nearly 1,300 measures on a single table

Yes, they are real measures.

Yes, the addin seems stable with that many measures in the field list, and performance does not seem to have suffered, at least not in my initial testing.

And yes, it was tedious.  More on this in a later post.

Modeling Hygiene Answer:  Why the hell would you do that Rob?

The natural reaction from the BI/DB pros was to suspect I was doing something wrong.

Now, I know myself pretty well, and the prospect of me Doing Something Wrong can best be described as Entirely Plausible. 

So I asked a few followup questions.  And it turns out, no, there isn’t any pure philosophical flaw in what I am doing, but they were still skeptical.

Diet, Caffeine Free, Cherry, Coke Classic, Zero…  Please

Here’s a joke that I play with my colleagues when they are leaving the room and it NEVER gets old:

“Hey, would you mind getting me a can of Diet Caffeine Free Cherry Coke Classic Zero?”

This joke is a roundabout explanation for how we end up with 1,300 measures.

Thing is, we only have 5-6 core numerical data columns in this model.  But all the permutations are what get us.  For example, we have Sales of course.  But we also have Sales in Prior Period, and Sales from Same Period Year Ago.  Don’t forget Sales Growth.  Ooh, and Sales Growth Percentage.  And, you guessed it, Sales Growth Percentage Year Ago.  You get the idea

Traditional Analysis Services has a feature named Calculated Members that helps address this problem.  But until that feature comes to PowerPivot, well, Coke is just gonna have to make all 2^11 flavors for me.

Bottleneck vs. Discipline

One member of the list in particular took an interest in what I was doing.  His name is Thomas, and he knows 100 times more about traditional Analysis Services than I do.

His chief concern was that I was using the wrong table structure in my model, like perhaps one big single table.  As it happens, I am not.  I am using something known as a Star Schema, which is something I have shown a lot of on the blog but never really described with any clarity.

I think Thomas has an excellent point.  It is easy to start out with the wrong table structures, especially if you are coming from an Excel background.  So I think it’s time someone who understands the Excel mindset AND the need for proper table structure, um, explain that.

Dick has volunteered to do a post (or series of posts) on that topic, so I will leave that to him.

My point back to Thomas was essentially this:  the need for smart measures today is almost certainly 10x what is provided, or more.  When my consulting budget ran out on the original Football Project, and my Analysis Services pro was no longer sitting in my office all day…  well, I was not getting any more new measures.

Doesn’t mean I didn’t want new measures, or need them.  I just wasn’t going to get them.  The person with the questions (me) lacked the tools to build them himself.  That is changing now.

Conclusion

I think that hundreds of measures in a model, or at least the potential to have hundreds, is a VERY good thing.  It is a sign that the bottleneck is going away.

Now, there are consequences of course.  A 1,300 measure field list isn’t a lot of fun to navigate (although the Search box in the PowerPivot addin is a savior!), and there will of course be the danger of “I just recreated measure X without realizing I already had a measure that did the same thing.”

But those are TOOLS issues.  Microsoft can build better field lists for us, with better ways to organize and view things, etc.  They will have to.

Don’t limit me arbitrarily.  Sooner or later, all 1,300 measures in this new model WILL be used.  Not all in the same report of course.  But they will. 

We could have built these measures on an as-needed basis rather than all at once, but guess what?  They would be less consistent, more prone to error, etc. – building them all at once was actually MORE disciplined

So, BI pros, my advice to you is to embrace the fact that the number of measures is about to grow, a LOT, in the average model.  And that is a Good Thing.

But as Thomas points out, correctly, in his latest blog post, the Excel folks can learn a TON from the BI community in terms of discipline and technique.  So let’s keep the conversation going

“Don’t you, forget about me.”

-Excel Pros Everywhere

From the beginning I’ve been talking about three different kinds of PowerPivot professionals:  Excel pros, SharePoint pros, and Database pros, and how they will all need to cooperate in order to get the most out of the system.

As far as I can tell, though, the majority of visitors to this site are from the Database camp (I lump Business Intelligence pros into this bucket).  Not surprising perhaps, since PowerPivot is a product of the SQL team at MS, and marketed heavily at conferences that db pros attend.

But for every db pro in an organization, there may be more than 100 Excel pros.  So, I think it’s clear that the PowerPivot message (and messaging) have a long way to go yet (it WAS just released, after all).

John Constant is one of the Excel pros who are “early to the party,” and has been sifting through all of the available PowerPivot materials for many months now, both the official MS stuff and the community offerings.

He raises some very valid points, things that I lose sight of at times.  It’s SO much simpler to grab a sample db from the SQL team and use it for blog examples for instance.  Even the Great Football Project starts with such a data source.

But I firmly believe that an organization’s success with PowerPivot rests in part on very competent training for the Excel pros…  and part of “competent” is “tailored to the Excel pro’s existing knowledge and viewpoints.”  In fact, I’d be out providing that kind of training this month if it weren’t for being very busy, the good kind of busy, applying PowerPivot for Pivotstream’s customers.

Without further preamble, I give you the words of John Constant:

PowerPivot vs the Excel Power user

Many moons ago, I can’t even remember where, I saw a comment…  Microsoft is coming out with a new business intelligence service… code named .. Gemini.  I was intrigued.  I had recently been tasked of coming up with some sort of data table /system  / process / spreadsheet / Excel voodoo to help put our business (and the competition) in perspective.  Hmm… a new tool you say.  So I delved into any links or articles I could find.  I saw the Donald Farmer video of Gemini sorting through millions of rows of video rentals – updating the charts/tables on the fly and .. sigh.. it was like love at first site.  I signed up for the beta at the first opportunity – archaic business software be damned! .  I was fortunate enough back in early fall 2009 to get my initial private beta Office 2010 release.  I was having a field day with the sparklines and the soon to be essential slicers, waiting with baited breath for the initial Gemini release.

The Microsoft talk was great.  It COULD be a standalone product.  It was meant for Excel power users and for IT and Data Administrators and Managers and so much more… but I’ve heard the talk before.  Don’t even get me started on the failings of Mappoint….   But low and behold – Gemini, with a few hiccups worked.  Sure if you linked in or brought in new data, it may corrupt all your work and you would have to start from scratch, but it’s beta.  Silly, silly beta.  It will grow up.  Sure there’s a new language that looks something like native excel formulas and there’s stuff that you take for granted working with Excel pivot data that you can’t do with Gemini… but it’s beta.. silly silly beta. It’s still growing.. be patient.  Help is on the way.  And help arrived.  There’s PowerPivotPro and PowerPivot.com  and Kasper and a host of other dedicated people who truly believe in the product.  And they do things to help build the community like having SQL workshops and handing out lovely diagrams (http://sqlcat.com/blogs/technicalnotes/image_4AFAE1C3.png) as a prize.

Errr… whoa..  hold on.  Did I mention the initial Microsoft talk?  Did I mention how Power users could use this product to help themselves and their company.  I understand the importance of SQL.  I understand the concept of Cubes; I’ve heard of OLAP (once or twice) but .. did you see that picture?!!  That’s a prize?!  Are you trying to scare away users?!   Let’s go back to Powerpivot.com, the ‘home’ of powerpivot (the grown up name of Gemini).   What’s that first video – the first public introduction of Powerpivot?  It shows a poweruser and PowerPivot for Excel.    Sure, it mentions Sharepoint but the debate continues – who is PowerPivot for? What about the power user?   – the grunts in the field who want to make the most of this product?  I know (reading some postings) there isn’t a large target population, but for some reason I feel like a Who in Whoville.. “We’re here!  We’re Here…. WE’RE HERE!!!!”  And what do I see …  honourable intentions of many esteemed PowerPivot bloggers aimed at….  SQL, OLAP, Business Intelligence users, like speaking to the already converted.

Don’t believe me?  Read your forums, your FAQ’s.  How are the samples set up?  Well you have FactInventory, FactSales, DimDate, DimThis, Dimthat – Dim Witted!   These examples and their structures aren’t what the normal excel user uses – or understands.  I’ve got Sales, I’ve got product, I’ve got territories, I’ve got stuff labeled poorly because they’ve been set up by someone who knew some programming about a decade ago… in other words, I have real word – DIM-LESS – data and structure.  It’s taken a few brick walls but I’ve hit my head enough times that the examples are sinking in but what about all those people that the ‘talk’ is supposed to reach?  Those who really have no experience with SQL or know of Cubes or data structure tables?  What about those people who can’t even consider getting Sharepoint because they are with a small business that doesn’t have the resources (financial or otherwise?).

I know why Microsoft is “selling” PowerPivot. I don’t fault them for that and I know the majority of users will have IT, Data Admin backgrounds, who will be setting up PivotViewers, and templates and services logs- all those lovely bells and whistles and the examples that will cater to those who deal with FactTables and DimData.  And then there were the Excel power users…  seeing a potentially great product aimed at the institutions, the data centres, the IT gods…   sigh…. 

I do not fault the Farmer’s the Collie’s, the Jonge’s, the Russo’s and the many many other dedicated PowerPivot supporters in the world – I thank them for all their hard work.  PowerPivot is a great product with great potential for many people, but just remember the little guy in the equation, the ones that someone deemed somewhere should have access to this power.  Bring it down a level from time to time… walk the walk and talk our talk and remember the Who’s… “we’re here!  We’re HERE… WE’RE HERE!!!”.

Powerpivot user – johncon aka Mongo41 on Twitter