Eugene Lebedev is a Power BI consultant by background. Before founding Vidi Corp in 2021 he created Power BI reporting for Autodesk. His Power Bi reports were used by VPs of Finance and C-suite of Autodesk. As part of Vidi Corp, Eugene created dashboards for Google, Teleperformance, Delta Airlines and 200+ clients worldwide.
Introduction to Power BI direct query and import
In Power BI, connectivity modes refer to the different ways in which you can connect to and retrieve data from various data sources. Power BI supports different connectivity modes to accommodate a wide range of data scenarios.
It is important to know data connectivity modes because some Power BI functionality which is available in Import mode is not available for Direct Query mode. In this article we will cover which functionality is available for each connectivity mode in Power BI and explain what happens in the background when a certain mode is selected.
There are 4 connectivity modes:
1. Import Mode: In this mode, data is imported from the data source into Power BI’s internal data engine called the Power BI Desktop.
2. Direct Query: In DirectQuery mode, Power BI connects directly to the data source without importing the data.
3. Live Mode: Live Connection mode is similar to DirectQuery, but it is specifically designed for certain data sources, such as SQL Server Analysis Services (SSAS) models.
4. Composite Mode: This is the combination of Import and Direct query mode where fact tables are connected as Direct Query and Dimension tables are connected as Import mode.
Power BI Import Mode Vs Direct Query
Import Mode in Power BI refers to the data connectivity option where data is imported from external data sources into Power BI’s internal data model. When using Import Mode, data is loaded and stored within the Power BI Desktop or Power BI service, enabling fast and efficient data visualisation and analysis. Here’s a detailed explanation of Import Mode in Power BI:
1. Data Import and Storage:
In Import Mode, you start by connecting to one or more external data sources such as Excel files, databases, cloud services, or other supported sources.
Power BI retrieves the data from these sources and imports it into the Power BI Desktop or Power BI service environment.
The imported data is stored within the Power BI internal data model, which is an in-memory columnar database optimised for fast querying and data manipulation.
Once the data is imported, you have access to all the data transformation steps inside of Power Query, such as cleaning, shaping, filtering, merging, and creating calculated columns or measures.
You can also define relationships between tables, create hierarchies, and apply data modeling techniques to enhance the structure and analysis capabilities of your data.
3. Visualisation and Analysis:
With the data imported and transformed, you can create interactive visualisations, reports, and dashboards using Power BI’s rich set of visualisation tools.
Power BI offers a wide range of chart types, tables, matrices, maps, and custom visuals to present your data effectively.
You can explore and analyse the data by applying filters, slicers, and drill-through actions to gain insights and answer business questions.
Power BI supports interactive features like cross-filtering and highlighting, enabling users to dynamically interact with visualisations and explore data from multiple angles.
4. Refreshing Data:
In Import Mode, the imported data is not automatically updated unless you manually refresh it or configure scheduled refresh options.
Manual refresh allows you to manually trigger a data refresh to pull in the latest data from external data sources.
Scheduled refresh allows you to define a refresh schedule, ensuring that your data stays up to date in Power BI without manual intervention.
Note that the availability of scheduled refresh depends on the Power BI license and the type of data source.
5. Performance Considerations:
Import Mode offers high-performance data analysis since the data is stored and processed within Power BI’s memory.
Power BI’s in-memory columnar database enables faster query execution and interactive visualisations even with large datasets.
However, it’s essential to consider the hardware resources and memory capacity of the machine running Power BI to ensure optimal performance when working with large or complex datasets.
Let’s understand how to connect to Import mode in Power BI: Open the Power BI desktop and click on ‘Get Data’. Select SQL server connector and click ‘OK’.
Enter the server details and database name. Here you need to select the connection type and select ‘Import mode’ from the data connectivity option.
Provide your credentials and select the table to load.
It will show the data in the preview mode and clicking on load will load the data to the Power BI desktop whereas Transform data will take you to the Power Query mode.
Features in Import Mode
1. Power Query mode will be enabled in import mode:
2. Data view will be available on the Power BI desktop to see the complete data model.
3. Calculated columns and tables can be created in the data model and Measure can be written without any limitations in Power BI.
Import Mode is a common choice when working with small to medium-sized datasets or when you require extensive data transformations within Power BI. It provides fast and interactive data analysis capabilities, allowing users to visualize and explore data efficiently.
DirectQuery Mode in Power BI is a data connectivity option that enables direct and real-time querying of data from external data sources. Unlike Import Mode, which loads and stores data in Power BI’s internal data model, DirectQuery Mode connects directly to the data source and retrieves data on demand.
Here’s a detailed explanation of DirectQuery Mode in Power BI:
In DirectQuery Mode, Power BI establishes a direct connection to the external data source, which can include databases like SQL Server, Oracle, or cloud-based platforms like Azure SQL Database or Amazon Redshift.
Instead of importing the data into Power BI, the queries and calculations are sent directly to the data source for processing.
Real-Time or Near Real-Time Data: DirectQuery Mode enables real-time or near real-time access to the data in the external data source. Whenever you interact with a visual or execute a query in Power BI, the query is sent to the data source, and the results are returned immediately, reflecting the most up-to-date data.
Query and Calculation Pushdown: In DirectQuery Mode, as queries are sent to the data source for processing, it is more efficient to perform calculations and aggregations within the source database engine. This means that complex calculations, filters, and aggregations can be performed inside the database, by writing SQL views which utilises the computational resources of your database rather than Power BI. By offloading the computation to the data source, Power BI can work with large and complex datasets without requiring the entire dataset to be loaded into memory.
Data Transformation Limitations: Unlike Import Mode, where extensive data transformations can be applied within Power BI, DirectQuery Mode has limitations on the types of transformations that can be performed. Power BI’s Power Query Editor is available in DirectQuery Mode, but some transformations may not be supported or may require custom SQL queries to be written in the source database.
Limited In-Memory Storage: In DirectQuery Mode, Power BI does not store the data in its internal data model. Instead, only metadata, such as table structure, relationships, and calculations, are stored in memory. This allows Power BI to efficiently manage memory resources and work with large datasets without consuming excessive memory. It is an appropriate connectivity mode when your pbix files become too heavy to open. This can especially be a problem for people with old laptops that struggle to handle 100 MB files. You can reduce the size of a pbix file drastically by switching from Import to Direct Query mode.
Performance Considerations: DirectQuery Mode provides real-time access to data, but the performance can depend on factors such as the complexity of the queries, network latency, and the performance capabilities of the data source. It is important to optimise the database design, indexes, and query performance on the data source side to ensure efficient query execution and responsiveness in Power BI.
Data Source Compatibility: DirectQuery Mode is compatible with various data sources, including relational databases, cloud-based platforms, and data warehouses.
In order to connect a data source through direct query mode, we need to follow the same process as we followed in import mode except below change. We need to select direct query as an option in the data connectivity field:
Power BI Features in Direct Query Mode
Calculated columns and tables cannot be created in Direct Query connection
No data view is available in the direct query connection.
Measures can be created in direct query but time intelligence functions are not supported.
We cannot define relationships between tables.
Column formatting, default summarisation, and sort order cannot be done.
However, not all data sources and connectors support DirectQuery Mode, so it is necessary to check the official Power BI documentation or consult the connector documentation for compatibility information.
How to establish Live Mode
In Power BI, a live connection refers to a direct connection between Power BI and the data source. When you establish a live connection, Power BI queries the data source in real time whenever a user interacts with a report or dashboard. Live connectivity in power BI supports below data sources only:
A dataset that already exists in the Power BI service
Azure Analysis Services (AAS) database
An on-premises instance of SQL Server Analysis Services (SSAS)
Using Composite Mode
A composite model in Power BI allows you to combine different connection types within your model. This means that you can have a section of your model connected to a data source using DirectQuery (e.g., SQL Server database), while another section can import data (e.g., an Excel file). Previously, when using DirectQuery, it was not possible to incorporate additional data sources into the model.
With the introduction of the composite model, you now have the flexibility to include large tables sourced from the DirectQuery connection, without the requirement of importing them. Meanwhile, smaller tables can be imported for quicker accessibility. Let’s understand with the below example where we have 2 tables in our database.
Purchase.vendor will be connected as Import mode and Sales.Credit card as Direct query.
We can check the connectivity mode of the table in the model view.
Conclusion
This article has highlighted the importance of different connectivity modes in Power BI. Choosing the appropriate connectivity mode depends on factors such as the size of your dataset, the need for real-time data, the capabilities of the data source, and performance requirements. It’s important to consider these factors to ensure an optimal experience when working with data in Power BI.
