Monday, September 16, 2013

Tableau vs. Power Pivot Part 1: Basic Functionality

Today, we will begin a new series comparing two of the top self-service BI tools on the market, Tableau and Power Pivot.  Most people would say that Tableau is a "visualization" tool and Power Pivot is a "modeling" tool.  However, is this really true?  That's what we're here to find out.  We're going to put many of the features of Tableau up against Power Pivot, and vice-versa, to see what they can really do.  This type of examination was done about three years ago.  Click here if you would like to read it.  Many of the flaws listed for Tableau have been worked out in the newer releases, while Power Pivot has not gotten the same treatment.  This is a major issue when comparing purchased products to free products.  However, our examination is from the user's perspective.  This means that current and future functionality must be considered, as well as pricing.  As usual, we will use the Superstore Sales sample data set from Tableau.

Data Connections:

Power Pivot can connect to the following sources:
  • Microsoft SQL Server
  • Microsoft SQL Azure
  • Microsoft SQL Server Parallel Data Warehouse
  • Microsoft Access
  • Oracle
  • Teradata
  • Sybase
  • Informix
  • IBM DB2
  • Microsoft Analysis Services
  • Microsoft Reporting Services
  • Windows Azure Marketplace
  • Microsoft Excel
  • Text File
  • OData
  • Data Feeds
Tableau can connect to the following sources:
  • Microsoft Access
  • Microsoft Excel
  • Text File
  • Actian Vectorwise
  • Aster Database
  • Cloudera Hadoop
  • DataStax Enterprise
  • EMC Greenplum
  • Firebird
  • Google Analytics
  • Google BigQuery
  • Hortonworks Hadoop Hive
  • HP Vertica
  • IBM DB2
  • IBM Netezza
  • MapR Hadoop Hive
  • Microsoft Analysis Services
  • Microsoft PowerPivot
  • Microsoft SQL Server
  • MySQL
  • OData
  • Oracle
  • Oracle Essbase
  • ParAccel
  • PostgreSQL
  • Progress OpenEdge
  • Salesforce
  • SAP Netweaver Business Warehouse
  • SAP Sybase IQ
  • Teradata
  • Windows Azure Marketplace
  • ODBC
It doesn't take long to see that Tableau can connect to everything that Power Pivot can, plus a lot more.  Tableau can even connect to Power Pivot, while the reverse would require copy-pasting into Excel first.  Power Pivot seems to focus on Microsoft products almost exclusively.  They added connectivity to some of their major competitors, such as Oracle and Teradata, but not much else.  This seems to be the advantage of a purchased stand-alone product over a free add-in.  In fairness, you could connect to some, maybe even most, of these data sources with another type of connector and a little ingenuity.  However, this is a test of "BASIC" functionality.  Anything requiring advanced knowledge is off-limits for now.

Winner: Tableau

Quick Answers from Clean Data

This section will examine how quickly a user can get basic information about clean data using these programs.  We will assume that the data can be easily pulled into these programs.  To keep it simple, let's get Profit by Region.
Profit by Region (Power Pivot)
With Power Pivots simple drag-and-drop interface, this took a couple of seconds.
Profit by Region (Tableau)
Ditto for Tableau.  I will give Power Pivot one advantage in that it automatically gives you totals and subtotals while Tableau does not.  However, Power Pivot says "Row Labels" instead of "Region" which detracts from the readability.  All in all, this was a very simple task in both tools.

Winner: Tie

EDIT: The following was pointed out by Shawn Wallwork: Even though Power Pivot labels the "Row Labels", Tableau does not label "Sum of Profit" at all.  With this new information, we must posthumously change the winner to Power Pivot.

Basic Typecasting

In this section, we will look at how each program handles changing data types.  We will do two things here.  First, we will change a numeric to a currency.  Then, we will change a string version of a number to a number.
Decimal to Currency (Power Pivot)
This was just a couple of clicks away in Power Pivot.
Decimal to Currency (Tableau)
This was slightly more complex in Tableau because we had to go through two menus to get here.  To be clear, Power Pivot has a Currency data type, while Tableau does not.  Tableau simply considers the data to be numeric and Currency is a display type.  If we were to change the display type to Currency in Power Pivot, we would have to go through the Excel Format Font options, which would be just as complex as Tableau.  We don't see why having a Currency data type is important (All formulas will see it as a number!).  Therefore, we will consider this part a draw.  For future reference, unnecessary features will not grant you extra credit from this council.

Now, let's look at how easy it is to actually change a data type in these tools.  Let's see how we would change a text field to a decimal.
Text to Decimal (Power Pivot)
Once again, the handy data type drop-down saves us again.  Power Pivot completed this task in a couple of clicks.
Text to Decimal (Tableau)
Ditto again for Tableau.  It has the same menu feature as Power Pivot.  However, when you cast a measure to a dimension, or vice-versa, in Tableau, you have to manually drag the field to the appropriate shelf, which is one extra step that Power Pivot does not have.  Therefore, Power Pivot is better at this (by a hair).

