In a DirectQuery model, tables with their storage mode set to DirectQuery form part of the same source group, which is a collection of model tables linked to a data source.

Two types of source groups exist:

1. Import: Encompasses all tables with import storage mode, including calculated tables. A model can have only one import source group.

2. DirectQuery: Encompasses all tables with DirectQuery storage mode associated with a specific data source.

DirectQuery model benefits

There are several benefits of DirectQuery. These are enlisted here:

1. Model large or fast-changing data sources

   Opting for a DirectQuery model is advantageous for voluminous and rapidly changing source data. DirectQuery tables, tailored for large data stores such as data warehouses, eliminate the need for regular refreshes, making them ideal for dynamic datasets. Importing an entire data warehouse into a model is often impractical and inefficient due to its size.

   In scenarios where source data evolves swiftly, and users require real-time insights, a DirectQuery model excels by providing near-instantaneous query results. When a report interacts with a DirectQuery model, Power BI directly sends the queries to the underlying data source.

2. Enforce source RLS

   DirectQuery proves advantageous when the source database implements row-level security (RLS). Rather than duplicating RLS regulations in the Power BI model, the source database can independently enforce its rules. This method is applicable to specific relational databases and requires the establishment of single sign-on for the dataset data source.

3. Data sovereignty restrictions

   If your company enforces security policies preventing data from leaving its premises, importing data becomes unfeasible. In such cases, utilizing a DirectQuery model that establishes a connection to an on-premises data source might be the suitable approach.

4. Create specialized datasets

   In most cases, DirectQuery mode in Power BI is designed for relational database sources, facilitating the translation of analytic queries into native queries understood by the data source.

   However, there is a notable exception. Users have the capability to establish a connection to a Power BI dataset or an Azure Analysis Services model and transform it into a DirectQuery local model. The term "local model" denotes its relationship to another model, often referred to as the remote model, in a chained setup. Chaining allows up to three models to be linked in this manner.

   This ability to chain models opens avenues for personalization and extension of a remote model. Basic modifications include renaming objects such as tables or columns and adding measures to the local model. Furthermore, the model can be expanded with calculated columns, calculated tables, or the inclusion of new import or DirectQuery tables. Notably, these extensions lead to the creation of new source groups, transforming the model into a composite model.

   DirectQuery model limitations

   In most cases, DirectQuery mode in Power BI is designed for relational database sources, facilitating the translation of analytic queries into native queries understood by the data source.

   However, there is a notable exception. Users have the capability to establish a connection to a Power BI dataset or an Azure Analysis Services model and transform it into a DirectQuery local model. The term "local model" denotes its relationship to another model, often referred to as the remote model, in a chained setup. Chaining allows up to three models to be linked in this manner.

   This ability to chain models opens avenues for personalization and extension of a remote model. Basic modifications include renaming objects such as tables or columns and adding measures to the local model. Furthermore, the model can be expanded with calculated columns, calculated tables, or the inclusion of new import or DirectQuery tables. Notably, these extensions lead to the creation of new source groups, transforming the model into a composite model.

Boost DirectQuery model performance

When there’s a justification to develop a DirectQuery model, you can mitigate some limitations in two ways.

Data source optimizations

You can optimize the source database to ensure the expected analytic query workload performs well. Specifically, you can create indexes and materialized views, and ensure the database has sufficient resources for all workloads.

DirectQuery user-defined aggregation tables

You can add user-defined aggregation tables to a DirectQuery model. User-defined aggregation tables are special model tables that are hidden (from users, calculations, and RLS). They work best when they satisfy higher-grain analytic queries over large fact tables. When you set the aggregation table to use DirectQuery storage mode, it can query a materialized view in the data source.

SWETA SARANGI

26.02.2024