skip to Main Content

Post by Rob Collie


One of Our Clients Sent This to us Before a Consult.  It is Perfection.

The Dysfunctional Myth of “Requirements Discovery”

A traditional BI project typically starts with “requirements discovery.”  This is where YOU, the business, get to spend multiple days, weeks, or even months teaching someone else (a BI consultant or internal BI pro) about your business.

There are MULTIPLE problems with this traditional methodology:

  1. YOU are the teacher (teaching about your biz), but YOU are paying.  Seems backwards yes?  Money usually flows in the opposite direction of knowledge transfer.  But not in BI.
  2. YOUR time is a MASSIVE hidden cost.  In addition to the fees you may be paying, don’t forget that YOUR time is being consumed in the process.  If we were going to put an “honest” cost on a BI project, the time consumed by Business personnel should be included.  (And that’s a lot more than your salaries – there’s opportunity cost to the business as well since you aren’t doing OTHER things).
  3. When the dust settles on “discovery,” the BI Pro STILL does not understand.  Oh, everyone PRETENDS that “discovery” is over, but really, it’s just begun.  In a few weeks, when the first “results” come in from the BI Pro, you will then, ahem, discover that there were tremendous misunderstandings.

This dysfunction is the root of BI failure.  Projects that never end.  Projects that end, but under-deliver.  And also…  projects that never even start.  If you’ve ever said “we can’t afford BI,” you were both simultaneously correct AND unknowingly reacting to this dynamic.

This dysfunction is NOT your fault, NOR is it the BI Pro’s fault.  You see, we’re terribly ineffective at communication, us humans – both on the “send” side AND the “receive” side.  No exceptions.

Requirements Discovery Works MUCH Better on Planet VulcanThe people involved are FINE.  It’s the methodology that’s broken.  I don’t have a single critical word for the PEOPLE involved in such projects.  We’d need to be Vulcans, equipped with the Mind Meld power, to make this approach work well.

So WHY did a bad methodology gain acceptance in the first place?  Because the software vendors did this TO the world.  Perpetrated it ON the world.  They built software that REQUIRED this sort of methodology.  All of the big players share historical blame here – Microsoft, IBM, Cognos, Business Objects, Microstrategy.  All of them.  Let’s shine a light on them.

Our Villain:  The Ivory Tower “Arrogance” of Past BI Software


Artist's Rendition of BI Software Engineer "Eight of Eleven"Traditional BI Software is to blame for traditional BI methodology, and people like ME are to blame for traditional BI software.  Yes, I am guilty.  I spent a lot of time on the “inside” of that industry, working on traditional BI software for Microsoft back in the early 2000’s, without ONCE even suspecting something was wrong.  So I hold “Past Rob” partly responsible for this, and “Today Rob” works to atone for these sins every day.  But I also hasten to say that really, I wasn’t smart enough to have truly helped create this problem.  I wanted to be that smart, but I wasn’t.

It takes a special kind of nerd to BUILD robust BI software.  Seriously, something like SQL Server Analysis Services (SSAS)?  It’s frankly stunning that something that beautiful could ever get built, and I’m talking about the “old” version of SSAS here – the one that first appeared in 2000.  Software like that, with its intricate logical interdependencies, can only be built by mathematical GENIUSES.  And you need more than one of them – you need Genius Critical Mass in order to build a credible BI tool like SSAS (or Cognos, or BO, or Microstrategy, or…)

Mathematical Geniuses go STRAIGHT into Software, without acquiring other experience first.  They don’t typically spend time in some other profession before becoming software developers.  They were drawn to computers in high school, studied them in college, and then were immediately hired by firms like MS.  (Heck, I was 100% like that too, I just was not as smart as I thought I was, and when I got to Microsoft, I discovered that.)

The Fatal Assumptions:  Where the “Arrogance Meets the Road.”

