Category 1: One-Time Sharing
Imagine that you are rummaging through your data and find an interesting pattern. So, you compile a simple report and want to share the results at the meeting that afternoon. The question is "How do you transfer the report?"
The easiest way is to take a screenshot. This can be done with almost any tool in any situation. However, it adds no interactivity, which is a major selling point for newer reporting tools. Power Pivot and Tableau both require that you give the workbook to someone else. But, these workbooks are usually too large to email. Therefore, you would need to post them on SharePoint, Dropbox, Skydrive or some other online storage medium to transfer the files.
Both of these tools also require that the reader have the appropriate software installed. If you are giving an Excel workbook to someone who does not have your version of Excel and/or Power Pivot, you will likely run into serious issues. The same can be said of Tableau. If the reader does not have Tableau installed on their machine, they will be unable to see your report. However, Tableau has one distinct advantage in that they can download Tableau Reader for free. Tableau Reader allows the reader to interact with your Tableau workbook without being able to alter anything. To our knowledge, there is no free software that allows someone to interact with Power Pivot/Power View reports. Therefore, Tableau inches this one out.
Category 2: Data Publishing
Imagine that you are an analyst who spent countless hours compiling all sorts of data in your Power Pivot or Tableau workbook. One of your colleagues loves your work and says "Can I see your data? I'd like to do some analysis of my own." How do you handle this?
Regardless of which tool you are using, one option is to give him a copy of your workbook. However, this is a one-time transfer and will likely cause issues with data consistency.
For Power Pivot, the better option is to publish your workbook to SharePoint. When the workbook is published to SharePoint, anyone with the appropriate credentials can connect directly to the data using an Analysis Services or ODBC connection. The only concern here is that SharePoint is not free.
Tableau has a very similar mechanic when you publish your data connection to Tableau Server. You can publish any type of connection to the Tableau Server. However, your calculated fields will be lost if you are not using a Tableau Data Extract. If you are using an extract, you can store all of your row-level calculations in the extract itself before you publish it. This is referred to as "Optimizing." But, you will still lose all of your other calculated fields. Also, if the workbook has more than one data connection that is being blended together, these connections will have to be published separately, unless you can find a way to join them into one data source.
EDIT: A reader pointed out that this paragraph was misleading. If you create a calculated field in Tableau, then publish the data source, all of your calculated fields will be stored as metadata and must be recalculated by Tableau every time that you use them. If you want Tableau to calculate the row-level values once and store the results in the extract, then you need to use the process known as "optimizing."
As an added bonus, Power Pivot allows many other tools to connect to its data. All you need is an Analysis Services/ODBC connector. Tableau, on the other hand, would require the user to view the data, then copy it into Excel, which can be quite a hassle if the data set is large. In fact, it is impossible to copy more than a million rows into Excel. Our point is this: Power Pivot allows users to model data then export it to another tool; whereas Tableau wants to be the end of the line. It seems we have a clear winner here as well.
Winner: Power Pivot
Category 3: Report Publishing
Now, you've created an awesome report that is now the "Gold Standard" for reporting in your business. Every other analyst needs to look at your report every day to see how the current metrics/KPIs are faring. How do you accomplish this?
These tools are almost identical in how they accomplish this task. You can publish a Power View report to SharePoint, where you can set an automatic refresh cycle. Tableau does the same using Tableau Server. Seeing as how this is the designed end-game for these tools, they are nearly identical in how they accomplish them. Also, we won't get into the caching mechanisms behind these tools. However, we will say that they both create caches that allow users the freedom to quickly view reports once they have been initialized.
EDIT: Ray M pointed out that Power BI's mobile publishing is not up to par with Tableau's. From our experience, that's one of the selling points of Tableau is it's ability to work on most mobile devices. We haven't heard much about successes for Power BI on that front.
We saw that Tableau barely edged out Power Pivot in One-Time Sharing because of its free reader. On the other hand, Power Pivot demolished Tableau with its ability to share data models. We hope you found this informative. Thanks for reading.
Associate Data Analytics Consultant