Power Pivot: a revolution in slow motion

By Avichal Singh

The first time you truly experience Power Pivot – not a demo, not some random public data set or someone else’s data – your own business data all lit up using Power Pivot. It is a transformative experience. And you know there is no turning back.

I remember that moment, I remember thinking this is HUGE, this gets BI in the hands of people who really need it. Power Pivot is surely is going to explode.

Sadly, it does not feel like that is happening :-(

As I have become more involved with the Modern Excel User Group (also on LinkedIn), I have realized that there are way too many people in buckets a) and b) below and not nearly enough in c).

a) Sad smile Unaware. Have not heard of Power Pivot or Power BI
b) Disappointed smile Aware but have not truly experienced the capabilities of Power Pivot
c) Red heart In Love with Power Pivot

Note: Are there stages between b) and c)? Between experiencing Power Pivot and falling in love? Perhaps. But guided by the right hands that path should be really short :-)

Unaware: Don’t know what they are missing

For the unaware group, I do my part; talking to anyone who would lend me an ear, about Power Pivot and Power BI at any event or gathering. But overall we would need to trust the higher powers :-) within Microsoft with that task. However I will make the offerings below to the powers that be:

- Power Pivot should be a free add-in for all versions of Excel 2013, just the way it is for Excel 2010. I feel it even makes good business sense for Microsoft. Power Pivot has the potential to attract a large user base and would solidly anchor Office and Office 365 in the rapidly changing world. These users can then be up sold services like Power BI.

- Power Pivot add-in should be easily discoverable. Both Power View and Power Map have buttons as part of the standard Excel ribbon, which can be used to easily activate these tools. Not so for Power Pivot.
It takes 8 mouse clicks to activate the add-in so you can launch Power Pivot.
File > Options > Add-Ins > Click Dropdown > COM Add-Ins > Go > Select ‘Microsoft Office Power Pivot for Excel 2013’ > Ok
As the first born in the Power BI suite, it is time for Power Pivot to claim it’s rightful place on the standard Excel ribbon and it would fit perfectly under the Data tab.

Currently in Excel 2013 Desired in Excel 2013
Currently in Excel 2013: Powe Map, Power View Desired in Excel 2013: Power Pivot or Data Model

Note: There was a shift in Excel 2013 to weave in Power Pivot so closely with the tool (and call it Data Model) that potentially many users would not even need to learn or use the Power Pivot window. IMHO, that is just keeping people away from all the goodness and richness of Power Pivot. Let them come, let them explore and experience.

Aware but not in Love: Mind the Gap!

While I may feel helpless in making a dent in the Unaware group, I feel impassioned and even obligated to help the Aware but inexperienced group.

I have spoken to many Excel users and noticed that there is a gap between knowing about Power Pivot and actually trying it out yourself to experience its true power. There is a step there that many users simply cannot take. Being on the other side, it baffles me a bit. I feel like yelling “Hey, come on over, the grass IS greener on the other side!”

From Excel to Power Pivot: Mind the gap!

I have some thoughts around, why is there a gap and what can be done to minimize the gap, but I’ll keep those to myself for now and focus on what can we do to get people to cross over from one side to the other. There may be many ways across, but I’ll pick the one that speaks strongly to me.

If someone were to just hold the new users hand and walk them through transforming their first data set using Power Pivot. Nothing fancy, a simple dimensional model with basic measures. This might take as few as two hours. It has to be their own data set though, one close to their heart, with answers to the riddles they are trying to solve. Not AdventureWorks or Tailspin Toys. Sample data sets can be good for learning but can still leave you short of crossing the gap.

That task my comrades, befalls on us. We are the enlightened, the empowered. The readers of this blog, most of you have crossed the gap, and now it falls on you to help the others. The Haves should help the Have Nots.

So look for the enthusiasts amongst your colleagues and friends – the data gene people – and encourage them to:-

- Read a book or online blogs

- Take a class (online or live in-person)

- Ask questions on forums

And when they are back at their desks and working through their first data set, help them. Just a little bit, just to get across. They will never look back.

Revolution starts with you

