Power BI has enough adapters that enable a user to connect to every type of data source as it isn’t always that the data source would be a desktop file. Various Power BI connectors, based on the type of data source you are connecting to, help you to choose a connection type and alter the Power BI desktop functionality.
When connecting a data source like excel to Power BI, the data just gets transferred by default. The only scenario with an option to choose a type of connection arises while connecting a data source to Power BI from databases like MS SQL, Oracle, etc. with multiple connection types.
Power BI connectors are of three modes:
Import mode
The first one, namely Import which is most widely used and default for most of the data sources. This connection will ingest or extract the data from the data source and become part of the PBI Desktop file.
This Import connection type allows users to utilize the full suite of transformation and data manipulation in Power BI Desktop. It consumes and pushes the data into the Power BI Azure backend (Azure DB/Blob Storage).
Users can experience the features in this mode by looking at the left-hand navigation and can look at three selections.
- The top selection which resembles a bar chart is the report page. This is where you would place all your visuals and develop your report pages
- The second item from the top, which looks like a table is just that, the data view in a table form. This lets you see all the data contained with a loaded data table
- Finally, at the very bottom, the relationship selection. This is where you will see multiple tables and the connections between the tables
Advantages of Import Mode:
- The import mode offers excellent performance since the data resides inside the data model
- Avoids throttling due to network latency or data source bottlenecks
Limitations:
- Data isn’t of real-time
- Updated data is only reflected after scheduled data refreshes are completed
- The size limitation of 1 GB (per dataset)
- 8 refreshes per day for Pro & 48 times for premium accounts
Direct Query mode
- The Direct Query connection type is only available when users connect to certain data sources
- In this mode, data will not be pulled into the Power BI Desktop instead leaves the data in the data sources, it is only queried
- The Power BI assumes the user is already doing all the necessary data manipulations in their source. As a result, users will not have the option to mashup data and that “selection” feature is removed in the left-hand navigation
Advantages of Direct Query Mode:
- The data is not stored locally, users have no restriction on dataset size
- No more scheduling of a data refresh as real-time data is available with each visual interaction
- Only metadata, i.e., schema, tables, and columns are visible (Type of XMLA)
- All transformations are supported
Limitations:
- No support for the Power BI Q&A feature
- Relationship feature has been removed
- The ability to manipulate data on the desktop is limited
- Bi-Directional filtering is not supported
- Multiple data sources per dataset are not supported
- Time Intelligence is not supported
- 1 Million-rows limitation for each query
- Quick insights not supported on the Power BI service
- Complex queries using multiple expressions sometimes does not work
Live Connection mode
Multidimensional, Azure Tabular and Tabular on-premises are only 3 data sources that support the live connection method currently. Those are a type of (SSAS) SQL Server Analysis Services
- The live connection type is the most unique in that it recognizes the full model or cube created by the user
- Power BI desktop turns off all data preparation features in this mode and thus, the user is given a bare minimum in formatting and report side calculations
- All the complex lifting is done on the server that supports the model and Power BI is only used as a reporting tool in this case
- This connection is used mainly for IT and enterprise implementations
- If looked at the left-hand navigation, Users can quickly realize that it is the most restrictive in terms of what can be done in the desktop itself
Choosing the right Data Connectivity mode:
Among the three Power BI connectors, choose one according to the need and the size of the data you are playing with.
- If the data set’s size is less than 1 GB and the data isn’t changing frequently, import mode is highly recommended to use. Users can import data with a scheduled refresh to get the latest data
- The direct query mode is best used if you are building a real-time or near real-time BI solutions, the data volume is very large and is changing frequently
- The live connection mode is best used if the user is working with complete real-time data and require continuous reflection of Realtime data over the Power BI dashboard
Power BI helps your business by delivering the most necessary and all other insights that you might consider while making a decision. With a customized dashboard, get access to the hidden insights from your data. Schedule a demo with us for more information on how we can help your business transformed with Power BI and other Digital Transformation solutions.