Winner: Power Pivot

We hope you enjoyed Part 1 of our series on Tableau vs. Power Pivot.  As a simple recap, Tableau destroyed Power Pivot with its ability to easily connect to different data sources.  However, Power Pivot edged out Tableau in its ability to get quick answers from clean data and typecast fields.  The series will get more intense from here on out.  Thanks for reading.  We hope you found this informative.

Brad Llewellyn
Associate Data Analytics Consultant
Mariner, LLC


  1. Hi Brad,

    I'm not familiar with Power Pivot (I'm still on Office 2007) and I'm curious about your logic about why Power Pivot is better than Tableau for not having the Dimension/Measure distinction? That distinction lets Tableau do a lot in terms of Show Me and automatically generating results when double-clicking or dropping pills into views, so it seems to me that the minor cost in typecasting would be outweighed by the later convenience in building views.

    1. Jonathan,

      Thanks alot for your comment. The analyses here are very narrow. Therefore, we chose to look at typecasting and ONLY typecasting.

      Show Me! is a great feature of Tableau and it will make an appearance in the next part, which will deal with basic charting in these tools.

  2. Hi Brad,

    This is great, because I know what's coming next ;)!!! Show's the Excel folks that the basics are fairly the same and they can do it. All part of your evil master plan, I'll bet.

  3. Brad, interesting blog. One minor quibble: While Power Pivot changes Region to Row Labels, Tableau doesn't label the Sum of Profit at all. So for readability I'd have to give this one to PP.


    1. Nice catch, Shawn. That totally slipped by me. I think I'll have to edit that one.

  4. Brad - this is an excellent series. We get questions from students all the time on the differences between PowerPivot and Tableau. I'm going to post a link to your series on our blog to direct them to~

  5. I agree that "Row Labels" is not a great heading. That's why for every pivot table that I create, I go to Design, Report Layout, Show in Tabular Form. Now, you have real headings again.

    1. That's really cool! Being from the IT world, I get so wrapped up in the DAX that I forget about all of the neat formatting features. Thanks for sharing.

  6. I'm not 100% convinced that these two products should be compared in a "versus" mode
    I don't see Power Pivot as a complete tool with visualisations but part of a toolset(and technically not free as you have to purchase Office Pro)
    Equally, I don't see Tableau as a mature data modeling tool, in fact it's not that at all
    The two products possibly enhance one another, no?

    1. That's the point of the series, to see if those kinds of statements are actually true, or are they just puffery? I know I was surprised at some of the things these tools could actually do once I tried it.

  7. I would be keen to see this debate between QlikView and Power BI, I think that would be a far better comparative analysis that using Tableau!
    Tableau is kinda low hanging fruit and is not really an Enterprise BI tool!

  8. Hem, the Tableau connector to PowerPivot is borderline scam, it can only use a linked table, which makes it totally useless...

    1. The Power Pivot connection within Tableau allows Tableau to query the Power Pivot model the same way as Power View or a Pivot table would. I'm not sure what situation you have seen, but we have a couple of clients who use this connection and I have personally used it.

    2. I'm currently evaluating linking Tableau to PowerPivot for Financial Reporting. Can you highlight some of the issues I might run into, especially considering I plan to publish the workbook to a Tableau server for others in the organization to view the visualizations?


  9. Hi Brad,
    We are currently using Tableau and Salesforce, and having challenge to do one of our requirement.
    We are currently facing the challenge in Tableau Report. The data is fetched from Salesforce.

    Please find the below problem statement . Please let me know if you need more information regarding the same.

    Problem statement:
    We have an requirement to display the data as month basis - where data has to be distributed equally from start date to end date.

    For example start date is 1/Jan/2015 and End Date is 31/Jan/2016 then over the period for every month data has to be distributed across the months between 1/Jan/2015 and 31/Jan/2016.
    In tableau we need to display the 12 months representation from the current month in x-axis. So we have taken Sheet named Months as the primary source, which will have static months list from current month to next twelve months. This would not be the correct way. But to achieve 12 months in x-axis we have taken this excel sheet.
    Kindly provide some idea/solution how can we achieve the same in tableau.

    Data :
    ID Start Date End Date data value
    1 01-Jan-15 31-Jan-16 1000
    2 01-Aug-15 31-Dec-15 700

    so here in the graph for jan-15 to jul-15 value 1000/12 has to show and from aug-15 to dec-15 value (1000/12)+(700/5) has to show and for jan-16 value 1000/12 should show.

    Kindly provide some idea/solution how can we achieve the same in tableau.

  10. This is an excellent series. We get questions from students all the time on the differences between Power Pivot and Tableau.