skip to Main Content

How Power Pivot, Power View, and the Other Power BI Tools Relate to Each Other

Power Pivot is the Engine that Turns Data Into Information!
But We Can’t Understand This Properly Without Examining the Three Big Lies of Data

Goal:  Answer Four Frequently-Asked Questions

So many things to say this week. Let’s jump in.  Here are the questions I ultimately aim to answer, which are questions I get basically everywhere I go:

  1. How do all of the Power BI Components relate to each other?  Power Pivot, Power Query, Power View, Power Map, Q and A, etc. = Power Confusion for some folks.  I get it.
  2. Has Power Pivot become less important, now that we have all of these other new “Power *” tools?
  3. Which tool should I learn first in the Power BI family?
  4. Should I consider abandoning this stuff altogether in favor of <hot new technology X>?  Tableau, Hadoop, R, etc.

In order to answer these, first we must confront some insidious lies that we are told every day.

Examining:  The Three Big Lies of Data

We want data tools vendors to lie to usThe world of data, today, is clouded by Three Big Lies.  These lies originate with all of the tools vendors – Oracle, IBM, Tableau, etc., and yes, Microsoft too is very much playing along.

Even though the Vendors are the Purveyors of these lies, they are NOT “at fault” for them.  Because the world actually WANTS to be told these lies.  BADLY wants to be told them, in fact.  And because the audience is so receptive to these lies, the vendors naturally learn to tell them, and tell them well.

Vendors who DON’T learn to tell these lies?  Well, those vendors don’t win many customers.  And then those vendors disappear.

So while the lies COME from the vendors, the PROBLEM, really, is with US – the people who BUY the tools.


No, I am not suggesting that we stop buying data tools!  Computers, after all, are fantastic at data.  Data is basically the ONLY thing that can go into a computer and then come out better.  (Computers have a much harder time “improving” IDEAS, for example).

Instead, I am advocating that we become smarter buyers of data tools.  And then the vendors themselves can start telling a more accurate and honest story, because we will be ready for it.

No more suspense, here are the Three Big Lies:

The three big lies of data: 1) Data and Information are the Same Thing. 2) You Have the Data, Now You Just Need a Tool to “Look at It.” 3) Data is Easy.

The Three Big Lies of Data:  We Hear Them ALL the Time.

Interested in Learning How to Do this Kind of Thing?

power query

Our New Online Power Query Course just launched this year. You’ll learn Power Query from the best in the business, two Excel MVPs Ken Puls and Miguel Escobar. We’ve included 7 Hours of Self-Paced Video over 31 Courses, with lots of example files, and a Digital Badge powered by Acclaim.

CLICK HERE to claim your spot.

So What’s the Truth?

For starters, let’s try a few things that I believe are Truths:

  1. Data must inherently be transformed into information.  In its raw form, data is really just noise.  You can be swimming in it and still be blind.
  2. Trying to skip that transformation step, and merely “looking at” the data, will RARELY yield useful results.
  3. The transformation from data to information is NEVER easy, and we should distrust people who promise otherwise.

Your Mission:  Ruminate/Comment While I Refine Part Two

I don’t want to rush this, it’s too important.  So, I’ll resume Thursday.  In the meantime, noodle on this a bit and let me know what you think OK?

The last parting thought, for now, is that I think the people reading this ALREADY know that the three lies are indeed lies, and you ALSO knew those three truths before you read them here.

So really, my mission here is to “crystallize” things you already know, and to give us all concrete ways to talk about them, so that overall, we can move the conversation about data (and tools) to a smarter place.

Part Two:  The World Doesn’t Have its Numbers >>

Rob Collie

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 21 Comments
  1. Love it. And very true regarding the “just buy our tool” lie.

    One thought: Though data and information may not be the same thing, you must admit that swimming in data can in fact be very fun.

    1. It IS fun, indeed! But only for 1/16 of us – those who carry the Data Gene. And that actually is a BIG part of this story.

      1. Yes, when I realised I had data gills I felt more alone than that only guy that saw Kevin Costner beeing the guy with gills.

  2. Bang on the money Rob. Now, what would really be helpful is that if you add the title “The Three Big Lies of Big to your chart so that I can use it to try to get my boss to understand this.

  3. I completely agree…I just started at a new company in the finance department where the modeling is very “old excel”. My first thought is to rebuild using PowerPivot as my DB instead of implementing a forecasting tool that the CFO wants. The main issue they have is insight into the data and the timing of putting the data together (which I believe PowerPivot can take care of). I feel like I have to educate the CFO on what PowerPivot can do, which is essentially the same thing as the forecasting tool.

  4. Rob, your points are excellent. You said a lot with your slides with so few words. That is an art form I aspire to learn.

    I think “transforming” data into “info” is hard work, but tools matter big time. If you must dig a hole for your pool in your back yard, do you choose a shovel or a backhoe? I’ll take the backhoe, thank you very much. And so Power Pivot seems to be the backhoe on steroids, a heavy excavator of sorts, when compared to everything coming before it, and even anything comparing with it today, does it not?

    Most companies swim in “data,” but man do they have a hard time getting to the “real” info they really want and need, especially when getting clear insight necessary for good decision making. That takes real work to produce. If you can get an executive or line manager to confide and discuss this, his or her answer would probably be similar; I contend that most of them don’t have access to the info and insights they “really” want or need in order to manage, much less in a timely fashion. Yes, they have reports and lots of ’em, but do those reports really give them what they want?

    In steps the potential of Power Pivot.

    So continuing with your points, Rob, even if data is transformed into good info with great tools like Power Pivot, people still need to create insight from that info so it can then be acted upon.

    In looking at your first slide above, Power Query and Power Pivot seem to help in the transformation of data into info. Then further downstream in this process, it seems that Power Pivot, Power Map and Power View help change that info into actionable and insightful info. Power Pivot is in the middle of this process and acts in both an upstream and downstream manner when it comes to the transformation of data. Power Pivot is truly pivotal. (Sorry, I could not resist.) 🙂

    Again, I really like that slide. It shows data, before it is transformed, in the realm of chaos and darkness. PowerPivot is a light saber — directed by the Force, if you will. It’s a force for good in dealing with chaos and darkness… Okay, okay I’m getting carried away here, but hopefully my point is made, especially to those with the data gene. 🙂

    So there’s a book titled “Drinking from the Fire Hose: Making Smarter Decisions without Drowning in Information” by Christopher J Frank, Paul Magnone. Here’s a quote I liked from it:

    “Insights allow us to see clearly into complex situations, and that’s the whole point. Data leads to numbers, numbers to information, and information to insights. Insights, finally, inspire action. Data is just a means to an end. But in our data-driven world, almost all of us seem to have lost sight of that.” (Kindle location 216)

    So, as the fire hose of data ever increases at tremendous rates, Power Pivot’s value shines even more in helping to create useful info and insights out of that data, especially as I learn it and use it more and more. I suspect this is true of many others, too.

    Sorry for my long response to your post. I rarely respond to blogs and such, but this subject is very interesting to me. Plus, I am really excited about Power Pivot and I appreciate the PowerPivotPro blog site. I am learning a lot because of it. Thanks Rob.

    1. I guess I should add to my post additional comments in order to speak to the second slide: The Three Big Lies of Data. I think those lies may never go away. With such an influx of data, how to deal with it will remain confusing for most people. What will cause ignorance to disappear? I guess getting educated on this is only way for the lies to go away. Dealing with data will come down to tools. But selecting only the right tools will enable the three big lies of data to be dealt with effectively.

  5. I think another Big Lie would be that you need a consultant along with your tool in order to get results. What I like about Excel and PowerPivot is the massive amount of resources available online so that I have rarely been unable to find an answer or learn how to do what I want to do. With other tools, I hunt and hunt and can’t find any help written in plain English instead of IT speak or pages of code. Sites like this one are the best of the bunch where I can casually learn about an awesome tool without very much Computer Science knowledge, because in the end, all I want is to get and analyze data.

    1. There is a ton of information on the web about building houses, building furniture and creating electricity. I still prefer just having someone doing it for me. Cause Im good at other stuff… and its a lot more efficient for me to just do what I do, and have other people do what they do better than me.

      There are a ton of good reasons why you should use consultants. I actually think this animosity towards consultants is a really bad thing.

      1. I half agree with you. I think there are a lot of perfectly good reasons to bring in consultants. I would think a lot of us that follow this site probably have experienced a consultant being brought in and really nothing was gained from it. In the self-service BI world I think there are aspects where consultants make sense (depending on your company) but just the term “self-service” makes me feel like it’s your data and you should know what to do with it.

      2. Yep, that sounded pretty sour on consultants, not what I meant to say. I think I was thinking more about the comment below, preferring PowerPivot as a self-service tool that I can learn about and troubleshoot and allows me to be be more independent and get quicker answers without too much setup.

  6. From you chart, Power Pivot is the glue that ties them all together. That reminds me of Amir Netz’s talk at the PASS, he talked about ‘data culture’ and how users can just easily perform magic with their data using Power View. What happened to hours of data scrubbing, integration, modelling , business logic design and development, optimizing for visualization and how this span across the various tools they have; Excel, Power Query, Power Pivot, Power View, Power Map etc.

    The uneducated user is wowed and left to believe that it’s that simple and straight forward but I wouldn’t blame Microsoft or Mr Netz (whom I respect so much), they’re only trying to catch up with the good old gimmick that have been employed by the other companies in the BI jungle. This, of course, can only fool the uneducated data enthusiasts and that is where you, Rob, come in by educating as many people as you’re currently doing. Thanks Rob.

    1. I feel like you have invaded my brain and stolen my most private thoughts 🙂

      MS has learned not to “talk too smart” – they have seen too many customers adopt a solution that was pitched in a “dumb” manner (one that skips all the messy details).

      Power View is not magic, it is an endpoint. But it’s hard to sell the “messy” stuff like formulas and relationships in today’s environment.

      Let’s do what we can to change that.

  7. Rob, Great post. I’m glad to see you addressing these questions. I discovered your blog in March after learning about the new thing, Power BI, while at Microsoft Convergence. I have grown to like your blog and your book quite a lot in spite of the fact that you tenaciously hang on to a pre-Power BI perspective. While reading your work to get a grip on DAX, I had to look elsewhere to begin understanding where PowerPivot stands within the larger Power BI concept. Anyway, I can’t help but believe that over time a larger percentage of your new readers will be less interested in PowerPivot within the context of Excel and more interested in Power Pivot within the context of Power BI. I would love to see you effectively address this interest but feel that it will require you to focus a bit more on Excel 2013 – and show a Power View visualization every once in a while. On the other hand, as I am far more concerned with learning to work with corporate data than Big Data, I have appreciated that you aren’t focused on showing off the whiz-bang awesomeness of Power Query – there is plenty of that out there already. Another way of putting all this is: You choosing to focus on Power Pivot is GREAT, but will you continue to discuss PowerPivot as the greatest thing to happen to Excel or will you begin framing it as the core component of Power BI? Or can you do both?

    1. Great question and great “food for thought” on my end.

      “Tenacious” – yes I believe that is a good word for me 🙂

      A few thoughts.

      1) The number of Excel people in the world is MASSIVE. For every person who “can spell BI,” as Amir Netz said a long time ago, there are 1,000 people who sling pivots and VLOOKUPs. MS has had a hard time reaching that latter audience, but still, numbers-wise, that’s the place to be IMO.

      2) I have a strong personal affinity for those Excel people. Whenever I meet one, there is instant connection, instant kinship. They are simply amazing people. I was once approached by an MS competitor in the BI space, to see if I was interested in “changing sides,” and my answer to them was that I could never “quit” the Excel people. My emotional attachment is pretty clear – these are the people I want to work with every day, the people I want to help.

      3) Even with Power BI, the numbers are the important thing that matter – AFTER THE SALE. Yes, Power View is a big part of WINNING the sale, many times, which is why it gets so much “air time” on stage and in demos. But when the dust settles and the analysts sit down with the toolset, yep, it’s Power Pivot that they need to know, and Power Pivot that delivers the results. I’ve seen some very big customers get VERY excited, at the C-level, over dashboards that were nothing more than cube formulas in the Excel grid. In others words, it’s the numbers, not the viz, at the end of the day.

      4) All of that said, yes, I need to think about the Power BI audience and maybe be a bit more welcoming/accommodating for those of you coming from that angle. Will noodle on that, thanks for triggering me to do so 🙂

  8. PowerView and PowerPivot should have been integrated. ETL + BI Analytics
    If you look at the List of Data sources from which you can pull data in the 2 tools – 80% are the same. though PowerQuery has a longer list and its growing. (Facebook, from Web, from SAP Business objects, From Exchange Server….), But it will be a killer app if they combine Power Q with Power P
    M + DAX = MDAX

    Power View – is Stupid – It is unstable, results in lots of crashes – Has absolutely now advantages over PowerPivot + Slicers + Conventional Charts

    The Map feature in PowerView is 2D – So PowerMap is definitely better for Geographical data
    But the Wise people at Microsoft made PowerMap non functional for Office 2013 Professional Plus and Excel 2013 Standalone users – One more Decision showing lack of vision in the current Office Team

  9. Yes! Yes! Yes! I once had a request for predictive forecasting. The manager was adamant that it was easy, that plenty of companies do such a thing, and that if I couldn’t do it, then he’d hire a 3rd party to do it. What he didn’t get (for the longest time) is that the task was difficult not because of the predictive part, but because the underlying data was junk. Predicting on junk data = junk predictions. Hiring a 3rd party wasn’t an easy way out.

    I’ve been looking for a “how it all fits together” chart for so long too. It’s perfect. Thank-you!

  10. This may just be a different way of saying what’s already been said throughout the post and comments, but I think there is a corollary thread to pursue here around the lack of investment on our part (“customers”) in determining which tool is most suitable for the specific situation at hand. And, most critically, viewing the “situation” as a gestalt: the state of the data + the potential state of that data + the actual relevant lines of inquiry + the toolset that will “deliver” the sunshine + the humanoids that will be deployed against it all in their various stations. Obviously vendors will have you believe that their tool is suitable for every possible eventuality, and without any effort. Investors need their exit after all (did I say that out loud?)

    But even if we just focus on the people/humanoids piece, people thrive on different things. If the magic number that will ultimately transform the business is 2.736%, odds are that one CAN get from here to there with any of the tools in question. It’s just math. AND, way more often than not, it’s breathtakingly simple math. I don’t think we look hard enough at “what do we have, what do we need, and how comfortable will Susie and Johnny be on the toolset that will bring this to life?”

    Several people can all be surfing the same wave. The wave’s attributes are universal to them all. But the attributes of each person’s surfboard are determined by each person’s unique strengths and weaknesses. (clearly better analogies needs to be part of our homework assignment as well).

  11. Right, thus the word ‘business’ in BI. BTW, wassup with a possible Power Pivot gang in the DC area?

  12. Rob,

    I wonder if you might make the graphic flow bottom->up instead of left->right and somehow overlay something of an iceberg effect like this: I would think that the water line would be directly to the right of your “Produces” arrow. That might help communicate the danger lurking beneath the surface of an oversimplified BI project.

    Also, regarding your truth points, what do you think about adding something about data source and data relationships. In my experience, just finding where to get the right data and knowing how to combine it properly with other data (mashup) requires some skill. This is more of a “Power Query / Relationships in Power Pivot” thing and not a “DAX” thing, but I still think it’s very important because you can’t do data transformation with DAX unless you first have a solid data foundation. It’s especially important when you’re dealing with internal data because oftentimes it’s scattered or isn’t very clean and needs some ETL (Power Query) cleansing applied to it.

Leave a Reply

Your email address will not be published. Required fields are marked *