PowerView vs. PowerPivot vs. Power BI

With Power BI being announced for general availability on Office 365, we have a customers who are seeing the various Microsoft business intelligence products that have been evolving over the past few years. Microsoft is marketing the entire suite of them as “Power BI” as they promote their latest version. However, Power BI is actually a specific set of add-on products to existing technologies that have been around in some cases since Excel and SharePoint 2010.

This means that even if you don’t have the latest versions of everything (Excel, SharePoint, etc.) you can still leverages specific versions of these tools. Here is the breakdown on the differences between PowerView, PowerPivot and Power BI.

Excel 2010 and SharePoint 2010: The Introduction of PowerPivot

The first tool that was added to the Microsoft BI suite was PowerPivot. PowerPivot was introduced with Excel 2010 and SharePoint 2010. It is a free add-on that provides Excel with a dynamic, in memory database for developing business intelligence models based on multiple data sources. It supports KPIs, calculated columns and millions of rows of data.

PowerPivot workbooks (e.g. Excel workbooks with PowerPivot data included) can be deployed to SharePoint 2010 and visualized using Excel Services which renders Excel workbooks as web pages. Visualizations are provided through Excel PivotTables and PivotCharts

Excel 2013 PowerPivot Improvements

In Excel 2013, there is a new more advanced PowerPivot add-on that provides additional functionality compared to Excel 2010:

Filter data when importing. You can import data in both Excel and Power Pivot, but when importing data in Power P

  • Filter data when importing. You can import data in both Excel and Power Pivot, but when importing data in Power Pivot, you can filter out unnecessary data to import just a subset.
  • Rename tables and columns as you import data in Power Pivot.
  • Manage the model and create relationships using drag and drop in the Diagram View.
  • Apply formatting (to be used in Power View and PivotTable reports).
  • Define your own calculated fields to use throughout a workbook.
  • Define key performance indicators (KPIs) to use in PivotTables.
  • Create user-defined hierarchies to use throughout a workbook.
  • Define perspectives.
  • Author your own calculations by writing advanced formulas that use the Data Analysis Expressions (DAX) expression language.

Use other more advanced data and modeling operations.

  • Filter data when importing. You can import data in both Excel and Power Pivot, but when importing data in Power Pivot, you can filter out unnecessary data to import just a subset.
  • Rename tables and columns as you import data in Power Pivot.
  • Manage the model and create relationships using drag and drop in the Diagram View.
  • Apply formatting (to be used in Power View and PivotTable reports).
  • Define your own calculated fields to use throughout a workbook.
  • Define key performance indicators (KPIs) to use in PivotTables.
  • Create user-defined hierarchies to use throughout a workbook.
  • Define perspectives.
  • Author your own calculations by writing advanced formulas that use the Data Analysis Expressions (DAX) expression language.
  • Use other more advanced data and modeling operations.

One of the key differences in performance is size limitations – in Excel 2010, the maximum size is 2 gigabytes in memory and 4 gigabytes on disk. In Excel 2013, there is no limitation.

SQL 2012 and SharePoint 2010: Introducing PowerView

SQL Server 2012 introduced a new product called PowerView. PowerView is a SQL Server Reporting Services add-on that can be deployed to SharePoint 2010 Enterprise Edition or SharePoint 2013.

The PowerView in this version is entirely SharePoint driven and you create PowerViews directly in SharePoint 2010 against existing PowerPivot models as well as other datasources (SQL Server, DB2, Oracle, etc.)

Excel 2013: Incorporating PowerView

Excel 2013 introduced PowerView directly into Excel. PowerView is a very easy to use dashboarding tool that allows self-service business intelligence. While in Excel 2010, you used PivotTables and PivotCharts (which have been around for ages) to visualize and explore your data, PowerView is an entirely new visualization experience.

In addition, the Excel 2013 version of PowerView introduced some improvements over the original SharePoint 2010 PowerView version including:

  • Pie C
  • Harts
  • Maps
  • KPIs
  • Hierarchies
  • Drill Up and Drill Down
  • Format Reports with Styling, Themes, and Text Resizing
  • Background Color and Background Images
  • Hyperlinks
  • Printing
  • Support for Right to Left Languages

Excel 2013 PowerViews need SharePoint 2013 to be deployed – you cannot deploy them back to SharePoint 2010.

Microsoft’s New Business Intelligence Add-On – Power BI

Microsoft has just released its new BI add-on – Power BI. Where it becomes confusing is that Power BI leverages existing Excel 2013 and SharePoint 2013 PowerPivot and PowerView functionality and adds on a few cool new features.

As described above, you do not need Power BI to leverage PowerPivot, PowerView, Excel 2013 and SharePoint 2013. You can continue to use all the features above as they were defined when these features were released.

Power BI is only available on Office 365 and Excel 2013. There is no Power BI for SharePoint on premise at the current time. You can use the Power BI Features with Excel 2013 on the desktop and then deploy Excel 2013 workbooks to Office 365 to share your creations with other users.

Power BI introduces several key features that build on the existing PowerPivot and PowerView tools and provides more capabilities:

  • Power Query is a new tool that allows for dynamic querying of more data sources than was available in PowerPivot.  Some of these new data sources include Azure Blob Storage, Azure Table Storage, Hadoop, Azure HDInsight, Folders, etc.
  • Power Map provides a new 3D geo-location based mapping tool based on Bing Maps.  It provides the ability to create tours of your maps for story telling purposes.
  • Natural Language Search allows users to ask questions against your Excel workbooks using natural language queries. 
  • Power BI sites allow for better sharing of reports and data views.  They also increase the limits for the sizes of workbooks that can be successfully rendered by SharePoint in the browser.
  • Power BI introduces the concept of a data catalogue where PowerPivot models can be published to SharePoint and shared through a centralized catalogue.
  • Mobile Access is provided through Windows 8 and HTML 5.

Power BI Pricing

As mentioned above, Power BI is only available through Office 365. It is sold either as as an add-on to your existing E3/E4 subscription or as a standalone subscription.

Christopher Woodill

About ME

Enterprise technology leader for the past 15+ years…certified PMP, Six Sigma Black Belt and TOGAF Enterprise Architect. I collaborate with companies to help align their strategic objectives with concrete implementable technology strategies. I am Vice President, Enterprise Solutions for Klick Health.

Leave a Comment