I am still convinced that Power Pivot and Power BI would transform the world of BI. However it would not transpire the way I had imagined. Instead of a big explosion it would be more of a trickle, till it starts to pour and then it becomes a torrent. It would spread from one person to the next…Revolution starts with you

…till it reaches the tipping point.

Revolution starts with you

Just not without your help. So help one person cross the gap from Excel to Excel+Power Pivot.

33 Responses to Power Pivot: a revolution in slow motion

  1. Joseph Looney says:

    Every time I talk to someone about PowerPivot I just get blank stares and even when they do understand what it does on the basic level, it doesn’t sit in until I can demonstrate with their own data just like you mentioned.

    I always relate it to having a rocket ship and if you give a rocket ship to the ancient Egyptians they wouldn’t know what to do with it although they are very interested in outer space and planets.

    I am still waiting on the tipping point!

    • Avichal Singh says:

      Joseph, glad to know that I am not the only one. Good analogy, at times I do feel like an alien (or a future human) who has landed amongst the Egyptians. It can be frustrating. But each person we pull over to Power Pivot is one more towards the tipping point :-)

  2. Mike Honey says:

    I have 20 years experience building analytic / cube solutions on technologies like Cognos PowerPlay, Analysis Services and Power Pivot.

    For a complete solution, all those analytic / cube tools always need a data transformation tool to shape the raw data into the format/structure/granularity needed. In the Excel world, the obvious tool that meets that need (and bridges your gap) is Power Query.

    As well as being a great tool for importing, combining and reshaping data (all with a much more approachable UI than Power Pivot), it can deliver data straight into the Power Pivot Data Model.

    http://www.powerquerypro.com seems to be available … :)

    • Avichal Singh says:

      I agree. My love for Power Query grows with each use (it’ll be hard to steal the Power Pivot’s spot though). Our struggle has been that we’re using SSAS Tabular models (instead of Excel_Power Pivot) and Power Query does not play with that; there is no way to send the output to an SSAS Model (unless you send it to say Excel and then import again in SSAS via other means). I hope that support is coming, since that would really change things for us regarding using Power Query.

  3. Chuck Haun says:

    It is even sadder when you visit your BI guys in the corporation and they try to fake a general understanding but obviously don’t have any real awareness of the tool or the power it brings. I met with our BI guys and they wanted to help but kept reverting to their own tools like Cognos that doesn’t do the analysis as easily or as powerfully as PowerPivot.

    I’m starting a guerrilla campaign here. My first step is to get access to the data (hard to do.) Then I will use the tool with one or two people here learning by helping to provide insights into some long standing questions/business problems. I plan to convert the advanced Excel users one at a time. :)

    • Avichal Singh says:

      Chuck, I know exactly how you feel. But realize that you are in a very powerful position. At times the seeming weakness and disadvantages can get you the victory (http://gladwell.com/david-and-goliath/). In your position, you have a lot more freedom than any BI team to go out there and get things done. Keep up the guerrilla war going!

    • gregkramer1 says:

      Chuck, Had a very similar experience. Tricky politics to be sure. Unfortunately, I was bit a bit of a bull in a china shop with my approach. Fail fast. Learn. Attack. :-)

  4. sam says:

    The same is true with Power Query – Initially like “DAX”, “M” looks daunting but once you get the hang of it – It grows on you and becomes your go to tool for ETL.

    It also has the advantage of having the most responsive team in MS who listen to what you have to say.

    Eventually I have a feeling that Power Query is the place where we will define the Relations (Joins) as more complex joins are possible via PQ than in PP and produce the flat Table (Sorry Rob – I know you said do not flatten :-) ) and Power-pivot will be the place where we do the CALCULATE(….)

  5. Kyle McGhee says:

    Earlier in the year I finally got Office 2010 at work and the first thing that I installed was PowerPivot. It was an exciting time, but then reality sunk in over the next month or two. I am the only one here with the skill set to utilize such a tool – every time I try something ‘fancy’*, PowerPivot or standard Excel formulas, I hear “that’s good/great, but you can use VLOOKUP and SUMIF too right?” or something along those lines. Even if I could showcase something powerful and time-saving, I would continuously have to maintain it or convert it to something more ‘digestible’ for whomever takes over the item in question.
    This and the files kept becoming corrupt and I would lose my data model, further diminishing the chance of cross the gap in the near future…one day….

    *Fancy: adjective: 1. Anything that is not an IF, VLOOKUP or SUMIF formula or combination thereof

    • Avichal Singh says:

      Ha! That’s funny and sad. Too bad you had file corruption issues in Excel 2010. I hadn’t faced too much of that, at least with 2010. Excel 2013 was problematic but has really stabilized now. I do work mostly in SSAS Tabular though and only occasionally in Excel.

      I haven’t quite encountered the reaction you describe. In my mind the things that Power Pivot is capable of blows regular Excel out of the water. But may be everyone does not percieve it that way…they just find it fancy :-)
      Check out the following resources, see if they help convince anyone
      http://www.powerpivotpro.com/what-is-powerpivot/
      http://bit.ly/powerpivotInfographic < This infographic was built specifically to convince the Excel crowd
      http://bit.ly/powerStory (YouTube Video)

    • Kerry says:

      This is me too. Finally got Office 2010, immediately started playing with PowerPivot, loved it, then they changed our security and I can no longer make a connection to our database through PP. So I get data out of Cognos and load it into PP ( not very efficient

      • Kerry says:

        The rest of my post was lost. Long story short, work has Cognos and I don’t know how to convince anyone to change and i can’t share what I do in PP because no one else has the addin. I pray PP can hang in long enough until my company upgrades to 2013.

        • Avichal Singh says:

          Kerry, I know it doesn’t help you one bit; but things have improved slightly with Excel 2013.
          Author: With 2013, you do need the Professional Plus edition to be able to “author” Power Pivot models
          Consume: But any Excel 2013 user can “use/consume” the model. Power Pivot (Data Model or whatever they call it now) is actually built into all versions/editions of Excel.
          Example: If I build a Power Pivot workbook and then create a sheet with Pivots, Cube formulas, Slicers, Graphs etc. Any Excel 2013 user can view and interact.

  6. Bryan says:

    I’m firmly I’m group B. I work for a company that manages everything with spreadsheets including our $200M capital budget. Huge spreadsheets that are linked, confusing and near impossible to extract useful information out of that could be used for any kind of analysis or forecasting. Then there are the ‘special reports’ that are asked for that MUST be done in a spreadsheet but are so time consuming to update on a routine basis. I’m sure powerpivot can help but I’m unsure where to start.

  7. sean says:

    Whilst initially delighted with power pivot, I soon got frustrated with its current limitations. Issues there is no programming setup. Meaning everytime you create a new spreadsheet, you have to retype all your measures – there isnt even a block copy. This is a big issue if you want to go much beyond a quick look at the data. Why should i make the investment in learning dax if there is no programming interface. Secondly the broken interaction with power query is very frustrating: eg I change the type of a column in power query, and the whole data model is lost so i have to reenter all the measures, and recreate all the pivottables, conditional formatting etc

    • GDRIII says:

      Save your Measures in an excel sheet and have them at the ready to copy paste anytime. Totally portable.

      • sean says:

        this doesn’t save anything from keeping them in an existing powerpivot and copy pasting them ONE AT A TIME into a new power pivot table/spreadsheet

    • Avichal Singh says:

      Sean,
      Let me see if I can help with any of your issues

      Now I was curious about this statement: “…Meaning everytime you create a new spreadsheet, you have to retype all your measures…”
      That puzzles me a bit…what is your scenario that drives this behavior – to create multiple spreadsheets (workbook) and have to copy formulas?

      • Multiple or Single Workbook
      • Our model has grown from one subject area to the next (we have 8 different subject areas in our model now, with 60+ tables). Instead of creating separate workbooks, we strongly felt that keeping everything combined in a single model is most valuable. “Perspectives” help a great deal.

      • Copy Paste Formulas?
      • As I move from one subject area to the next, there are maybe a few measures which are similar (my standard are CurrentYear, PreviousYear, YearOverYearChange, YearOverYearChangePercentage). But everything else is driven by the requirements of that specific dataset and is unique. Example “Sales” may have comparison with Budgets, whereas “Survey” will have Net Satisfaction scores (NSAT).

        So I never feel like I need to copy paste all formulas each time I venture into something new.

      • sean says:

        Thanks for your reply Avichal,
        Re Broken interaction with Power Query:
        the fix reported only works for 2010. see http://social.technet.microsoft.com/Forums/lync/en-US/9399c888-aaaf-4908-a665-6bd8007d20a1/modification-to-power-query-results-in-power-pivot-error-upon-refresh?forum=powerquery.

        Scenarios for creating new spreadsheet.
        a) broken power query interaction. ( I have to recreate all measures/power pivot pivot tables/charts/formatting etc)
        b) different granularity analysis. I am working in internet advertising and its useful to look at the data at different granularity levels/dimensions depending on the analysis. eg if I want to monitor bidding behaviour I look at 15 minute intervals and segment by campaign (and not site [of which there are ~100,000] and retrieve the data over a couple of days. If I want to analyse sites ( for each campaign) then I might aggregate at day level (because otherwise there would be too much data).
        Similarly, there are people in the team that want to only have an overview at account level for the week. They don’t want to load a 100MB spreadsheet just to look at 10 numbers.
        c) different accounts: there are some people in our company that manage certain accounts. They will want to create a spreadsheet that only loads data for a single account, rather than every single account we run. Because its only being used for a single/few accounts then the layout is different.

        What would you suggest?

        • Avichal Singh says:

          Sean,

          Power Query Bug

          Argh…for the Power Query bug. Perhaps a workaround for Excel 2013 can be:-

          1. Use Power Query to load data into File1

          2. Connect Power Pivot File2 to File1. Create all your measures in File2

          I realize this may or may not be an option based on your setup. But by providing a level of indirection, may insulate you from the Power Query bug.
          This way if you get the error (in File1), you can just follow it’s instructions (disable load to Data Model and reenable it), without losing all your work (which would be in File2).

          Core and Thin workbooks
          For your other question, look into Core and Thin Workbooks.

          Every time you need to pull a report, (different granularity, different accounts…) you do not need to pull data in and create a new data model. Instead, create a single “Core” model, and connect other lightweight/thin “reports” to it, they can be Excel based or any other reporting tool (Power View, SSRS, Tableau…).

          Consider the following options to host your Core workbook:-

          a) Excel on SharePoint

          If On premesis SharePoint is not an option, consider 3rd party (plexhosted.com, pivotstream.com)
          I find SharePoint way beyond my comfort zone. If you’re in the same boat, best left to a third party.

          b) SSAS Tabular

          See
          Migrating From Power Pivot to Analysis Services Tabular Model

          p.s.: Transition to core-and-thin would not happen overnight. But certainly is the direction to go.

  8. I teach Excel and have been mentioning Powerpivot in every class I do and I’ve been teaching and introduction to ti. .but I do think people need to see it with their own data to really realise that they no longer have to do vlookups or get to grips with index/match. To be honest though, there are still quite a few people for whom a vlookup is a revelation…never mindI think Power Query (which I have only started looking at) will be of huge benefit side by side with this…(because lots of people still get their data in an pivot unfriendly way and Power Query would save them so much time….). …so maybe there is room there for us Excel lovers to offer quick demos to people with their own data.. maybe?

  9. Dan MacLeod says:

    Our small company works with small-medium enterprises; we do their BI work for them. Cognos was our original software of choice, but not since last year when we started using Power Pivot and Power Query. That said, we know that the issue is not the reports, but understanding what can be done with the reports.

    Most end users don’t have the knowledge, attitude, skills, or tools ( we call it KAST) needed to understand the reports and what must be done to use the information to change their processes. Our contracts include a major training component where we spend time with the report consumers so that they will learn how to make a difference. The great thing about the Microsoft tools is that they are usually starting from the familiar Excel base and venturing forth with a friendly interface. We also understand, and communicate that, we will be changing the Power Pivot reports when needed to reflect what was actually needed, not what was originally requested. (Get them past the “WOW” factor to the real issues…)

    Thus, group b) may well be aware, but not have the full KAST to do anything with the reports!

    • Avichal Singh says:

      Dan, you open a whole new can of worms here :-) But I could not agree more.

      To give one example from our side, we often get these detailed requests for a “Daily Report” with like 500 metrics on it (I only exaggerate slightly).

      I never honor those requests, instead I go back to the business user and ask them about

      • What?
      • What is the core problem they are trying to solve OR question they are trying to answer

      • Action?
      • What will they do with this information? How will it be actionable? What actions would it drive? How would they measure results?

      We end up having a very different discussion and pursuing a very different path.

      I would voice my support for two of your points:-

      “The great thing about the Microsoft tools is that they are usually starting from the familiar Excel base and venturing forth with a friendly interface”
      This in my opinion is a crucial advantage.

      “…we will be changing the Power Pivot reports when needed to reflect what was actually needed, not what was originally requested…”
      OMG, this takes me back to Rob Collie’s excellent PASS BACON speech. If you haven’t already, check out these posts that brushed on that:-

      Simple, Amazing, and Happy: A Story About Data

      We Have a “Crush” on Verblike Reports

  10. Todd says:

    I’m a web developer. I work with oodles of tech. Always pushing whichever company I work at to upgrade all their Microsoft related tech. (not against open source, but I know Microsoft related stuff very, very well)

    Anyway, I have a personal Office 2013 Home license, but since I can’t use it to learn PowerPivot I gave up. I also figured that since I couldn’t share anything with any of our executives anyway, even those that happened to have bought their own Office 2013 Home licenses, there really wasn’t any point. I forlornly check the PowerPivot space (and this site actually) from time to time to see if anything had improved.

    Such a pity that my current company is still using Office 2003/2007, Exchange 2003, and SharePoint 2005. Because SharePoint 2005 looks so ghetto, many talk of moving to other platforms. I’m certain I could have made a really good case for upgrading to the latest for Office and SharePoint. Hmph.

    • Avichal Singh says:

      Todd,

      That is the reality :-( Many companies are indeed still using older versions. You should be able to make things work if you can get to Excel 2010 (Power Pivot is a free add-in) or Excel 2013 (see note below).

      * Office 2013 Home license: Yes, you would not be able to create Power Pivot models with this version. Consider buying Excel Standalone, see Hey, Who Moved My (PowerPivot 2013) Cheese?
      Or you can also try a free trial of Power BI+O365

      * I couldn’t share anything with any of our executives anyway: This may work actually. All Power Pivot functionality is built into Excel 2013 (even the Home license ones); thus they should be able to consume/use any of the work that you do. You cannot “author” a Power Pivot model with a Home license, but if someone else sends you a file with a Power Pivot model, you can still open it and interact with it (create/modify pivots, charts etc.)

      • Todd says:

        I wasn’t aware they could still consume power pivots. Very nice. Thanks for the info. I’ll have to decide if I really want to buy a standalone or whatnot.

  11. KHAN SUL-O says:

    Where was the option above for “Aware, but hate it!”? Unless your data sets are unrealistically small… this product does not seem to function at all.

    • powerpivotpro says:

      You are running 32-bit. The 64-bit version handles amazing data capacities. Just last week we were crunching 500 million rows on a 4 GB laptop.

      That said, it is a shame, indeed, that MS doesn’t publicly communicate that 64-bit is a borderline MUST, and it’s also a shame that they haven’t made 64-bit easier to deploy (such as, side by side with the 32-bit version of Office).

      You are not alone in this frustration, in other words.

  12. KHAN SUL-O says:

    I am running 64. One thing that is important to know is that inspecting the Table Properties from within the PowerPivot DataModel Window will launch a full blown reload of the data that you have no control over. It would be great if it would just pull 10 rows of each field in that window for a preview, and then allow you to remove columns from the pull. For the 37 million records I pulled in my test, just to use the Table Properties meant losing an hour and a half. I waited it out, then it failed.

    • Avichal Singh says:

      Khan, I tried this out both in Excel 2010 and Excel 2013. I am not seeing the behavior you describe. I connected to a table that has close to 2.5M rows. During the import or later when launching Table Properties from Power Pivot, In the preview window, I only see just 50 records being pulled (for Excel 2010 & Excel 2013).
      Table properties only pulls 50 rows

Leave a Comment or Question