NOTE: My top tips are to understand Microsoft cloud vendor data lock in risks, true cloud costs for real world use cases and assumed free software surprise fines of $100,000+ in Microsoft audits. Microsoft is not a charity. They do quickly change prices up to 22% as of 2016 and in 2017 they made significant changes to Power BI licensing models. They are the most aggressive vendor in the industry when it comes to using audits to collect fees after apps get used. Groups that thought ETL or reporting was free when they bought SQL Server or Office have learned the hard way in unpleasant audit lawsuits. Tableau and most other vendors do not go to market in this manner or treat customers this poorly.
Please don’t ask me for an update of this article. Microsoft bullied me for writing this one. It is not fun to be the bully target of staff protected by one of the biggest companies in the world.
Happy New Year! 2013 was an interesting year for Microsoft BI with the move to newly branded Power BI’s Excel and Office 365 Cloud-first, Cloud-only focus. What is Microsoft Power BI? There are a variety of Power BI flavors and it is a bit confusing to understand what Power BI features work with what versions of Office, Office 365, SQL Server, SharePoint, Excel add-ins, and so on. (UPDATE 2/7/2014 BizIntelligist added a great matrix to at http://businessintelligist.com/2014/02/07/microsoft-self-service-bi-on-premise-vs-could/ to help sort out what works where with what flavor of Power BI.) In this article to keep it as simple as possible, I will be referring to the on-premise Excel 2013 Power Pivot, Power View, Power Map, Power Query and SharePoint 2013 flavor, and the pre-release Office 365 Cloud Power BI Sites, Excel 2013 Power Pivot, Power View, Power Query and Q&A (Question & Answer) flavor. There is fantastic long term potential with Microsoft Power BI. In the short term, it is currently introducing a big change and gaps into the Microsoft BI ecosystem causing many groups to explore Tableau and other BI options.
As I mentioned in my opening blog post in 2013, I returned to multiple-BI vendor platform implementation versus the Microsoft centric/purist approach to continue to grow professionally and supplement the solid, basic BI offering Microsoft provides. Last year I dived into Tableau, R, Predixion, SAS JMP, Frontline Systems Solver, Hortonworks Hadoop, RapidMiner and a plethora of other solutions. It was not just me exploring what else was out there. Quite a few other BI professionals, Microsoft MVPs and partners in my network did the same thing quietly, “under the radar” to maintain their Microsoft status and relationships. Even a PASS Microsoft BI User Group openly showcased QlikView as a main presentation topic. Note QlikView is not a Microsoft BI supplement but rather an all-up, Microsoft BI replacement platform often pitching O-Delta for Analysis Services migration.
Due to my Microsoft BI-heavy background and vendor neutral status, one of the most frequent questions that I get these days is “how does Microsoft Power BI compare to Tableau”. There are quite a few blog posts comparing these two solutions to one another. I also hear a lot of comments about both solutions that clearly indicate a general lack of understanding about these two offerings. I don’t believe it has to be a one OR the other choice. Each BI offering has strengths and weaknesses. In my mind, these two solutions can be better together as my free recorded webcast suggests. The key is understanding the differences between these two BI offerings and when, what use cases and scenarios does one make more sense to use than the other. There are far too many differences to list in a blog. Top of mind, the big ones are as follows:
Completely Different Depth and Breadth of Functionality: Tableau, Microsoft Power BI Power Pivot and Power View may look and sound like they have similar capabilities on the surface but they are completely different with regards to both breadth and depth of functionality. Tableau has amazingly deep visual analytic and dashboard capabilities. It does not have an ETL or data modeling tool. Microsoft Power Query has great self-service ETL and Power Pivot has strong data modeling features and multidimensional formulas via DAX. Power View has very limited, basic data visualization capabilities. Power Map is a unique animal altogether; it is a stand-alone, desktop Excel add-in mapping movie tool that has map data layering capabilities. Q&A is also a unique feature not seen in mainstream BI. I usually encourage folks to try these offerings for themselves to personally experience the sheer magnitudes of differences. If you just need simple data visualizations, bar and line charts with no mobile, anonymous access on the web or embedding, Power View could fulfill your requirements. If you need an enterprise dashboard solution or advanced data visualization, you will most likely need Tableau or another third-party dashboard solution.
On-Premise BI Platform Implementation: Tableau is a closely unified BI platform. Tableau Desktop and Tableau Server can literally be installed in a few minutes or hours. A fully functioning Tableau Server can be installed on a Windows machine with 6 or 8GB of RAM for evaluation. You don’t need to join Tableau Server to a Windows domain at all. Tableau Server can be stand-alone or integrated into portals; SharePoint is not required. Tableau does not require BI Professionals to set up, manage or develop solution content for self-service or enterprise level BI.
Microsoft Power BI is a collection of different Excel add-ins, SQL Server services features, SharePoint and/or the Office 365 Cloud. Although desktop Power Pivot add-ins can be easily installed in Excel 2010 and is already embedded along with Power View in Excel 2013, Microsoft BI on-premise server set ups are complicated, one week or longer installations and configurations of SharePoint, SQL Server and Reporting Services that also require Active Directory accounts and Kerberos for authentication. (Note: Power View is both embedded in Excel 2013 but also as a stand-alone version via Reporting Services Power View reports hosted in SharePoint. Each version has a different mix of capabilities. For example, Excel 2013 Power View can’t connect to Analysis Services multidimensional models a.k.a DAXMD support and can’t export to PowerPoint. Reporting Services Power View can connect to Analysis Services multidimensional models a.k.a DAXMD support and views can be exported to PowerPoint, etc. Excel Power View and Reporting Services Power View can’t be swapped out/imported between the two different Power View versions.) On-premise SharePoint Enterprise 2013 needs a minimum of 32GB RAM. In general, Microsoft BI set up and configuration usually requires BI Professionals, SharePoint Professionals and Network Administrators to participate – it is not at all a business user level endeavor. Microsoft Power BI content development ranges from simple Excel Power View reports that any business user can create to more advanced multidimensional Power Pivot models with DAX that BI Professionals create. BI Professionals usually create enterprise level Microsoft BI solutions.
Cloud BI Implementation: Both Tableau and Microsoft Power BI Cloud offerings are easy to set up – like many cloud solutions they take a few minutes to get spun up and running. Tableau Cloud BI was released in summer 2013. Microsoft Power BI previewed in the fall of 2013, has monthly iterative releases but is not yet released to public as of January 2, 2014. Both solutions copy on-premise and web data to Cloud their storage models. Tableau copies data via scheduled data refreshes to Tableau Data Extracts (TDE) in the cloud. Microsoft copies data via Data Management Gateway to Office 365 shared queries and Excel Power Pivot models.
Data Source Connectivity: Tableau offers a deep library of enhanced drivers for data connectivity across many vendor data sources. This is an area of significant engineering investment and cross-vendor collaboration difference.
Microsoft Power BI can only connect to a subset of vendor data source types that Tableau offers. Microsoft primarily invests in enhancing SQL Server connectivity. Many other vendor data sources are not as elegant to use with Power BI.
ETL and Data Cleansing: Tableau does not have an ETL tool. There was something shown at Tableau Customer Conference in September 2013 that looked like a future data modeling, loading, ETL-like offering might be coming. Tableau does offer a free data reshaping Excel-add in that has some basic pivoting and cleansing features. Tableau also has some data mash up capability via Blends matching column names from different data sources. Usually Tableau will refer Alteryx to groups wanting true, self-service ETL since there is nice integration of between the two complementary offerings.
Microsoft Power Query is quite nice and has both basic and advanced self-service ETL capabilities including pivot/unpivot, joining, filtering, deduplicating, grouping, splitting, and transforms. More advanced functionality can be achieved via Power Query M scripting. Although Power Query is in preview and a v 1.0 offering, it gets monthly enhancements and already has proven to be a great tool for personal data collection and shaping. I blogged several times about Power Query (formerly called Data Explorer) last year.
Semantic Model Development: Tableau automatically develops a base dimensional semantic model (Tableau Shared Data Source) of dimensions and facts that can be easily customized and published locally or to Tableau Server for building additional reports with one shared version of the truth. Tableau does not have deep data cleansing or preparation capabilities – some cleansing can be done in calculations, functions and groupings. To enhance the base Tableau semantic model, you can easily, visually develop calculations, functions, logical groups, sets, hierarchies and other features from the front-end solution when visually exploring data. There is a rich, deep library of VizQL (Visual Query Language) capabilities, added functional enhancements to several data source types such as Hadoop jar functions and R function integration. Although Tableau can be used with combined data sources via Blending, it does lack a data modeling diagram view. Semantic model development is easy, rapid, intuitive and powerful with one or two combined data sources.
Microsoft Power Pivot has Power Query and Power Pivot wizards for easily loading data. (Power View is not a semantic modeling tool.) Loaded data does not automatically become a base dimensional semantic model. Power Pivot authors need to have some basic dimensional modeling knowledge and skills to create a good base dimensional semantic model with time intelligence type features. Power Pivot does have rich data modeling capability for combining multiple data sources together and using role playing dimensions. The rich modeling capability comes at the cost of a steep DAX learning curve. Power Pivot models need to be published to SharePoint for scenarios where you want to build additional reports from a shared one version of the truth model. DAX is used to develop calculations and groups. Hierarchies can be built with a simple drag and drop of fields in a nice diagram view. Adding calculations and making model changes do require going into the back-end Power Pivot model. These kinds of changes can’t be done from the front-end Power View displays when visually exploring data making the process a bit more time consuming and awkward. Semantic model development has a learning curve but is quite powerful with one or several combined data sources.
Time Intelligence: In Tableau, time intelligence is automatically created. Tableau also allows the user to easily, visually define custom calendars and offers a variety of default popular time intelligence calculations that users do not have to script/code to use such as Year over Year, Year To Date, Month to Date, etc.
Microsoft Power Pivot does not have out-of-the-box time intelligence or calendars built. Power Pivot authors need to create calendar data sets and write DAX code for DAX Time intelligence features and calculations to work.
Data Connectivity and File Size Limits: Tableau models have in-memory and direct connect capability options meaning data does not have to be copied out of the data sources to local or server files. There is no limit per se to the data sizes or amounts used in Tableau models. Direct connectivity with large scale capability is a big differentiator.
Power Pivot is an in-memory model with no direct connect capability option unless the Excel Power Pivot model is upgraded to Analysis Services and is using a SQL Server relational data source. Excel Power Pivot and Power Query loads data copies to the local Excel Power Pivot file. There are local Excel file limits of 1 million if using an Excel worksheet as the data source. If you load data directly to a Power Pivot model you can get about 50 million to 80 million records before you hit the file size limits. If using on-premise Power BI and the Power Pivot model is uploaded to SharePoint, the file size and model has to be less than 2 GB in size. If using Power Pivot models in Office 365 Power BI Cloud, these models are limited in file size to 250 MB. If the data resides only in Excel worksheets, the limit in file size is 10 MB.
Functions, Filtering and Multi-Pass Calculations: Tableau has intuitive, front-end VizQL functions, visual filtering and *two-pass calculations – not true multi-pass calculations. (*I got the multi-pass clarifications from BI Industry Analyst readers, Fredrik Tunvall and Cindi Howson. Thanks!) Tableau also has Table Calculations and scopes for relative cell visual analytics.
Microsoft Power View does have front-end visual filtering but does not have front-end functions or calculations. Functions and calculations would be done in Power Pivot with DAX. Power View and Power Pivot do not have comparable relative cell visual analytics or multi-pass calculations per se. Relative cell visual analytics in Power Pivot can be simulated with advanced DAX concepts of context and filtering.
Trends, Forecasts and Statistical Functions: Tableau has visual trends, forecasts, and a variety of both linear and nonlinear statistical functions, confidence intervals, test values, statistical significance, quartiles and other advanced analytics in the semantic model and visual displays. Tableau also has R function integration for advanced analytic functions.
Microsoft Power Pivot DAX has statistical functions but the front-end Power View tools do not have comparable visual trending, forecasting capability or visualization of statistical features.
Front-End HTML 5 and Silverlight: Tableau uses HTML5 and is fully supported on a wide variety of browsers and mobile devices today.
Microsoft Power View was designed in Silverlight. The Office 365 Power BI Cloud version of Power View has an HTML5 option in preview for some of the chart types. On-premise Microsoft BI Power View is only Silverlight today and not mobile device friendly.
Microsoft Power View has basic bar charts, line charts, pie charts, a scatter chart and non-customizable Bing Maps that can show pie charts or circles – no thematic mapping or overlays. Power View is lacking combination charts, dual axis charts and many other common chart types. Power View does not allow granular control of visualizations, axes, colors, labels, annotations, drill actions, conditional formatting or visualization display logic, visual banding, statistical reference lines or custom marks. There is not an API for automatically building Power View visualizations. Only Power View scatter charts in can play over time periods. Power View does not support easily viewing underlying details or exporting the view data. You can do some simple drill drowns. There is no comparable export of Power View detail data shown on a view. To see the raw underlying detail data, you need go back to the back-end Power Pivot model. Power View does not have features for changing data color variations, KPI icons are only circles, no chart object sizing, contouring and no customization of tool tips. Power View does have text sizing and limited chart labeling options.
Mapping: Tableau has geospatial and thematic maps that overlay, allow use of WMS servers, GIS sources and custom shapes. Tableau maps support things like drawing a radius circle. Tableau maps do not use 3D charts and do not have movie/video recording like Power Map.
Microsoft Power Map has 3D, flat thematic mapping, heatmap visualizations, data layer overlay mapping and unique movie/video recording features. These features are only available in the stand-alone Power Map add-in for Excel 2013 desktop and can’t be published to SharePoint or the Office 365 Cloud BI Sites right now. Both Power View Maps and Power Map do not have radius circle features.
URL, Filter and Highlight Actions: Tableau offers URL, Filter and Highlight Actions to allow combination of applications, web, mapping and other features for interactive, guided analytics.
Microsoft Power View does not have any comparable capabilities in this area but does allow HTML links.
Visual Parameters: Tableau has dynamic parameter capabilities allowing dynamic values to be used for what-if type analysis or in calculations, filters, and visual banding or reference lines lines. Tableau parameters also allow for advanced dynamic visual reporting where the dimensions and measure change or are unknown at the time of development.
Microsoft Power View has URL and Pinned Filters for dynamic filtering with parameters passed via URL string but those values can not be passed down into Power Pivot model functions or calculations in the view. There is no visual banding or reference lines in Power View and no advanced dynamic visual reporting where the Power View chart dimensions and measures are unknown at the time of development. Power View requires a Power Pivot or Analysis Services model. In Excel 2013, a non-optimized Power Pivot model is automatically built with Excel worksheet data when a Power View sheet is added and an existing Power Pivot or Analysis Services model is not already used in that Excel file.
Personalization/Saved User Preferences: Tableau allows users to choose their dashboard view filters and save those selections via Favorites for personalized dashboard experiences.
Published Microsoft Power View reports do not have a comparable personalization settings capabilities.
Subscriptions: Tableau allows users to subscribe to dashboards for them to be automatically emailed to them on a schedule.
Microsoft Power View does not have a comparable subscriptions capability.
SharePoint Web Parts: Tableau has a SharePoint Web Part and comes with the Visual Studio solution that allows further customization. Tableau can also be embedded into SharePoint pages with the Page Viewer Web Part.
Microsoft Power View does not have a SharePoint Web Part. You can use the SharePoint Page Viewer Web Part or Silverlight Web Part. Passing parameters is not easy or a business user level activity. It entails customizing the Silverlight xap file to pass parameters via SharePoint Web Part connections or custom coding to pass parameters via URL string to Power View URL and Pinned Filters.
Large Data Set Views: Tableau is capable of rendering large data sets of 80 million plus data points on a chart.
Microsoft Power View can not render more than 1000 data points on a chart. Power View charts use sampling techniques at 1000 data points to guesstimate the display of large data sets.
External and Anonymous Users Scenarios: Tableau can easily support multi-tenancy, anonymous access and external web reporting use cases that Microsoft Power BI, Power Pivot and Power View do not. Tableau supports a variety of authentication mechanisms and data security mechanisms like trusted-tickets, SAML, user filtering and others for large scale dashboard distribution. Tableau also has free Tableau Public and Reader dashboard viewing offerings.
Microsoft Power BI requires Excel 2013, SharePoint or Office 365 accounts, Active Directory and Kerberos authentication. External deployment scenarios are complicated if even possible and/or cost becomes extreme. Microsoft Power BI does not offer anonymous or free viewing. A very limited Web Site Excel Viewer called Excel Mash Up is available for viewing anonymous, public Excel files stored on SkyDrive. Power BI content and data limited to local Excel 2013 files can be stored, emailed and viewed by other Excel 2013 users but no Excel data security can be applied in that scenario.
Native Mobile BI Applications: Tableau has mature native iPad and Android mobile applications, as well as support for a wide variety of mobile tablet types with touch sensitive HTML5 content. Tableau mobile BI includes Mobile Web Editing and Mobile Authoring use cases.
Microsoft Power BI, Power Pivot and Power View continue to have weak mobile BI capabilities. Right now Microsoft Power BI has a Windows-device only native Power BI mobile BI app preview for Excel and Power View files hosted in the Office 365 Power BI Cloud offering only. There is no native Power BI mobile BI app for on-premise Power BI deployments. There is some browser-based HTML5 mobile BI capability with native Excel Services, not Power View, when deployed in SharePoint or Office 365. In the future, an additional Power BI mobile app for iOS to show Office 365 Cloud hosted Excel and Power View content is expected and has been talked about for several years now. There has been no news on if/when an app for Android devices would be developed.
Microsoft Power BI, due to the collection of stand-alone Excel add-in tools, has a very different approach to Developer APIs. Excel historically has used VBA APIs but VBA is not supported in SharePoint or Office 365 published Power BI files. Office Web Apps “Project Agaves” are an interesting option that might be viable in the future but have not been used much as an API yet. Typically embedded BI scenarios with Power BI require SharePoint and Visual Studio custom-solution development.
Parameterized Incremental Data Refresh: Tableau offers parameterized Incremental Data Refresh of models.
Microsoft Power Pivot does not have Incremental Data Refresh. It completely reloads all the data when a model is refreshed unless the model is upgraded to Analysis Services. One possible work-around for local Power Pivot models and Office 365 Power BI in the Cloud is to use Power Query M scripted parameterized queries with data connection refreshes. It is not a true incremental data load – it dynamically full reloads a filtered parameterized data set. On-premise Power Pivot in SharePoint only has full refreshes.
Question & Answer (Q & A): Tableau does not have Q & A.
Q & A is a Microsoft Power BI in the Office 365 Cloud only feature that allows users to pick an Excel Power Pivot file and type in free text questions to automatically have a few charts built showing possible answers. Like Power Map, Q & A is a unique, very different BI feature not usually seen in mainstream BI today. It is a little like Apple iPhone Siri for published Excel Power Pivot files.
Monitoring and Governance: Tableau and Microsoft Power BI both have management and monitoring. Tableau has central management and monitoring. On-premise Microsoft Power BI management and monitoring is located in a few places (SQL Server, SharePoint Admin, Reporting Services Admin, etc.). Office 365 Power BI in the Cloud has central management and monitoring.
Pricing: Don’t quote me on exact costs – check licensing prices for yourself. These are rough prices from publicly posted price lists seen in the past and can change at any time.
Tableau Desktop costs ~$1999 and Tableau Server is around $1000 per user or ~$200K for an unlimited user license. Tableau Cloud costs $500/per user per year. The Tableau Cloud plan includes 100 GB of storage. I do not know the storage add-on costs for groups wanting more than 100 GB.
Microsoft Power BI costs range widely depending on what you actually choose to use. Microsoft 2013 Excel Professional Plus, stand-alone is around ~$240 a year per user. The Office 365 Subscription model that Microsoft is shifting to is priced at $20/per user per month. On-premise Microsoft Power BI with SharePoint Enterprise eCals for pusblishing and sharing Power BI content for all users is an added cost of ~$90 per user. SQL Server 2012 Enterprise Edition (needed for Power BI features, SQL Server 2012 BI Edition can be a little cheaper for groups with less than 250 users) new Core versus CAL based model pricing at $6,874 per Core runs about $27K for a minimum 1 CPU w/4 Core Server with no Software Assurance pricing included. Granted Microsoft Enterprise Agreements do usually discount SQL Server pricing, the number and size of needed SQL Server 2012 Enterprise servers for Microsoft BI solutions is usually underestimated. You will typically have one for the database, one for Power Pivot in SharePoint services, one for Analysis Services, another one for Reporting Services and possibly one for ETL Integration Services. Often I hear people say they already own SQL Server 2012 Enterprise so there is NO added licensing cost for Microsoft Power BI. In reality, Microsoft Power BI server and Cloud solutions are usually NOT free of added licensing cost. Just because you have a Microsoft Enterprise Agreement does not mean you already own all the SharePoint and SQL Server Enterprise licenses that you need to deploy Microsoft Power BI. Those added servers will be audited and counted in your Enterprise Agreement contract renewal process. You don’t want to have to explain a large, surprise SharePoint and SQL Server bill when it is too late. It is much better to double-check your license usage and know how Microsoft Power BI licensing works up-front, as you design your BI solution. Do not be naive about licensing. It is not at all uncommon for the total added Microsoft Power BI solution server licensing to exceed $200K.
UPDATE 1/4/2014: Microsoft Power BI Cloud pricing was just released. There are three plan options:
1) Power BI Cloud costs $480/per user per year + *Storage
2) Power BI Cloud with Office 365 ProPlus costs $624/per user per year + *Storage
3) Office 365 E3/E4 ~$240 + Power BI $240 costs $480/per user per year + *Storage until 6/30/2014
Office 365 E3/E4 ~$240 + Power BI $396 costs $636/per user per year + *Storage after 6/30/2014
*Storage: For Cloud BI projects, plan storage limits and costs would be important to know and actively govern. I am not sure yet how Power BI file storage usage works/costs with these plans and base plan storage included per user. I know as a current Office 365 subscriber that I have a storage add-on option in my Administrator panel to pay more per month for extra Office 365 storage. I don’t publish files online so I never looked into my Office 365 plan base file storage limits or storage add-on costs. I did find an MSDN blog on storage price lists and also how to add storage to your plans. I will update this post again when I find these answers. If anyone knows how storage works/costs with Office 365 and Power BI plans, please share those details with me. UPDATE: 5/14/2014 I finally got the details on storage. You can review them at http://office.microsoft.com/en-us/office365-sharepoint-online-enterprise-help/sharepoint-online-software-boundaries-and-limits-HA102694293.aspx.
I know there are many other differences in these two platforms from architecture to user experience. These differences also change as the platforms quickly evolve. If you want to dive deeper into this topic with me or feel I have not accurately shared a difference, please contact me directly and I may add that feedback here. I already added pricing that went live after my initial post. I try to keep up with many mainstream BI tools and once in a while I do miss a key improvement.
A lot of people don’t realize just how much these two self-service BI platforms differ from one offer. The best thing you can do is learn what each one offers and then play with them to see where each one fits in your specific BI landscape. Nothing is more revealing than a hands-on evaluation or proof of concept done by the developers and users themselves – not the BI vendor or BI vendor’s aligned partner. It can be helpful though to get some vendor support and a good checklist of BI features to review so you don’t overlook a critical requirement during your hands-on evaluations.