Microsoft PowerPivot vs. Tableau

Ready to rumble

I wrote a recent article providing an overview of the Tableau Self-Service BI (SSBI) suite and a brief comparison with that of Microsoft PowerPivot. However, since that time even more people in the community are asking me about how the two products stack up against one another. In this extended blog post I will take you on a pound-for-pound comparison of the two SSBI products while explicitly excluding the price factor.

Note: To keep the comparison fair I am not comparing any of the Microsoft BI platform's features beyond those found in PowerPivot

In this corner

Tableau 5.1

Tableau is the product of an internal R&D project at Stanford University in 1997. In 2003, the company was launched with Tableau Desktop. Since 2003 the product has incurred 5 major versions including spawning its Server and Reader brethren. Tableau Software markets their suite of business intelligence applications as ‘rapid-fire solutions’. The suite is now comprised of Desktop, Reader, and Server components. When used together, they cover enterprises SSBI needs. Tableau Software is currently shipping version 5.1 and the entire suite ships in x-86 builds only although they run just fine on x64 operating systems.

PowerPivot 2010

PowerPivot is the upcoming SSBI suite from Microsoft. PowerPivot continues the Microsoft product design lineage by combining multiple products and technologies to address new customer requirements. PowerPivot is like-wise composed of Desktop and Server components. In PowerPivot, the Desktop component (aka client experience) is embedded into Excel as a free add-in. The Server component of PowerPivot is a combination of SQL Server Analysis Services (SSAS) and SharePoint. When used together, they cover enterprises SSBI needs. Microsoft is currently shipping version 1 of its PowerPivot suite via Excel 2010 w/PowerPivot add-in, SQL Server 2008 R2, and SharePoint 2010 delivery vehicles, all of which ship in both x86 and x64 editions excluding SharePoint server (x64 only).

Note: x86 architecture is slowly dying, especially in the enterprise server market.

Round 1: Features

Tableau 5.1

Tableau Desktop, which is the self-service authoring component of the Tableau suite, has a very Excel-like feel. There are concepts of workbooks and worksheets. Tableau Desktop provides an easy to use drag-and-drop interface which allows anyone to create spectacular pivot tables & data visualizations.;

Data is provided in a read-only state in Tableau Desktop. There is no limit on the amount of data you can work with in Tableau Desktop. Users can create custom aggregations, calculated fields, table calculations, binned data, subtotals, grand totals, and percentages. You use a general formula language to create calculated fields and custom measures. Users can visually filter data by using Quick filters.

Dashboard capabilities are also available in Tableau Desktop by combining multiple worksheets into a single display. Users can publish their authored insights to a centralized Tableau Server for internal sharing and collaboration. For those who would like to share their insights with partners, vendors, and the rest of the world (no security) you can opt to publish worksheets and books to the Tableau Public Server. And a bit of a hidden feature of Tableau Desktop is that it allows you to extract datasets into local files for offline data access and offloading server workload.

Tableau Server’s primary function is to facilitate thin-client (browser) viewing for published workbooks & dashboards. The server component clearly achieves this goal and goes beyond it by providing advanced thin-client viewing capabilities such as filtering, paging, sorting, and out-of-the-box data security (users only see the data they are authorized to see). In addition, users can add notes, questions, and comments to published content. To embed published content in other enterprise applications you use a URL that maps to the worksheet or dashboard of interest.

So what about Tableau Reader? No, I didn’t forget about Tableau Reader (but I almost did). Tableau Reader is essentially the thick-client (PC only) viewing equivalent of Tableau Server’s thin-client viewing experience. Tableau Desktop authors must first pre-package their works into a packaged workbook file that Tableau Reader users can then consume.

PowerPivot 2010

The PowerPivot client experience is embedded directly within Microsoft Excel and thus its authoring experience is not only similar to Excel but IS Excel. Technically, there is a separate window called the PowerPivot Workspace which is used for building a PowerPivot application’s underlying data model but even it has a very ‘Excelish’ feel to it (the PowerPivot Workspace uses data constructs such as tables, rows, and columns not cells and ranges). PowerPivot authors create insights and visualizations using Excel pivot tables & charts.

Data is provided in a read-only state in PowerPivot. There will be (RTM) a limit of 4GB when authoring PowerPivot applications to facilitate SharePoint storage (limited to 2GB max file upload size).  Users can create custom measures, calculated fields, subtotals, grand totals, and percentages. You use a new language called Data Analysis eXpressions (DAX) to create custom measures and calculated fields. Users can visually filter data by using Slicers.

PowerPivot’s performance definitely deserves to be referenced as a feature. Microsoft is using an in-memory, column oriented data store engine called Vertipaq that facilitates rapid data importation, custom measures, and analytics. The format of the in-memory database is that of Analysis Services.

In PowerPivot there is no separation between views and dashboards. The only difference is the number of Pivot tables & charts the author decides to create. After information producers create their insights they have the option of publishing to a centralized SharePoint 2010 server. PowerPivot has no need of an extract mechanism because its data resides in a highly optimized in-memory database.

The PowerPivot server’s primary function is to facilitate thin-client (browser) viewing for published PowerPivot applications. SharePoint 2010 clearly achieves this goal and goes beyond it by providing advanced thin-client viewing capabilities such as filtering, paging, sorting, rich Silverlight-based previews of published insights, and a set of management features. An interesting capability of published PowerPivot applications is the ability to create additional (downstream) PowerPivot applications using the original as a data source. To embed published PowerPivot applications in other enterprise applications you can leverage the Excel Web Service (EWS) and other Excel Services integration techniques.

