The first thing you do when creating a Power BI report is to connect to a data source. Some data sources actually allow you to select the Data Connectivity mode and there are two options available: Import and DirectQuery.
We will explain the general differences and advantages of Import and DirectQuery mode so you know which one to select. Because the list of data sources constantly evolves and some data sources only support certain connectivity modes, we refer to Power BI data sources where you can see the up-to-date list of data sources.
One of the data sources which support both data connectivity modes is SQL. When you load data from an SQL server you need to select the Server, Database, and Data Connectivity mode.
⚠️ It’s important to know what once you have selected Import, you can’t change it to DirecyQuery later on. On the other hand, you can change a DirectQuery connection to Import mode also later in the process.
Let’s look at the main differences between the two:
Import: The data is imported into Power BI Desktop. When you interact with the report, the imported data is shown. All features of Power Query Editor and Power BI are available
DirectQuery: No data is imported into Power BI Desktop. When you interact with the report, the data is loaded from the underlying data source so you’re always viewing current data.
When looking at just the above, your obvious choice might be to select DirectQuery because then, you always have current data. Unfortunately, it’s not that easy and there are many other things to consider because there are some limitations (but also benefits) when using DirectQuery:
Limitations of DirectQuery:
– Performance is slower because the data is loaded from the source system, every time the user interacts with the report.
– For some data sources which support DirectQuery, the Power Query functionality is not available so all transformations need to happen in the source system.
– Time intelligence features are not fully supported
– Not all DAX functions are available
– The number of rows that can be aggregated is limited, depending on the data source and your Power BI subscription.
Benefits of DirectQuery:
– You can use huge amounts of data which might not be possible in import mode where you first have to load all the data into Power BI Desktop
– You always see current data
– Under certain circumstances, SSO can be used to access the underlying data source so there’s no need to separately specify access roles in Power BI. Find out more.
These were the main differences between the two Connectivity modes and the one you chose depends very much on the specific requirements and circumstances. We can’t give some general advice but what we can see is that most reports which we are dealing with are using Import mode so this seems to be the best solution for many scenarios. But again, it heavily depends on the specific case.