Should I learn PowerPivot yet?

Rob’s got a good post called What is Power Pivot’s #1 Competitor? which has some great comments, as well as a hilarious YouTube clip.

One of his musings is that companies like Tableau – who are indeed competitors to Power Pivot – don’t really view Power Pivot as an obstacle to Tableau getting new customers. They know their biggest competitor is still just Excel itself.

That’s also PowerPivot’s biggest competitor. To the point that the opportunity cost to MS of users foregoing PowerPivot for Tableau is an order of magnitude smaller that the opportunity costs to MS of Excel users that simply never realize PowerPivot might be something they could use to sack lots of overpriced, underwhelming reporting analysts.

As per my comment I left on that post, I haven’t embraced PowerPivot yet because I’m still too busy panicking about not knowing everything I think I need to know about Excel and VBA. There’s still plenty of posts on this site that I don’t even partially comprehend, and only when I’ve got these under my belt will I STOP panicking about “this” and then START panicking about “that” (PowerPivot). Guess I’ve got a one-track mind.

Last time I looked, PowerPivot and those other new-fangled inventions weren’t programmable by VBA to a great extent. So in my mind – and based partly on what I do know and largely on what I don’t – I get the feeling that I can’t very easily integrate these new-fangled inventions into my existing factory. While I’m sure that I can probably replace quite a bit of that VBA-driven factory with these new-fangled inventions, I’m loathe to step away from the production line while I do this, because of a fear that orders are going to be piling up somewhere.

What’s more, because I switch companies every 18 months to 3 years, while I could possibly put these new-fangled inventions to work where I work now, there’s no guarantee that my new skills will be required in a new role, given PowerPoint ain’t exactly as ubiquitous as Excel is. In fact I think an increase in my old skills will probably be a better bet.

That all said and not done, why to I still get the feeling that I’m missing the opportunity of a lifetime by not investing now. Why do I have a vague sinking feeling that I’m sinking almost imperceptibly into a career tar-pit, rather than evolving into a higher life form?

Ah well, if I’m going to be a living dinosaur, then I’m in good company and in the right place:
DDOE_Should I learn PowerPivot

