Power BI Direct Query: DirectQuery is an alternative method of querying data in Power BI. Find out everything you need to know about DirectQuery.
When working with data in Power BI, it is essential to choose the connectivity mode. Import mode is generally the most commonly used, in combination with the Tabular model. This allows you to run queries and generate reports very quickly. However, a less well-known alternative is DirectQuery.
What is Power BI Direct Query?
Power BI Direct Query is a method for retrieving data directly from its source, at the time of the query. Whereas Import mode stores a snapshot of the data in memory, Power BI Direct Query (DQ) does not store any data.
With each query, the data is extracted directly from the source. The data therefore resides within the original source before, during and after the query is executed.
With each query, users generate a query or a set of queries that need to be executed in a specific order. The Tabular model is made up of the Formula Engine (FE) and the Storage Engine (SE).
The FE accepts queries, creates a query plan, and generates the query based on the choice between Import and DirectQuery modes to target the corresponding data source.
If Power BI Directy Query mode is chosen, the Formula Engine translates the DAX into SQL and sends the query directly to the data source. The data is not imported by PowerBI from the underlying tables. Only the metadata is retained.
Using Power BI’s Composite models, it is possible to combine Import and DirectQuery modes within the data model. The preferred option can be chosen for each table. Note that it is possible to switch from DirectQuery to Import mode, but not vice versa.
Finally, Dual mode is a combination of Import and DirectQuery modes. Data from the table is loaded into memory, but can also be retrieved directly from the source at query time.
When should Power BI Direct Query be used?
It is advisable to use Power BI Direct Query rather than Import mode in a number of situations. For example, if you need real-time data. Import mode keeps snapshots of data that are updated periodically. DQ mode is a better choice for real-time queries.
DirectQuery is also good for large data models. If a .pbix file of maximum size is not sufficient, this method is more suitable. The data remains at source and the calculations are performed directly there.
However, in some cases, DirectQuery is not the best choice. If your source database is not optimised for an analytical workload, and queries need to target multiple tables, the report generated with DQ will be incorrect.
The number of users interacting in parallel with the report also has an impact. Each parallel user generates simultaneous queries to the underlying data source. The performance of the source server and network latency must also be taken into account.
How can I optimise the data source in Power BI Direct Query?
There are various techniques for improving the performance of the data source. Indexes can be added, and data integrity can be ensured.
Persistent objects can also be created in the source database. Aggregations, transformations and calculations can be materialised in a special table or indexed view. In this way, Power BI can retrieve all the data from a single location, rather than performing complex operations each time a query is run.
Tips and tricks for Power BI DirectQuery
There are a number of ways to make the most of Power BI and DirectQuery. Firstly, avoid complex Power Query transformations. Each transformation applied to the data model generates a query transmitted to the source database.
If you need to use calculated columns, create them on the source database and keep them persistent. Also avoid complex DAX measures, as DAX statements need to be translated into SQL.
Avoid relationships on GUID columns, as this type of data is not supported by Power BI. It will therefore be necessary to apply a data conversion during query execution. Performance will be impacted. The solution is to convert this type of data within the source database before Power BI generates its queries.
Limit parallelism as much as possible, by defining a maximum number of connections that can be opened simultaneously. This number can be configured in the Options. It is also preferable to limit the number of visuals on the report page, as each visual increases the time required to retrieve data.
How do I learn to use Power BI?
The Power BI Direct Query method is one of the many features of Power BI. To learn how to use this platform, you can choose DataScientest training courses.
The Business Intelligence module of our Data Analyst course covers:
The other modules cover:
- Python programming
- data visualisation
- text data management
- and Big Data.
On completion of the course, you will have all the skills needed to become a Data Analyst.
This job involves analysing a company’s data to help it make better decisions. The results of the analyses are shared in the form of visualisations and reports, enabling executives and managers to exploit them. Power Bi is one of the many tools used by data analysts.
DataScientest’s training courses showcase a cutting-edge Blended Learning methodology, seamlessly integrating online learning with in-person MasterClasses.
Participants have the flexibility to choose between an immersive BootCamp format, designed to be completed within a matter of weeks, or opt for lifelong learning, allowing for a harmonious balance between personal or professional commitments.
Find out more about the Data Analyst course, and register now below:
