Power BI let’s you connect different types of data sources. Power BI has different data connectors to connect those data sources. There are two different Data Connectivity modes in Power BI; viz; Import & Direct Query. Both of these connectivity modes have their own unique features and capabilities.
Import Connectivity mode: Import Data Connectivity mode lets you import data into Power BI from the source. Importing in Power BI means consuming memory and disk space. As long as you are developing Power BI on your machine with Power BI Desktop, then it would be memory and disk space of your machine. When you publish the report into the website, then it will be memory and disk space of Power BI cloud machine. Import connection is recommended when the data size is less than 1 GB and the data is not continually changing. You can import data with schedule refresh to get the latest data. This is the fastest method available.
You can use Power Query to combine data from multiple sources, or DAX to write advanced time intelligence expressions or the visualization. There will be no limitation in the functionality of Power BI with this method. You can use all components.
Direct Query: Direct Query Connectivity mode connects directly to data. No data will be imported into Power BI. Instead Power BI will send queries to the data source upon building visual/interacting with visuals. Power BI will only store metadata of tables (table names, column names, relationships), not the data. Direct Query mode used to build Real-time or near real-time BI solutions when the data is changing frequently, and Data volume is very large. Each query is restricted to return less than or equal to 1 Million rows.
You can check for the supported data sources for Direct Query in the below link.
https://docs.microsoft.com/en-us/power-bi/connect-data/power-bi-data-sources
The main advantage of this method is it will not have the limitation of the size. Mainly because no data stored in the Power BI file, so you never get any issue for the size of data.
The limitation is it doesn’t support the utilization of full functionality. Data must come from a single data source. It doesn’t support some complex DAX functions such as Time Intelligence functions. This connection is slower than other types of connection. In Direct Query mode every visual sends a query to data source and data comes back. You usually have more than one visual in your report and with slicing and dicing every time you are sending queries to the data source. It takes more time.
Praisy Joy
Data Analyst
Addend Aanalytics