Microsoft Power BI Desktop: Difference between Power Query, Power Pivot, and Power View

Share on facebook
Share on twitter
Share on linkedin

Are you beginning your Career Journey with Microsoft PowerBI? Do you want to learn Power BI?

Well here in this article, I will explain about Microsoft Power BI and its essential components which are essential to understand in order to work on Power BI Desktop.

Microsoft Power BI Developers who are not from an excel background are often confused about PowerPivot, Power Query, and Power View.

Microsoft PowerBI is an integrated platform that is made from components of Excel BI toolkit such as Power Query, Power Pivot, and Power View.

Do not worry, If you do not know much about these components beforehand. I have tried to explain each of these in great detail.

Power Query:

Power Query is the ETL Component that helps the developer to extract, transform and load the data into PowerBI.

There are a lot of transformation options provided in order to transform your data and prepare it for data modeling.

Ribbon Bar

Some of the common transformation options provided in the Power Query are listed below:

-Group by
-Use First Row as Header
-Transpose
-Reverse Rows
-Count Rows
-Change Data Type
-Rename Column
-Split Column
-Apply Statistical Function/ Math Functions / Trigonometry Function
– Keep Top Rows/ Keep Bottom Rows
– Remove Columns
-Merge Queries
-Append Queries

With the help of Power Query, You can extract data from multiple sources such as File, Databases, Azure products, web sites and a lot of modern platforms.

There is three connection type through which you can extract data into Power Query.

1. Import
2. Direct Query
3. Live

Import Connection-

Microsoft Power BI default connection type is the Import connection. Import is the only connection type that will allow you to leverage the full capability of the Power BI desktop. Using import data is pulled from the data source connected and stored and compress the data into the PBIX file. Using import, you can use a complete power query for mashing up the data from various data sources. Once you come out of the Power Query data is loaded to PowerBI frontend into the Vertipaq engine. Import connection is generally suitable when data volume is little around 5 million because as the size of data increases it becomes difficult to handle the data in the PBIX file and the system used to get hanged.

DirectQuery Connection-

In Direct Query, Data remains within the data source only some queries are sent to the database to return data in order to show Visualization. There are some pros and cons while working with Direct Query which is very much important to understand when to use it and when not to use it.

Pros of Using Direct Query

1. With Direct Query, data is not stored in the PBIX file so there is not to place any schedule refresh. However on-premise gateway is required in order to establish a connection with the data source. 2. In Nov 2017, the Query Reduction feature has been added to Power BI Desktop. This helps to reduce traffic and improve the connection method exponentially. This allows enabling an “Apply Button” and sends the query to the database only once. Before this method, Query to the database was sent on each selection.

Cons of Direct Query

1. When Direct Query is used, you cannot mash up much of the data in the power query because it is assumed that you would have already done it in the backend. Even if you don’t receive any error in power query, you can receive it once you come out of it and go modeling tab to make the data model. 2. Data Tab is also disabled in the Power View. You have to make sure that all the data transformation has been already done in the backend. 3. Time Intelligence function is unavailable in Direct Query mode. 4. There is a limit of one million row limit on returning data for showcasing Visualization unless you have a premium subscription.

Live Connection

Live Connection is provided by SSAS ( SQL Server Analysis Service ) in order to connect it with Power BI.

The live connection allows you to use your data model for multiple reports thus leveraging the use of the data model created in SSAS.

Pros of Live Connection

1. The central model that supports many reports. I have explained above how Data Model created in SSAS can be used for multiple reports.

2. No Memory or Size Constraints in Power BI

3. More Secure and Better Security

Cons of Live Connection

1. It disables most of the Power BI features. Data Pane, Relationship Tab, Dax Tables, Calculated Columns, M/ETL/Power Query are all gone like as it is in Direct Query. It is expected to do all transformation, ETL operations within the SSAS or at the database level. However, From May 2017 Microsoft allow us to use calculated measure with a live connection.