What is the difference between PowerPivot, PowerQuery and PowerBI? How should one decided which tool should be used and when.
75
votes
5 Answers
134
votes
Power Query
Power Query is a self-service ETL (Extract, Transform, Load) tool which runs as an Excel add-in. It allows users to pull data from various sources, manipulate said data into a form that suits their needs, and load it into Excel.
Power Pivot
Power Pivot is an in-memory data modelling component that enables highly-compressed data storage and extremely fast aggregation and calculation. It is also available as part of Excel, and can be used to create a data model within an Excel workbook. Power Pivot can load data itself, or can have data loaded into it by Power Query. It is extremely similar to the SSAS (SQL Server Analysis Services) Tabular model, which is like a server-based version of Power Pivot.
Power View
Power View is an interactive visualisation tool which provides users with a drag-and-drop interface allowing them to build quick and easy visualisations of the data in their Excel workbooks (using the Power Pivot data model).
Power BI
Power BI is a SaaS service which enables business users to service their own business intelligence needs. It provides built-in ability to connect to SaaS services like Salesforce and many others. It provides connectivity to on-premises and cloud sources using a combination of direct query and periodic data refreshes. It is available as a freemium service. It is the successor to 'Power BI for Office 365' that was based on Microsoft's Office 365 and SharePoint Online products, and, through Excel 2013, encompasses Power Query, Power Pivot, and Power View.
Power BI (with O365 and SharePoint Online) provides a site where users can upload and share their created content with other users, as well as manage gateways into enterprise data source, enable data refresh, and advanced features like Q&A, which allows natural language querying of data models.
Microsoft has also released a standalone Power BI Desktop application, which ties together Power Query, Power Pivot, and Power View in a standalone application, removing the Excel 2013 constraint. Power BI Desktop is available for free.
It is also possible to achieve a lot of the functionality of Power BI using on-premise SQL Server 2012+, Excel 2010+ and SharePoint 2010+, if cloud-based is not an option for you.
23
votes
Simple answer:
PowerBI=PowerQuery+PowerPivot+PowerView
Alternate answer:
If
[PowerQuery]="Clean Data/M language" and [PowerPivot]="Calculations/DAX language" and [PowerView]="Data visualization"
then
[PowerBI]=concatenate ("PowerQuery","-","PowerPivot","-","PowerView")
else "Excel"
Hope this answers the question :)
8
votes
Power View is an interactive data exploration, visualization, and presentation experience that encourages intuitive ad-hoc reporting. Power View is a feature of Microsoft Excel 2013, and of Microsoft SharePoint Server 2010 and 2013 as part of the SQL Server 2012 Service Pack 1 Reporting Services Add-in for Microsoft SharePoint Server Enterprise Edition.
Power Map for Excel is a three-dimensional (3-D) data visualization tool that lets you look at information in new ways. A power map lets you discover insights you might not see in traditional two-dimensional (2-D) tables and charts.
5
votes
I like both answers from GShenanigan (tools breadown) and excelislife (simple answer). But I will merge them all into my own synopsis to provide an updated definition as of 2018 November.
What is Power BI?
Simple answer: PowerBI=PowerQuery+PowerPivot+PowerView
If
[PowerQuery]="Clean Data/M language" and [PowerPivot]="Calculations/DAX language" and [PowerView]="Data visualization"
then
[PowerBI]=concatenate ("PowerQuery","-","PowerPivot","-","PowerView")
else "Excel"
Another perspective of the PBI Tools
I have removed Power View, because I do not think this is relevant anymore. It was a canvas sheet for building visualizations (charts, maps, graphs, table), but now this is done in PBI Desktop.
Power Query
Users connect to a data source and build a data set. Drag-and-Drop interface to build ETL steps with the ability to also write custom code (Language: M). Data is loaded to Excel table or PowerPivot data model. NOTE: There are a variety of “connectors” for accessing many different data sources. To name a few: Database tables (SQL Server/Oracle/SSAS...), ODATA Feed, SaaS services (Salesforce/Exchange/…), Files (text/csv), and more.
Power Pivot
Users import data sets and build a data model. Measures are Calculations/Aggregations (Language: DAX). Report visuals are built off of the data model (DATASETS + RELATIONSHIPS + MEASURES) using the PowerBI visualizations canvas.
Power BI
PowerBI Desktop, or Excel, is where you build the reports (charts, maps, graphs, table). PowerBI uses the PQ and PP steps to get the data.
There are few ways to share these reports as seen on this blog post, "PBI 15 ways to share", https://blog.coeo.com/power-bi-15-ways-to-share-data. Here are the most relevant for enterprise collaboration based upon my experience.
- PBI Service Deploy Power BI reports to the service, https://app.powerbi.com/. These are overarching visualizations of a business overview, created by pinning visualizations from one or many reports. You publish dashboards inside and outside your organisation by hitting the share button when viewing the dashboard. Recipients of the share require a Pro licence (you pay per user per Month), or you deploy the content to premium capacity. You can configure the ability for internal recipients to re-share during the sharing process. Re-sharing can also be turned off completely in the Power BI admin portal.
- On-Premises Report Server Deploy Power BI reports alongside paginated SSRS reports.
- Embed - App Owns Data Embedded within third-party applications, requires no authentication by the end-user to Power BI.
- Share PBIX The PBIX file is open by the users in the development environment (PBI Desktop). On-premises and free, no PBI Pro license required.
- Export to Excel or CSV Click the ellipses on the top right of a visualization and select “export data”.
Hope this helps!
0
votes
Power BI: Power BI is a Business Intelligence and Data Visualization tool which helps you to convert data from the various data source into interactive dashboards and BI reports. It provides multiple software connectors and services.
Power Query: It allows you to discover, access, and consolidate information from different sources.
Power Pivot: A modelling tool.
Power View: It is a presentation tool for creating charts, tables, and more.
Power Map: Helps you to create geospatial representations of your data.
Power Q&A: This allows you to use natural language to get answers to questions.
