What is Power Pivot’s #1 Competitor?

As a BI developer, I thought I’d try getting going with Power Pivot. Microsoft seems to have a sales prevention team. Upgrade my Office 365 Home subscription to Office Pro? Can’t be done. I’d have to uninstall, somehow figure out how to get my money back, then install (from scratch) Office 365 Pro. I’ve had a Tableau licence for years, they’ve been nothing but supportive, they’ve added “R” support, and my favourite: Box and Whiskers plots. Get started with Power Pivot? Ain’t nobody got time for that!

I discovered PowerPivot within weeks of getting a Tableau license, courtesy of my then employer. I followed both paths for a bit, and have been following this blog since. My thought was that I might at some time in the future, get into an organization that doesn’t, can’t, won’t have Tableau, and I want a solid place to fall back to and be able to produce the same kind of results.

I changed jobs about six months ago into an organization without Tableau. I requested Excel 2010 so I could use PowerPivot, thinking this may be just the situation I had in mind. In the end, I just recommended Tableau, for a variety of reasons. The learning curve for Tableau was easier than PowerPivot (this coming from an Excel Power User), and there are a bunch of analysts who would have to learn whichever one we went with. Tableau is more stable (I know, you say it can handle millions of rows, but it’s REALLY temperamental. I kept running into all kinds of memory pinches and errors with useless messages when trying to connect to large datasets. Furthermore, there are weird constraints like only being able to handle one to many relationships in it’s models. It’s not that these things can’t be resolved or worked around in PowerPivot, it’s that they are not even problems in the first place in Tableau). Speed of development, analysis, and iterating can’t be beat in Tableau. Additionally, at my new organization, and I imagine many others, there is not a standard for what version of Excel people use. Trying to create a Power Pivot for wide user community with multiple versions of Excel is a nightmare unleashed.

Sorry Excel, I love you, and I use you for a lot of different things every day, and I couldn’t live without you, but Tableau does what Tableau does better than anyone else. Ultimately, I couldn’t even justify the cost of “free with Excel” because of the time saved in so many arenas with Tableau (development, support, etc) exceeds makes it less expensive for the org. The cost of purchase was covered in the first two weeks of time savings, no joke.

Someone at another monolithic entity is probably blogging as we speak:
I know the Ford culture from the inside. To me, it’s not a faceless monolithic entity in the way that, say, Microsoft is.

One thing I’ve always wondered is what the uptake of things like PowerPivot by within Microsoft itself is like, and how it compares to similar sized monoliths…err…organizations. Rob, what was your observation about how sophisticated is the use of Excel and it’s newfangled addins among the Human Resources, Facilities Management, Finance and Planning, and inhouse IT support teams?

BIll, have you ever had the chance to ‘appraise’ MS’s use of it’s own tool in your many visits to the Wizard’s castle?

So I’ve been using Power Pivot for about 6 months now and I think it’s an incredibly awesome tool. In the mid to long run I don’t think it faces any serious competition from Tableau or anyone else for that matter.

I also agree that the biggest thing working against PPVT is the lack of knowledge that it even exists. People I know in the BI team have often heard about it, but as its target user is a business expert who is fairly good with computers as opposed to virtually every other BI solution out there which are aimed at computer experts who are fairly good at business thinking, it’s hard to gain traction in the traditional BI world despite it being an incredibly powerful, low cost, highly flexible tool.

That said, in the short run Tableau does IMHO have some significant advantages which point not to fundamental flaws in PPVT or Excel in general, but rather to some potential low hanging fruit for future releases. I know developer hours are scarce (every potential feature added comes at the expense of not adding another nearly as awesome feature) and I do hope this doesn’t seem like me complaining about what is essentially my absolute favorite piece of software; but despite generally being a better solution, Excel/PPVT could look to Tableau to point out a few areas where growth would be extremely helpful.

(BTW I don’t own Tableau so a lot of the qualities I’m assuming Tableau has are totally based on what I’ve read/hear, if I’m wrong about them, or wrong about something in Excel for that matter, please feel free to correct me.)

The advantages, I would argue, seem to lie primarily in chart availability and default chart quality. Tableau markets itself (smartly) as a BI reporting solution, but from what I’ve seen its modeling system is nothing great, the arena in which it really shines isn’t building models but instead visualizing results. While Excel/PPVT is OK at this (the modeling being its focus), Tableau looks to be designed *specifically* with this in mind and hence is optimized for it.

The good news is that the big three charts that I think are the most useful (Line, Scatter, and Bar/Column) are readily available in Excel (though in 2010 there’s no “Scatter” Pivot Chart, not sure if this shows up in 2013 but you should be able to build one with a linked table); the other piece of good news is that while several great charts are noticeably absent (histogram, box plot, multi-strip plot, bullet chart, etc) you can MacGyver almost all of them out of other existing charts. While I personally really like doing this, having to build them out of other charts feels and looks really klunky, especially to people you’re trying to convince to not buy Tableau. Also if you’re a new user and don’t know how to duct-tape together these other excellent charts they functionally don’t exist.