13 Comments

  1. PeterB says:

    Like you, I have not really looked at PowerPivot with any intent as I am still overwhelmed by how much I don’t know about Excel and VBA. That said, I use SQL to link data to ‘normal’ pivot tables via ADO disconnected recordsets. This is a powerful combination and allows me to quickly analyse vast amounts of data.

    Am I missing something? What would PowerPivot add?

  2. Bob Phillips says:

    You are missing so much.

    PowerPivot compresses the data, so you can process so much, far more than 1M rows.

    PowerPivot allows you to easily mash-up extra data, including Excel data in the same workbook, to extend the enterprise data.

    PowerPivot allows you to pull in data from multiple sources.

    PowerPivot has DAX, the F-22 compared to pivot tables Sopwith Camel calculate function.

    And then there is Power Query, Power View, Power Map, and the fact that MS are actively improving(?) it, putting research $ into it.

  3. JimC says:

    Since I can rarely get at source data in a normal form the Unpivot functionality provided by PQ is irreplaceable. Yeah, there are other ways to unpivot data but non as easy.

  4. Rob Collie says:

    Hi Jeff. Your comment on that post is still making me smile.

    Since this appears to be a VBA crowd, let me say that I think the “lack” of VBA support in Power Pivot is *partly* (but not completely) overblown.

    I use VBA all the time. Every day. And it’s always being used in conjunction with Power Pivot.

    Thing is, Power Pivot surfaces all of its “results” in the good old Excel grid, or floating over the top in Excel charts. So the entire Excel OM is still of course available, and highly relevant.

    Imagine the Power Pivot model, behind the scenes, as a “library,” or DLL, that you built in another language. And that model/library brings tremendous power to the table, that your VBA automation can leverage – not directly via OM, but via the Excel OM, which by proxy accesses your model. Power Pivot magnifies the impact and power of your existing way of doing things, even though it lacks an OM of its own. This is a mind shift for sure but the results can be astonishing.

    For me, Power Pivot is the *reason* to write lots of VBA. Not an obstacle. I bet that’s surprising to hear but it’s 100% true. My personal xlsb has like 20 modules in it and they’re all “aimed” at Power Pivot scenarios.

    And then in 2013, Power Pivot actually has an OM. Not a very good one, I agree. But once you digest what I say above, the strength of the Power Pivot OM is not all that salient of a concern.

    Anyway, I share this with you in the hopes that it gets you thinking in new ways that benefit you and make you money. I am not cheerleading, just sharing what I have come around to in my own biz. Lots of room here for VBA pros like you (I am really just a hack) to do amazing things and monetize them.

  5. Ken Puls says:

    “Last time I looked, PowerPivot and those other new-fangled inventions weren’t programmable by VBA to a great extent”

    Time to look again. :) In 2013 PowerPivot’s data model is baked into Excel, and there is an object model behind it. Is it as fully extended as we’d like? No. But is it big enough to use in the trenches? I think so. The auto refresh on the desktop is probably the biggest implication, and that can certainly be done.

    Having said that, I rarely find myself using VBA with PowerPivot. I’m way more interested in the functionality that Bob mentions, particularly now that I can reach outside my own systems, pull in data from the web and make it part of my BI solution. Very cool and very powerful stuff.

    PowerPivot and Power Query are the most exciting thing to happen to Excel since VBA. While they may not be ubiquitous in the enterprise today, I’m predicting that they will be in 5 years. You’ll always have a ton to learn about Excel, but this is just one more piece that you need to add to your list.

  6. Lukas says:

    I’ve been thinking about exactly that topic for the past two or three months: Continue building deeper Excel & VBA skills (I know I have yet to scratch the surface on some topics) or go all out for PowerPivot? So far I’ve been reading along on Rob’s blog and have purchased his first book. Kindle tells me that I’m only 3% into the oeuvre, so at the moment, I’m not even at the stage where I know what I don’t know (conscious incompetence?) when it comes to PowerPivot.

    Part of my hesitation to go head long for PowerPivot stems from the same issue that Jeff sites above: “there’s no guarantee that my new skills will be required in a new role”. I currently don’t see many opportunities to get customers here as no one knows it, no one is asking about it, and many customers simply want automation. Yet I too feel like I’m missing the boat!

    If you haven’t already, I recommend reading Chris Webb’s BI Blog post titled “Ninth Blog Birthday“. He talks about some of these same topics.

    Anyone else want to add their thoughts?

    Regards from Switzerland,
    Lukas

  7. Bob Phillips says:

    That is the odd thing Ken. I am a developer by inclination as well as by upbringing. I mainstream on VBA, most of my work is in VBA. Excel and VBA is usually my first thought when looking at a problem. But when I use PowerPivot and PowerQuery I switch off from VBA, I rarely even consider it, I tend to look at the problem differently

    I must admit though that I do not see it being ubiquitous in 5 years. It will either be seen as a core component of a business teams product set but mainly used by a few specialist in the teams, or it will disappear altogether, wiped out by the opposition. Which will depend upon MS, and I fear for it as MS seem hell-bent on making it very hard to acquire. The 2013 licensing fiasco has done real damage, and even though it has improved, it is not good. I love the idea of developing Tableau skills, but I consider that product far too expensive; by my calculations, top-end Office 265 with PowerBI is also very expensive. When I try to explain the licensing structure to potential PP users, they think I must be a confused dolt, nobody wanting to sell product could make it that complex.

  8. Jeff Weir says:

    JimC: Since I can rarely get at source data in a normal form the Unpivot functionality provided by PQ is irreplaceable. Yeah, there are other ways to unpivot data but non as easy.

    I feel your pain. Or at least, I felt it, and it motivated me to do this:

    http://dailydoseofexcel.com/archives/2013/11/21/unpivot-shootout/

    We have Excel 2010 at work, and so installing the FREE addin would be a no brainer given it adds new functionality like this. But has anyone in the history of IT ever successfuly convinced IT to install something FREE? No way….if it doesn’t cost a s#!tload of money to purchase and implement, it simply can’t be any good.

  9. Doug Glancy says:

    Jeff, as regards your main question, I’ve never been motivated to use/learn PowerPivot, because at my job-up-to-last week we had very free access to the source data, so creating database views, pulling them into Excel tables, and then pivoting those always seemed like the way to go. If I had to assemble multiple data sources within Excel I’d definitely look at it. Also, Bob Phillips’ comment above makes me realize there are other benefits I should explore.

    As to your ongoing IT rant, you should come to the US and get a job with a school district :). At least in my single experience, they don’t have enough people to try to manage what users can and can’t load, so instead let anybody install anything, and then re-image when things go wrong. I was able to install different browsers, VirtualBox, FoxIt Reader, CCleaner, various addins, Daemon Tools Lite, and much more.

  10. Jeff Weir says:

    Me back at school? NEVER!

  11. Doug Glancy says:

    What was I thinking?

  12. Ryan says:

    Maybe this is a typo “PowerPoint ain’t exactly as ubiquitous as Excel”, as I’m pretty sure it is :)

  13. Mike Rizza says:

    I haven’t invested much time into PowerPivot because it crashes every time I use it. I think the install isn’t clean, but if I can’t find a way to get a clean corporate install package, it will probably be a dead-end for me.

    A co-worker recently moved his Excel tables into Access, and is now accessing his tables and queries from Excel 2010 Pivot Tables. Very slick. After seeing his set up, I’m not sure why I’d want to mess with trying to get a clean install of PowerPivot. Other than setting up the joins from Excel rather than Access, am I missing anything here? It seemed to me that his setup was better because he could share it with all Excel users rather than having to get a functional copy of Power Pivot installed for everyone wanting to use the data.

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: