Power BI DirectQuery: Everything You Need to Know

If you are working with Power BI, you might have encountered the term DirectQuery. But what does it mean, and how does it work? In this blog, we will answer these questions and more. We will explain what Power BI DirectQuery is, how it differs from Import mode, and its benefits and limitations.  

We will also show you how to use Power BI DirectQuery for Data Modelling and Analysis, optimise its performance, and follow the best practices. By the end of this blog, you will better understand Power BI DirectQuery and how to use it effectively. 

Table of Content 

1) What is Power BI DirectQuery? 

2) Advantages and disadvantages of Power BI DirectQuery 

3) Power BI DirectQuery for Data Modelling and Analysis 

4) Optimising performance and best practices for Power BI DirectQuery  

5) Advanced functionality and use cases of DirectQuery 

6) Conclusion 

What is Power BI DirectQuery? 

Power BI DirectQuery is a feature that allows you to connect to your data sources without importing or loading the data into Power BI. Instead, Power BI sends queries to the data source, displaying the results in real-time. You can access the most up-to-date data and perform complex queries on large datasets exceeding the Power BI capacity limits. 

Power BI DirectQuery differs from Import mode, which is the default option when connecting to a data source. Import mode copies the data from the data source and stores it in a compressed format in Power BI. It allows you to perform fast and interactive Data Analysis but also requires you to refresh the data periodically to keep it in sync with the data source. 

Power BI DirectQuery is suitable for scenarios where you need to: 

a) Analyse large datasets that exceed the Power BI capacity limits (1 GB per dataset for Pro users and 10 GB per dataset for Premium users) 

b) Access the most recent data and immediately reflect any changes in the data source. 

c) Perform complex queries not supported by Power BI or require custom logic. 

d) Comply with security or privacy policies that prevent you from copying or storing the data in Power BI.
 

Advantages and disadvantages of Power BI DirectQuery 

Power BI DirectQuery is a data connectivity mode that allows you to create reports and dashboards based on data that is stored in its original source without importing it into Power BI. This means that you can access the most current data and avoid the size limitations of importing data. 

However, using DirectQuery also has some drawbacks and implications that you should be aware of before choosing this option. Here are some of the main advantages and disadvantages of Power BI DirectQuery: 

Advantages of Power BI DirectQuery 

Power BI DirectQuery has several advantages such as: 

a) Real-time data: DirectQuery reports always use the latest data from the source, without requiring any data refresh or scheduling. This is useful for scenarios where the data changes frequently and you need to monitor it closely. For example, you can use DirectQuery to track the stock market, the weather, or the sales performance of your company in real time. 

b) No size limit: DirectQuery does not import any data into Power BI, so you don’t have to worry about the size of the dataset or the storage capacity of Power BI. You can connect to very large semantic models or databases that would be impractical or impossible to import. For example, you can use DirectQuery to analyse billions of rows of data from an SQL Server database or a Spark cluster. 

c) Data security and governance: DirectQuery leverages the security and governance features of the data source, such as authentication, authorisation, encryption, and auditing. You can also implement Row-level Security (RLS) to restrict data access based on the user's identity or role. For some sources, such as SQL Server Analysis Services, you can also use Single Sign-on (SSO) to pass the user credentials from Power BI to the source. 

Disadvantages of Power BI DirectQuery 

Power BI DirectQuery also has some disadvantages and limitations such as: 

a) Performance issues: DirectQuery relies on the performance and availability of the data source to generate the report visuals. Every time you interact with the report, Power BI sends queries to the source and waits for the results. This can cause delays or timeouts if the source is slow, busy, or offline. To avoid performance issues, you should optimise the data source and the report design and use caching and aggregation features in Power BI. 

b) Limited functionality: DirectQuery imposes some limitations on the functionality and features of Power BI, such as Data Modelling, data transformation, and Data Visualisation. For example, you cannot add calculated columns using DAX, you cannot use some Power Query functions or connectors, and you cannot use some visual types or custom visuals. You should check the list of limitations and implications of using DirectQuery before choosing this option. 

c) Complexity and maintenance: DirectQuery requires more technical knowledge and skills to set up and maintain than importing data. You need to understand the data source structure and schema, the query language and syntax, and the network and security configuration. You also need to monitor and troubleshoot the data source and the Power BI service to ensure the reliability and accuracy of the reports. 

Here’s the table that summarises the advantages and disadvantages of Power BI DirectQuery 

Want to know more about identifying and connecting to data sources in Power BI, join our Microsoft Power BI Data Analyst PL300 Course.     

Power BI DirectQuery for Data Modelling and Analysis 

Despite the disadvantages and limitations of Power BI DirectQuery, it is still possible to use it for Data Modelling and Analysis, as long as you follow some guidelines and best practices. Here are some of the main steps and tips for using Power BI DirectQuery for Data Modelling and analysis: 

Relationship establishment 

The first step for using Power BI DirectQuery for Data Modelling and Analysis is establishing the relationships between the tables and columns in the data sources. It is important for creating accurate and meaningful reports and dashboards and optimising the performance and efficiency of the queries. 

To establish the relationships, use the Power BI Desktop or the Power BI Service and go to the Model view. There, you can see the tables and columns available in the data sources and drag and drop them to create the relationships. You can also edit the properties of the relationships, such as the cardinality, direction, and cross-filter direction. 

When creating the relationships, you need to consider the following factors: 

a) Use the native keys and indexes of the data sources: When creating the relationships, you should use the native keys and indexes of the data sources, such as the primary keys, foreign keys, and unique indexes. It will ensure that the relationships are consistent and reliable and improve the performance and efficiency of the queries. 

b) Avoid creating unnecessary or redundant relationships: When creating relationships, you should avoid creating unnecessary or redundant relationships, such as many-to-many, bi-directional, or inactive relationships. This will prevent confusion and ambiguity and reduce the complexity and overhead of the queries. 

c) Use the Composite mode for multiple data sources: If you want to use Power BI DirectQuery with multiple data sources in the same report, you need to use the Composite mode, which allows you to combine data from different sources and create complex calculations and aggregations. To use the Composite mode, you need to enable it in the Power BI Desktop or the Power BI Service and then choose the storage mode for each table, either Import or DirectQuery. You can also create relationships between the tables from different sources if they have the same storage mode. 

Direct transformation 

The second step for using Power BI DirectQuery for Data Modelling and Analysis is to perform the necessary transformations on the data, such as renaming, formatting, filtering, sorting, grouping, and more. It is important for preparing and cleaning the data for analysis and optimising the performance and efficiency of the queries. 

To perform the transformations, use the Power BI Desktop or the Power BI Service and go to the Transform Data or the Edit Queries option. There, you can see the tables and columns that are available in the data sources and apply the transformations using the ribbon, the formula bar, or the advanced editor. 

When performing the transformations, you need to consider the following factors: 

a) Use the native functions and capabilities of the data sources: When performing the transformations, you should use the native functions and capabilities of the data sources, such as the SQL functions, operators, and clauses. It will ensure the transformations are compatible and efficient and improve the queries' performance and quality. 

b) Avoid performing unnecessary or complex transformations: When performing the transformations, you should avoid performing unnecessary or complex transformations, such as adding columns, merging tables, pivoting tables, and more. This will prevent errors and inconsistencies and reduce the complexity and overhead of the queries. 

c) Use the Import mode for unsupported transformations: If you want to perform transformations that are not supported by the data sources or the Power BI DirectQuery, you need to use the Import mode, which allows you to import or copy the data into Power BI and perform the transformations using the Power Query Editor. To use the Import mode, you need to enable it in the Power BI Desktop or the Power BI Service and then choose the storage mode for each table, either Import or DirectQuery. You can also create relationships between the tables from different sources if they have the same storage mode. 

Calculation utilisation 

The third step for using Power BI DirectQuery for Data Modelling and Analysis is to create and use the calculations needed for the analysis, such as measures, calculated columns, and DAX expressions. It is important for creating and displaying the metrics and insights that are relevant and meaningful for the analysis and for optimising the performance and efficiency of the queries. 

To create and use the calculations, you need to use the Power BI Desktop or the Power BI Service and go to the Report view or the Data view. There, you can see the tables and columns available in the data sources, and create and use the calculations using the ribbon, the formula bar, or the advanced editor. 

When creating and using the calculations, you need to consider the following factors: 

a) Use the native functions and capabilities of the data sources: When creating and using the calculations, you should use the native functions and capabilities of the data sources, such as the SQL functions, operators, and clauses. This will ensure that the calculations are compatible and efficient and improve the performance and quality of the queries. 

b) Avoid creating unnecessary or complex calculations: When creating and using the calculations, you should avoid creating unnecessary or complex calculations, such as calculated columns, quick measures, time intelligence functions, and more. This will prevent errors and inconsistencies and reduce the complexity and overhead of the queries. 

c) Use the Import mode for unsupported calculations: If you want to create and use calculations that are not supported by the data sources or the Power BI DirectQuery, you need to use the Import mode, which allows you to import or copy the data into Power BI and create and use the calculations using the DAX language. To use the Import mode, you need to enable it in the Power BI Desktop or the Power BI Service and then choose the storage mode for each table, either Import or DirectQuery. You can also create relationships between the tables from different sources as long as they have the same storage mode. 

Become an expert in Power BI by signing up for our Microsoft Power BI Course! 

Optimising performance and best practices for Power BI DirectQuery 

Power BI DirectQuery is a feature that allows you to connect to data sources without importing data into Power BI. This way, you can access the live data and see the most current results in your reports. However, using DirectQuery also has some limitations and implications you must consider. Here are some best practices for optimising performance and using DirectQuery effectively:
 

Data refresh & scheduling 

Unlike import mode, DirectQuery does not require you to refresh the data manually or schedule a refresh frequency. The data is always up to date as long as the data source is available and responsive. However, you can still use the Refresh button in Power BI Desktop or the Power BI service to refresh the visuals and clear the cache. You can also use the Refresh dataset REST API to trigger a refresh programmatically. 

Pre-aggregation & optimisation 

One of the challenges of using DirectQuery is that it can generate a large number of queries to the data source, which can affect the performance and responsiveness of the reports. To reduce the query load and improve the user experience, you can use some techniques to pre-aggregate and optimise the data, such as: 

a) Creating aggregate tables that summarise the data at a higher level of granularity and using them for visuals that do not need detailed data. 

b) Creating composite models that combine import and DirectQuery tables and using them for different purposes. 

c) Applying filters and slicers to limit the data that is queried and displayed. 

d) Using incremental refresh only loads the data that has changed since the last refresh. 

Performance recommendations 

Power BI provides some tools and features to help you diagnose and improve the performance of DirectQuery reports, such as: 

a) The Performance Analyzer pane shows the duration of each visual and the queries sent to the data source. 

b) The Query Diagnostics tool collects detailed information about the queries and the data source. 

c) The Performance Insights feature analyses the report and provides suggestions for improving the performance. 

Advanced functionality and use cases of DirectQuery 

DirectQuery in Power BI offers more than just basic functionality. It also has advanced features that suit different data sources and complex situations.  

Let’s look at these features and some examples of their use.
 

Cloud and on-premises sources 

DirectQuery connects to both cloud-based and on-premises data sources without any hassle. Imagine your data is in different places – in the cloud and on your servers. DirectQuery lets Power BI access both sources without moving or copying your data. It’s like having a translator who can speak both languages, ensuring your Power BI reports can get and analyse data wherever they are. 

Example: What if your customer data is in the cloud, but your sales transaction data is on-premises? DirectQuery lets Power BI join these sources, giving you a complete picture of customer behaviour and sales performance without transferring data. 

Multidimensional sources 

DirectQuery can also work with multidimensional sources, which adds more complexity to your Data Analysis. Multidimensional sources are data arranged in cubes or hierarchies, often used in databases for complicated business scenarios. DirectQuery lets Power BI interact with these complex structures, giving you a more profound and subtle analysis. 

Example: Imagine your financial data is in a multidimensional cube, showing different aspects like time, geography, and product categories. With DirectQuery, Power BI can explore these dimensions, letting you see detailed financial insights without changing or simplifying the data structure. 

Enterprise features 

DirectQuery has enterprise-level features that meet organisations' needs for large-scale Data Analysis. These features are about scalability, security, and manageability. In simple terms, they make sure Power BI can handle the big tasks required by big businesses while maintaining performance and data quality. 

Example: Consider a large corporation with many departments, each with its own data. DirectQuery’s enterprise features let Power BI handle and analyse huge amounts of data from various departments, ensuring decision-makers have a full understanding of the organisation’s performance. 

Excel in the field of Business Intelligence reporting by learning the necessary skills with our Microsoft BI Training. Sign up now!   

Conclusion 

Power BI DirectQuery is a feature that allows you to connect to your data sources without importing or copying the data into Power BI. This way, you can access and analyse the most up-to-date and live data in real-time without worrying about data refreshes, storage space, or processing power.