Finally, there is no thick-client based reader for PowerPivot applications. This question has come up a lot in various technical communities since PowerPivot’s introduction. The idea would be to have an Excel 2010 PowerPivot Viewer but alas it does not exist.

Round 2: Strengths

Tableau 5.1

Tableau’s strength lies in the product’s visualization and ease of use authoring capabilities. Tableau Desktop, in my personal opinion, is the single best desktop BI authoring experience in existence today. There is a very fluid-like aspect to creating analytical views and visualizations in Tableau Desktop. And there are a lot of different data visualization options in Tableau Desktop. Tableau Desktop’s extract data and quick filters are nice features as well. The formula language is easy enough to use and build custom measures and calculated fields with. The availability of Tableau Reader is also a nice option for those folks who travel a lot without a Tableau Desktop license and need offline analytical consumption.

Tableau Server is actually a bit more impressive than I had originally thought as well. Tableau Server provides some value-ads such as supporting comments, questions, and threads around published views. Both Tableau Server and Desktop installations are extremely easy to perform. Finally, the ability to share non-sensitive worksheets and books with the general public is a neat capability.

PowerPivot 2010

PowerPivot for Excel’s strength lies in the product’s ability to rapidly aggregate and process millions of rows of data. Unlike Tableau Desktop, the product does ship in x64 client builds, leverages a column-oriented in-memory data store, and thus you can work with massive volumes of data in a very performant fashion. Additionally, DAX is essentially ‘MDX for information producers’, providing a very extensive expression language to build custom measures and fields with. PowerPivot for Excel’s support for Slicers and Sparklines are nice features as well. Finally, PowerPivot for Excel’s ability to merge the two worlds of individual views and dashboards into a single interface is pretty cool in its own simplicity. And don’t forget that PowerPivot for Excel supports practically any data source available current day including ATOM feeds and ODBC/OLEDB.

However, just focusing on SharePoint 2010’s PowerPivot features there are many strengths. First and foremost are the PowerPivot for SharePoint manageability features. Administrators can control when data refreshes occur and under what security credentials. Administrators gain transparency into the usage of PowerPivot applications that are published to SharePoint so that they can see a given application that is becoming mission critical to the enterprise. The PowerPivot Gallery, a Silverlight-based preview of a given PowerPivot application has to be mentioned as well, the previews are just jaw-dropping.

Round 3: Weaknesses

Tableau 5.1

While the Tableau suite is a solid SSBI offering there are shortcomings. First is that Tableau Desktop does not leverage an in-memory column-oriented data store. Secondly, the product does not ship in an x64 build. These two factors together result in working with massive volumes of data more ‘painful’ when compared to the PowerPivot experience. Tableau’s formula language is impressive and in ways its strength is in the language’s simplicity; however the language is conversely nowhere near as extensive as that found in PowerPivot’s DAX. Tableau Desktop’s array of data sources is impressive but for some reason the product does not support basic ODBC/OLEDB connections.

Tableau Server is a good server product for the desktop authored insights but the product by its very nature cannot offer near all of the features of that found in SharePoint Server (PowerPivot Server). Tableau Server pales in comparison to PowerPivot for SharePoint 2010 and there is one central reason why. No, it’s not that PowerPivot for SharePoint has rich looking Silverlight previews of workbooks (although that doesn’t hurt). Tableau Server fails because the product does not include managed features such as that found in PowerPivot for SharePoint. There are no managed concepts built into Tableau Server beyond basic security options.

PowerPivot 2010

PowerPivot is not without its flaws as well. First and foremost is that PowerPivot for Excel still feels a bit more analyst/IT oriented of a tool than Tableau Desktop. Sure, Excel is used by nearly everyone who owns a PC, but not everyone that uses Excel uses pivot tables and formulas. For example, my own mother is an accountant at a local TV station and she uses Excel almost daily. Now, in her case she does happen to use Excel formulas, but she does not use pivot tables. The point is that while Excel is probably the best delivery vehicle for Microsoft SSBI, that doesn’t mean the product is always the easiest tool on the market to use.

Moving along, DAX is an awesome expression language (I love it and learn more about it daily) but that’s the problem. I am a BI/IT professional and thus if I like a particular expression language it probably infers the language has depth to it and can be complex. By no means am I saying that DAX was a bad move but the expressions can become complex, and quickly. PowerPivot for Excel does not support parent/child hierarchies either. Finally is that of data security requirements. PowerPivot for Excel does not contain any data security features.

PowerPivot for SharePoint is impressive to say the least. I only have one major issue with PowerPivot for SharePoint and it’s just a result of having SharePoint as a backend as well as the sheer number of components involved. PowerPivot for SharePoint deployment is not a task for the faint of heart. I hope that by RTM the installations are as simple as they can be given all of the steps and environmental variables they must accommodate.

And the winner is...

This is not an easy decision, in fact the more I learn about each of these two products the more I come to appreciate all of the hard work and mental sweat that I know took place in creating them. If your company is looking for an IT oriented (i.e. powerful client experience and IT manageability features) SSBI product that is geared for integration with corporate BI solutions& PowerPivot is a no brainer. If your company is looking for a business user centric (not quite as powerful but very user friendly) SSBI platform with little to any IT usage or corporate BI integration, Tableau should be your choice.