Also, the default template charts look OK, but they include a lot of chart-junk that usually is best removed (legends with single entries) and black ink that is almost always better either gone(chart border) or at 20% grey (grid-lines). Making these (and similar) improvements on a given chart is fairly easy, and you can save chart templates to avoid having to repeat the same steps every time, but I don’t think you can make Excel use a template in place of the default chart type templates, and if you don’t have a background in the graphic-design aspects of visualization you’re probably just going to use the defaults, which again, look OK but not great. My understanding (again, correct me if I’m wrong) is the Tableau defaults tend to look very very good and has a user interface that my graphic design friends would be happy to be at the wheel of; so even though in the right hands Excel can produce fantastic looking reports, Tableau makes the process much easier, requires less expertise, and is generally harder to produce mediocre looking charts it.

(I should give some Excel some serious credit however for it’s default color scheme, which I think looks really good in addition to I believe being colorblind accessible. Also, the addition of sparklines makes for some awesome possibilities.)

Data visualization certain shouldn’t be a beauty contest, but good default design and a reasonably expansive set of top-shelf charts are *extremely* beneficial qualities for any BI system to have in that they significantly improve the speed with which the end user can parse the data in a finished report and also leverages the end user’s incredibly powerful visual pattern recognition system in attempting to find business insight in the numbers.

PPVT/Excel does offer quite a good toolset for visualizing data, and I certainly wouldn’t describe it as deficient or lacking. You can make some absolutely fantastic looking dynamic reports with it, and given its superior modeling system I absolutely think it’s the best single solution out there for a BM. However given the importance of visualization in a BI system I think there are some things that at this moment based on what I’ve seen Tableau does have the edge on. Would I recommend a dual system where PPVT is used for modeling and Tableau is used for visualization? Like any good business decision I would say it depends, but generally speaking, no. With some extra work you can achieve similar visual quality in Excel without the added headache of having two systems. Further, given that none of these potential improvements ask Excel to do something it already doesn’t fundamentally do, if Tableau or any similar competitor continues nipping away at market share this seems like a fairly easy update to prioritize in the next Excel version (even if it ends up being some sort of serious integration with R and some of it’s plotting libraries).

Tableau’s ease of use means little to a BM professional (they’ll trade that for power or speed any day of the week), but a reduced number of mouse-clicks/keystrokes to high quality output is something a software professional in any field would take note of, be they BM, Photo Editor, or Statistician. With that in mind I do think Tableau will indeed make some additional marginal headway in the near future, but I expect it to be short lived as not just good, but top notch data visualization tools find their way into future versions of Excel/PPVT to match its world class modeling system.

Anyway, that’s just my two cents. If people feel differently (particularly if they’ve, ya know, used Tableau) I’d love to hear their thoughts on how the two systems handle visualization.

As a fairly new user of PowerPivot (took Rob’s class in the fall of 2014) and a long-time Excel “Power User”, I share Rob’s view that PowerPivot will up-end the whole analysis world once it starts to spread. That is, of course, if it does spread. While I agree that Power Pivot’s biggest competitors are “nothing” (Meta) and “this is the way we’ve always done it”, everyone (including Microsoft) should realize that the target audience for PowerPivot is a relatively small proportion of the overall Excel user community. This fact is the prime driver behind the overwhelming ignorance of the existence of PowerPivot, as well as the other Power BI tools.

Part of the problem is the relatively small target audience for a tool like Power Pivot. As an IT instructor in a large company, I teach the full range of Excel classes and, at one point or another, come into contact with virtually all of the Excel users in the company that truly need to master Excel in order to do their jobs. For most of them, 80%-95% depending on the business area, all they need, or ever will need, is ordinary spreadsheet functionality. Of the remaining 5%-15%, probably only half need or use any kind pivot table functionality.

The bad news for Microsoft is this is a pretty small group of potential evangelists. The good news is that these are the people that hold the keys to the information kingdom in their companies so management sees their work and interacts with them on a daily basis (well at least here they do). With a few exceptions, however, these potential evangelists are not equipped with the skills needed to make the sale. This is where Microsoft needs to focus its efforts – either by working with or through these people to spread the word.

Having recently debuted a course on PowerPivot (using Rob’ book as a text) and while working on a Power Query class (to be released next week), I realized I had made a mistake in putting Power Pivot first. Most of the data that analysts use is not neatly stored in a data warehouse already arranged in a star schema with dimension (lookup) and Fact (data) tables that PowerPivot can use. As any analyst will tell you, most of the data they go after is really dirty and ill-formed for their purposes. Most of their pain is in getting the data they need and then getting it in a usable form. Even more pain if the process has to be repeatable. Only then does a tool like PowerPivot come in to play.

This is where Power query shines. Power Query does in seconds what it used to take me hours or even days to do writing VBA code and VLookups to produce a repeatable process to produce useable data. It combines data from multiple sources and different types of sources almost effortlessly.

So if you are going be a Power BI evangelist, start with Power Query. Its potential user base is much larger than Power Pivot (not everyone that needs to clean and form data needs it for a pivot table) but may also serve as an important foundation stone in developing serious BI capabilities in your company.

Microsoft could help tremendously with this by simply providing better documentation for Power Query. The tools in the graphical interface are fairly intuitive and self-explanatory, and will probably serve the needs of about 90 % of the potential user community. But if you have do dive into the M programming language, their current references Microsoft Power Query for Excel Formula Language Specification (PDF) and Power Query Formula Library Specification are nearly incomprehensible. And I’ve yet to see anything that explains how to structure M code. It may be out there, I just haven’t found it, which is just as big of a problem.