Math Geniuses Tend to Make Mathematical Assumptions About the World.  And this is where the problem starts.  Here are the relevant assumptions:

  1. They believed the Business World could be Accurately and Completely Captured in Pure, Logical Structures.  “Dimensions.”  “Measure Groups.”  “Cubes.”  These are the containers into which they believed the biz world could be poured – without overflow or leakage.
  2. They believed that the world is basically Just as Smart as them, and able to translate their businesses into these elegant structures.  This is where the word “arrogance” doesn’t fit.  I’ve found that the world’s smartest people are, by and large, some of its nicest.  They don’t remotely realize how exceptional they are.  So they design languages and systems that are so difficult, people like ME (no dummy) find them intimidating.  I was scared to death of MDX (the language of traditional SSAS), for instance.  No way I was ever learning it.
  3. They believed that the Business World is relatively static.  So, once you finished translating your entire business into those structures, you could move on and never revisit the translation.

All three of those assumptions were DEEPLY flawed.  And committed, universally, by everyone in the BI software business.

The Assumptions’ Impact:  Three Hurdles


  1. The software could only be configured by… Math Geniuses!  For perspective, I was on the Calculus Team in high school and yet I wanted nothing to do with MDX!  This hurdle is the result of assumption #2, of course, and is also the reason why you need specialized BI pros – you just didn’t have any Biz experts who were also BI experts.  Commence six-month mind meld.
  2. Inability to deal with exceptions, corner cases, and other rough edges.  The BI software TOLERATED a certain amount of real-world “noise,” but viewed noise as the exception rather than the norm.  But reality is often incredibly noisy.  This complicated the translation process tremendously, because now the BI pro had to educate the Biz pro on the limitations of the software, so that they could try figuring out a compromise.  Mind meld enters month seven.
  3. Changes to “finished” BI systems often felt like starting from scratch.  If you made it over Hurdles 1 and 2, panting and exhausted, it wasn’t long before your Biz environment and/or requirements changed.  And the software simply wasn’t designed to absorb that – the implications of such change would ripple across the whole system and invalidate many formerly-valid structures.  Start over?  Nah.  In practice, you’d just start ignoring the BI system and pressing the world’s favorite export button.

The Borg Got Humility, and We Got Power Pivot

imageEverything above is The Past, and we live in a VERY different Today.  Seriously, do you know what it takes for someone like ME to say something so gushingly positive about technology?  My ringside seat for all of this taught me to HATE software, and technology, rather than love it.

Lucky for me, I ALSO had a ringside seat for the birth of Power Pivot.  Which means I got to watch Amir Netz go through an amazing process:  of retracing his own steps.  (Not JUST him, but the entire team.  He was the architect however, and I worked with him more than anyone else).

Amir and I disagreed about a lot of things, but on today’s topic you will find nothing but admiration and respect.  Awe, even.  Without qualification.

Power Pivot (and SSAS Tabular) was a “Reboot,” a “Do-Over” on past sins.  Yes, it was also science-fiction level compression and speed, but the memory burned onto my brain was of the WISDOM – the hard-won wisdom on display during those years.

“Arrogant,” Pure Structures Like Dimensions?  Gone.  Replaced by HUMBLE structures.  Things like, you know – tables and formulas.  And, GASP, links between tables!  Things the world already undeniably had.  Things that were flexible and simple.  Things that absorbed real-world noise with grace.  Things that absorbed CHANGE with grace.

In the process, the language could be simplified and made more approachable.  No, not everyone can learn DAX (the language of Power Pivot and SSAS Tabular).  But MILLIONS of us can.  Millions. 

Most importantly, a small subset of Business people can learn it.  Bye bye mind meld.  No more MASSIVE communication cost.  Just go do it.

Wrapping Up:  The New Process

Pretty simple really.

  1. Sketch out what would change your biz.  As in “if we knew THIS information, it would change everything.”  You can sketch it out mentally, or go the extra step of drawing what it might actually look like, as pictured at the top of the post.
  2. Go build THAT, as fast as possible.  If you know Power Pivot, just go build it.  If you DON’T, you can sit down with someone who DOES know it.  Crack open Power Pivot, as a 2-person team, dig into your data, and collaboratively build it. 
  3. Yes, there is SOME communication involved here, but 100x less than before, because you aren’t trying to “capture” requirements in abstract form.  You are just BUILDING WHAT YOU NEED.  And there’s zero ambiguity here, while looking at the evolving results in real time.  A picture is worth 1,000 words and so is an evolving dashboard.  In two hours, you can often arguably be DONE with something that may have consumed weeks or months in the old way.  Honest.  I promise.
  4. Keep learning. If you had help in step 2, guess what?  That’s an awesome start to becoming a trained pro!  You are now likely already capable of making small improvements on your own.  And you can call the experts back in for occasional help as needed.  If you were “solo” for step 2, you’re just one step further along, but we are ALL always learning.  Me too – definitely.
  5. Now keep working backwards to improve the process “under the hood.”  Maybe you started out using text and excel files as data sources.  And maybe you invested manual “cleaning” steps on those before you could start.  Now is the time to start getting IT help.  Can we connect to a database instead?  Maybe we need to create a new db just to feed this model.  (Yes, maybe NOW is when you start building a Data Warehouse, but it’s going to be so much lighter and cheaper than traditional DW’s that I think the label doesn’t quite fit anymore).

Working fast in steps 1 and 2, in other words, does NOT dispel the need for responsibility.  In fact, your Power Pivot work benefits DRAMATICALLY MORE from responsible “plumbing” and IT support than your old Excel approach.  And it benefits IT to participate as well.

And of course you will soon want YouTube for Workbooks Smile

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 12 Comments
  1. Hello, nice post, I like your point of view on the matter. However, from my own little experience, I always needed to take the requirement from the business team. At least you should know the grain, and the “thing” that needs to be observed.

    What do think Rob ?

  2. I’ve had more success listening to what the “pain points” are for the business unit than in asking what they think they need. Because I have experience and training in many areas of business, I can probably come up with a workable proposal for a solution once I learn where people are experiencing bottlenecks and headache work, or where they are guessing about data they don’t have at hand.

  3. It’s similar to my research to find out what was agile development. It turns out I’ve been doing it my whole “programming” career. Because I started out working as an IT consumer and then learned Lotus 1-2-3 and dbase II at a group level to standardize and simplify our work, it feels natural to continually enhance the tool(s) via an iterative rather than water fall methodology. So we continue to build working prototypes until we get to a state of diminishing returns for additional effort needed to reach the unreachable “perfection”. Then it’s on to another project to learn new subject matter all the while sharpening my IT toolset.

  4. Hear Hear!!! The irony at the end of the day is that ALL() businesses are, essentially, the same. Thus, if you’ve gone through one “mind-melt” you’ve done them all–Company creates product, Company markets product, Company sells product, Company reaches a fork in the road: to B.I. or not to B.I.??? The rest is, as they say, is all semantics…

  5. I am completely sold on the massive potential of DAX (and Tabular) versus MDX. It has immensely helped me. But I want to confess that I have just begun!

    Well, one of the roadblocks is the ‘Organizational Structures’ and silos within an organization. I am sure many from this community will agree with me because they have been veteran witness to this. What makes me wonder is that the pace of technological change (DAX and tabular) is way faster than the general pace of change in the organizational structures (and silos!) even for relatively modern organizations with flat structures. People controlling the silos literally take stance and become possessive, protective and defensive. Sometimes I wonder if they are protecting the silo or the legacy systems (read: IBM-DB2/AS400 etc.)?!

    Great post
    Thank you Rob Collie!

  6. Great post!

    And I agree with comment by im2fast4u about corporation road blocks. From my experience, you can still move forward with PP… and wait for the others pushing the export buttons to catch up.

    I use PP for all of my projects/work now – it seems useful for larger BI projects and also for smaller ad/hoc requests. I can set up a workbook once, schedule it to refresh and never have to answer those same ad/hoc questions again.

    Another reason that I love PP, is that everything that you do is documented within the workbook! You can easily find the data sources used, filters, and calculations.

    I’ve seen a lot of Excel files… where you have no idea what the source of the data was, what time frame was used, was it a subset of some data, and etc. PP is nice little package of documented information. It’s all in there… kind of like Ragu pasta sauce 😉

  7. As a BI Pro, you had me worried in your opening, especially when I got to “the BI Pro STILL does not understand” part.

    Your article is calling for agility, without saying it by name. It’s fantastic and I agree 100%. Power Pivot, especially, lends itself to agility. Build something useful, incorporate it into your process. Add to it over time.

    In some ways though, you’re comparing apples and oranges. The BI pro in your article is building something for ***someone else***. You are comparing that to using Power Pivot to build something ***for yourself***. Building something for someone else is inherently harder and depends much more on communication (or mind-melding) than building something for yourself.

    If a Power Pivot pro impresses her boss, the boss might then ask her to build a similar solution for Sarah down the hall. At this point, the Power Pivot pro is going to run into all the same communication challenges that the BI pro does. Especially if Sarah down the hall isn’t interested in learning Power Pivot or testing half-built solutions.

    Power Pivot is freaking cool. It enables MILLIONS more people to solve their own problems than was ever possible with traditional BI. It is cheaper and more widely available. But Power Pivot isn’t inherently or automatically easier than traditional BI when it comes to solving ***other people’s*** problems.

    (I say this because I don’t want someone reading this to over-promise what Power Pivot can do compared to traditional BI and bite off more than they can chew)

  8. I completely agree with the potential of Power Pivot, DAX and Tabular (of course!).
    I have also experienced that the “corporate BI” still makes sense as a consolidation step. Yes, business are not immutable, but certain processes can be automated when the number of “consumers” is order of magnitudes larger than the producers and other requirements (refresh schedule, data volume, number of sources, security, …) come into play.
    I would really love to see a better integration between the two worlds. MDX still has a role in certain business problems (a niche, but 10% of everything is still a big slice, I wouldn’t call it a niche).
    My suggestion is to always *always* start any project with Power Pivot, and then with a working PoC making a decision about the future. That, 10% of the times, still go strecth to MDX and Multidimensional.

    The point about communication is really true. That’s where Power Pivot wins hands down, and today I have a new way to explain that. Thanks!

  9. The PowerPivot team at Microsoft have a lot to learn about agility, rapid development and listening to customer feedback from their counterparts of the Power Query team

  10. After re-reading this post a couple of days later, I’m simply blown away by the clarity of expression it contains.

    Thank you for, again sir, for breathing new life into the socialization of this project and reminding your tribe of what is possible.

    Come on Sarah, you have nothing to lose but your chains.

  11. Rob, I have to apologize to you.
    When I started reading this text, there was a parallel thought in my mind telling angrily: “This is stupid! I do not do these things so slowly and badly! Power BI is much simpler than that!”. It took me a while to realize I ignored the word ‘Traditional’ in front of the BI. During the reading of the second part of the text, the previously mentioned parallel thought was just nodding: “yes, yes, yes” all the time, forcing me to apologize for my initial thoughts about your writing. 🙂

    As somebody who was part of Microsoft AS team since end of the last century, I totally love the way Power BI excels (pun intended) in data discovery, manipulation and presentation. It is so good, that I actually did a few projects without ever talking to the customer. I was just pointed to their data source, or to their current bad report, and did my magic trick called “This is how I think you should do your job even, if I have no idea what do you do for living”. Power BI solution showing their own data, was usually so close to what they really needed, that only a few adjustments were missing to finalize it. If they have never seen the power of these reports, it could be even better not to talk to the customer too much, as they usually ask for what they had, not for what the I could offer them instead.

    Thanks for writing the article. It makes me feel good about my work again. 🙂

    Sasha Juric

    1. Hi Rob, I’m going to throw something in here a bit left field. I’m fully embracing these technologies but am finding that I have no need or desire to use pivot tables per se in Excel

      For a long while now I have yearned for some kind of supercharged DSum type formulae in Excel – Cube formulae referencing an underlying data model is precisely that

      So I’m going about building my data model and establishing calculated columns (where needed) and measures. So the ‘grunt’ of the business logic occurs behind the scenes and the user just sera cube formulae at the front end with something very transparent such as ‘Measures.ActualSalesNorth’. The work behind this calculation is in the data model itself and once set up the logic is locked and can’t be tampered with

      I suppose what I’m trying to say here is that I can do an awful lot just by defining my own measures and referencing them in cube formulae. It also gives me asymmetric reporting
      I have barely touched pivot tables themselves other than to create them and convert to formula using OLAP tools so I can see the necessary syntax

      Also I don’t need to be concerned about any filter context applied by a pivot table as I don’t use any. My measures always reference the datasets in the model itself. Virgin, unfiltered, unpivoted data

      Please let me know if there any pitfalls to the route I’m going!

Leave a Comment or Question