Mainly, Power BI Desktop support two types of data source connection mode- Import & Direct Query mode.
Import Mode-
In Import mode Power BI Desktop store the data inside Power BI Cache. If your data size is less then 1 GB or data not continually changing then you can use Import mode.
Import mode is very fast compare to Direct Query mode because all data comes from Power BI Desktop Cache.
DirectQuery Mode-
Direct Query data connectivity mode allows you to Direct Connection with live data base. It does not load or copied data into Power BI model, because data directly came from data source.
Import Mode Vs DirectQuery Mode feature comparison-
| Feature | Import | DirectQuery |
| Size | Up to 1 GB per dataset | No limitation |
| Data Source support | Import data from Multiple sources | Data must come from a single Source |
| Performance | High-performance query engine | Depends on how fast the network connectivity and data source is as queries are executed in real-time. Only metadata and schema structure is stored on the Data model |
| Data Change in the underlying data | Not Reflected. Required to do a Manual refresh in Power BI Desktop and republish the report or Schedule Refresh | Power BI caches the data for better performance. So, it is necessary to Refresh to ensure the latest data |
| Data storage in Power BI | Since it is a cached mode, data is stored in the Power BI Service (cloud) | Data will not be stored in the Power BI cloud service. Data resides on-premises |
| Schedule Refresh | Maximum 8 schedules per day | Schedule often as every 15 mins |
| Power BI Gateway | Only required to get latest data from On-premise data sources | Must require to get data from On-premise data sources |
| Data Transformations | Supports all transformations | Supports many data transformations with some limitations |
| Data Modelling | No limitation | Some limitations such as auto-detect relationships between tables and relationships are limited to a single direction. |
| Built-in Date Hierarchy | Available | Not available |
| DAX expressions | Supports all DAX functions | Restricted to use complex DAX functions such as Time Intelligence functions. However, if there is a Date table available in the underlying source then it supports |
| Clustering | Available | Not available |
| Calculated Tables | Available | Not supported |
| Quick Insights | Available | Not available |
| Q&A | Available | DirectQuery support for Q&A (preview) |
| Change Data Connectivity mode | Not possible to change Import to DirectQuery | Possible to change DirectQuery to Import |
| Security | Can create row-level security on the PBI dataset (import only) | Re-use on-prem row-level security for Analysis Services Tabular using DAX expressions |
| Target Audience | Small and Medium Datasets | Large Datasets (Greater than 1 GB) |
Refer more Power BI Post – Power BI
Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome.