Power BI supports connecting to various data sources natively. For many of them, you can just import data and use it for reporting. Some sources also support connecting data without importing it using DirectQuery. Let’s find out what it is, how it works, and when you should consider using this connectivity mode.
What is DirectQuery in Power BI?
DirectQuery is a connectivity mode in Power BI that allows you to extract data directly from database sources like SQL Server and Snowflake. When you use this mode, Power BI creates schema (metadata) that defines the model structure of the connected source. When you use this data source in a report, the model structure is queried for fresh data. This happens every time you navigate or interact with the visuals in your reports.
The DirectQuery mode is suitable when you want real-time reporting from large datasets.
DirectQuery vs Import – know the differences
Import and DirectQuery are two connectivity modes to connect to data sources in Power BI Desktop.
The Import mode copies the underlying dataset into the local memory. Now, when you create a visual or perform transformations, the data is loaded locally from the copied version of the data source.
Unlike Import, no data is stored in the memory when using Power BI DirectQuery. The underlying dataset is directly queried to fetch the data. Query caching option is also available for Power BI Premium users, where the results of queries are cached for optimum performance.
Let’s say you want to connect to a dynamic dataset containing billions of rows. In this case, importing the entire dataset into Power BI can make the report size extremely large and eat up a few gigabytes of disk space. The DirectQuery mode can be helpful in similar situations where import mode is not feasible.
How does DirectQuery work in Power BI?
In DirectQuery mode, Power BI saves the schema (metadata) of the data source to create a data model, which is then used to translate all DAX queries into SQL queries for fetching data. The data source is queried wherever the user creates or interacts with a visualization.
Let’s see how the Power BI DirectQuery works on both Desktop and Service.
Connect data source to Power BI Desktop
In Power BI Desktop, you can connect to on-premise and cloud data sources using DirectQuery.
For example, here’s how you can connect to an SQL Server.
Open a blank or existing report in Power BI Desktop. In the Home tab, click Get data and choose SQL Server.
Enter the address of your SQL server and the database name you want to connect to.
Under the Data Connectivity mode, select the DirectQuery method for the connection and click OK.
Now, enter the credentials, username and password, required to access the SQL server database.
Click the Connect button.
Once successfully authenticated, Power BI will display a preview of the tables from the SQL database server.
Select all the tables that you wish to use as a data source and click the Load button. Optionally, you can also click Transform Data to open the connection in PowerQuery and perform transformations.
The connected DirectQuery Power BI source will appear in the right pane, as shown below. You can use it to build your report and add visualizations to it.
Now, every time you create or edit a visual using this data source, Power BI will query the SQL Server and fetch the data in real time. Power BI Desktop will also show a note at the bottom about the DirectQuery connectivity mode, as shown below.
You can click this note to change the connectivity mode to Import. But be cautious, as this change is irreversible – you won’t be able to convert the same connection to DirectQuery again.
Publish report to Power BI Service
You can publish reports containing DirectQuery data sources to Power BI Service, which involves an additional step.
To access and query the DirectQuery data sources in Service, you must install an on-premise data gateway and register it. The Power BI gateway bridges the Power BI Service and the database to authenticate the requests.
Exceptions are the following data sources, which can be accessed simply using the credentials:
- Azure SQL Database
- Azure Synapse Analytics
- Amazon Redshift
- Snowflake Data Warehouse
Once you’ve published the Power BI report, you can manage the data connections and gateways from your Service account.
For example, to declare credentials for a connection, log into your Microsoft Fabric account. Click settings (gear icon on the top) and select Manage connections and gateways.
Click on the three dots against the connection name and select Settings from the drop-down menu.
In the connection settings, enter the appropriate information to authenticate the data source and click Save.
You can also set a privacy level for connection. For example, set it to Organizational to allow everyone in your organization to access it.
Now, your report containing DirectQuery Power BI data sources is ready to share with others via Power BI Service.
List of supported Power BI DirectQuery sources
The Power BI DirectQuery mode is only available for data sources that are database models or have modeling engines.
How do you connect Power BI to other data sources beyond those that support DirectQuery?
DirectQuery connections are helpful in creating real-time reports or dashboards. But you may still require other sources in your report that don’t support DirectQuery. How do you keep them up to date?
You can use Coupler.io, a reporting automation platform that lets you connect 60+ data sources to Power BI and schedule data refresh.
To get started, select the required data source in the form below and click Proceed.
You’ll be offered to create a Coupler.io account for free. Then, you need to authenticate your data source and select the data for import.
After that, you’ll be able to preview the data and organize it before importing it into Power BI.
Next, generate an Importer URL and copy it. Open Power BI Desktop, navigate to Get Data > From Web, and use the copied URL.
Your data will be loaded to Power BI. To automate this connection, set up automatic data refreshes in Coupler.io. To do that, enable Automatic data refresh and set your preferences for the refresh schedule.
That’s it – Coupler.io will perform scheduled refreshes of the data as configured. This way, you can easily connect dynamic data sources to Power BI and have access to the latest data in almost real-time.
How do you troubleshoot performance issues with your connection?
Sometimes, the DirectQuery Power BI connection can negatively affect reporting performance. Every time the data source is queried, Power BI has to wait for the response from the underlying data source for fresh updates. Therefore, the connection performance is dependent on the network latency and the underlying data source.
However, you can still optimize the DirectQuery connection for better performance on the Power BI Desktop. Here are some steps to troubleshoot performance issues:
1. Use Performance Analyzer
You can use the Performance Analyzer tool to troubleshoot a slow-loading visual using Power BI DirectQuery. In the View tab, click Start recording in the right pane to monitor the queries.
It will show you the queries the report sends to the data source, along with the processing time. You can use the diagnostic information from this tool to improve the queries and fix performance issues.
2. Analyze trace files using SQL Server Profiler
Power BI Desktop logs the current session into a trace file at the <User>\AppData\Local\Microsoft\Power BI Desktop\Traces directory. You can use the Microsoft SQL Server Profiler tool to analyze these traces and find the queries affecting the performance.
It provides details like the query processing duration, raw queries, and errors if occurred.
3. Manually update dataset schema
Power BI report stores metadata (schema) of the database connected using DirectQuery, which is then used to query it. When there is a change in the data source column name, the connection may not work due to schema mismatch, leading to connection issues.
The solution is to manually refresh the data source to update the schema changes.
Frequently asked questions
What are the Power BI DirectQuery benefits?
DirectQuery mode is the ideal option to query large or frequently changing datasets. Here are some key benefits of using it over the default Import mode:
- Real-time insights – Get near real-time data from the underlying sources and keep your reports up-to-date.
- No limits on dataset size – Query massive datasets that are too big to import (say millions of rows).
- Reduce file size – Keep the report size within the capacity limits (i.e. 1 GB for pro users) by not importing the entire dataset into it.
- Data security – Stay compliant with the data security and protection rules by using credentials.
What are the Power BI DirectQuery limitations?
The DirectQuery Power BI connectivity mode has the following limitations you should consider before using it:
- Performance issues – Sometimes, querying large datasets can take a few minutes. This can make the visuals and dashboard look sluggish.
- Limited data sources – You can use this connectivity mode mainly for databases. It may not be compatible with all data sources you connect to in the report.
- One million rows query limit – Even though there is no limit on the dataset size you can connect to, the maximum number of rows you can load from a single query is 1 million.
- Network dependency – Since the connectivity mode loads data in real time, an internet connection is required to view the report. You can not access a report with a DirectQuery Power BI connection while offline.
- Limited data transformation – You can not use all the data transformation features of Power BI with the DirectQuery connections. You may get an error to translate the connection to Import mode if the transformations are too complex.
- Data modeling constraints – This connectivity mode limits data modeling features like slicers, calculated columns, no support for parent-child DAX functions, and a lack of date hierarchy.
Can we use DAX with DirectQuery?
Yes, but with limited support. Not all DAX functions are supported with Power BI DirectQuery. For example, you can’t use DAX functions that handle child-parent structures.
Can we use Power BI Import and DirectQuery together?
Yes, you can create a composite model to combine data from both DirectQuery and Import data sources.
Expert tips to use DirectQuery connectivity mode
DirectQuery offers an efficient way to query big datasets. It can be a good choice if you want to build (almost) real-time reports.
Here are some expert tips you can use to optimize the DirectQuery model:
- Keep queries simple – In the Power Query editor, avoid using complex queries as they can take up much processing power and affect reporting performance.
- Create database index – Indexes can help the data source quickly process the query and generate results, improving server-side query processing.
- Reduce the number of measures – Each measure you create from a DirectQuery model sends a query to the data source. Therefore, it’s better to avoid unnecessary measures.
- Use Query Reduction – In Power BI Desktop, you can enable the Query Reduction option to reduce the number of queries by disabling cross highlighting/filtering. You can do this at this path: File > Options and settings > Options
Lastly, it’s crucial to understand the core purpose of DirectQuery connectivity mode. It’s ideal for working with large data for real-time reporting but unsuitable for smaller datasets because of performance implications.
For optimum performance, you can explore other methods to automate data connections including Coupler.io which lets you automate data imports in Power BI for real-time data analysis.
Automate data export to Power BI with Coupler.io
Get started for free
