In this blog, we will talk about different modes of importing data from sources in Power BI. Power BI is a versatile tool. It offers four modes of importing data i.e., Import Mode, DirectQuery, Live Connection and Mixed. We will go through these four modes in this blog.
Import Mode:
With this method, the data from the source will be cached into Power BI. It means the data will be loaded into Power BI’s memory. When you’re developing the report on your system with Power BI Desktop, it will consume your system’s disk space and memory. Once you are done with the development of the report and you have deployed the report on Power BI Services, it will consume Power BI cloud machine’s disk space and memory.
Benefits of using Import mode:Supports data integration.
1. Supports data integration.
2. Performs all types of transformation in Power Query Editor.
3. Supports entire DAX functionalities.
Limitations of using Import mode:
1. Data refresh reloads entire table on every refresh.
Direct Query Mode:
DirectQuery connects to the data source directly. Power BI offers DirectQuery connection to relational databases only. Power BI itself writes the query and send it to the data source to retrieves the required data. Using DirectQuery we just import the schema of the data source’s table that is the reason DirectQuery model won’t occupy the disk space and memory as compared to Import mode model.
Benefits of using DirectQuery mode:
1. Can create with larger volume.
2. Delivers near real-time data to report.
3. Keeps our systems memory free.
Limitations of using Direct Query mode:
1. Does not support data integration.
2. Limited DAX functionality support.
3. Negative impact on data source’s performance.
Live Connection Mode:
Live connection is same as DirectQuery in sense of not storing data in Power BI’s memory. It queries data from the modeling engines. Following are the data source supported for Live connection,
1. SQL Server Analysis Services (Tabular or Multi-Dimensional)
2. Azure Analysis Services
3. Power BI Service.
The difference between the Direct Query and Live connection is that, in Live connection you don’t need to model the data or perform any calculations in Power BI Desktop. The Edit Queries and all related options of that are disabled in the Live Connection mode. Everything you will do it on the data source only. Once the data source gets refreshed, the latest data will be directly reflected on the report developed by Live connection. You don’t need to refresh the data. Live connection separates model development from report development.
Mixed Connection Mode:
Mixed connection develops composite model using Import mode and DirectQuery mode. You can leverage the benefits of each mode on table basis in a single model
This is all about the mode of development in Power BI. I hope you will find this article helpful
Gaurav Lakhotia
Data Analyst
Addend Analytics