Composite Models and DirectQuery in Power BI

Every Power BI table has to get its data from somewhere, and the choice of how, the storage mode, affects speed, freshness, cost and which features work. Microsoft's guidance is unusually direct: use import by default, and move beyond it only when specific constraints such as latency, size, governance, security or architecture require it (Microsoft Learn: DirectQuery in Power BI). This article explains when those constraints apply, what DirectQuery costs, and how composite models let you mix modes in one model.

The three storage modes

With DirectQuery, nothing is imported: each visual sends queries to the source when it loads, and Power BI translates its DAX into the source's own language, such as SQL (Microsoft Learn: storage mode). Dual tables can answer from the cache or behave like DirectQuery, whichever the query needs.

When DirectQuery is justified

Microsoft lists the situations where DirectQuery is most beneficial:

• Data changes too often for import. Imported models on Pro can schedule 8 refreshes a day and on Premium or Premium Per User 48; if the business needs fresher numbers than that, DirectQuery or a hybrid approach may be needed.
• Data is too large to import, within the memory or refresh window available.
• Security must be enforced by the source, by passing each user's identity to the database.
• Regulations restrict keeping copies of the data outside the source.
• The source defines the measures, as some multidimensional systems do.

If none of these applies, import is almost always the better choice.

What DirectQuery costs

• Speed depends on the source. Every slicer click and cross-highlight sends queries. Microsoft suggests aiming for visuals that refresh in under five seconds; beyond thirty, usability suffers.
• A one-million-row limit. Any query or intermediate step that returns more than 1,000,000 rows fails, unless a Premium capacity raises the limit.
• A four-minute timeout per query in the Power BI service.
• Transformations must fold. Power Query steps have to translate into a single native query; steps that cannot fold cause errors.
• Fewer modelling features. There is no automatic date hierarchy, so an explicit date table is needed, calculated columns are limited to row-level expressions that fold, and time precision stops at seconds.
• Load on the source. Many users, many visuals and frequent tile refreshes all become database queries.
• Case sensitivity. Power BI's engine is case-insensitive; with a case-sensitive source, values differing only in case can produce undefined results unless casing is normalised.

Composite models: mixing modes

A model is composite when its tables use different storage modes, or when DirectQuery tables come from different sources (Microsoft Learn: composite models). Microsoft's example is typical: sales from an enterprise data warehouse, sales targets from a departmental database, and extra data imported from a spreadsheet, all in one report.

The most useful pattern is simple. Keep the large, fast-changing fact table in DirectQuery, and set the small dimension tables it shares with imported tables, such as Date, Product and Customer, to Dual. Dual dimensions answer slicers and filters from the cache, and avoid limited relationships with imported tables. The model design itself should still be a star schema.

Three traps in composite models

Cross-source relationships are limited. Relationships between tables from different sources are created as many-to-many by default, and even when changed to one-to-many they behave as limited relationships: the RELATED function cannot fetch values across them, and performance can suffer.

Data travels between sources. A query sent to one source can include values retrieved from another. In Microsoft's example, a visual showing sales by product manager, where managers come from a spreadsheet, sends a SQL query to the sales database containing the managers' names and their products. If one source holds confidential data, consider where it will be sent. Power BI Desktop shows a security warning when a composite model is created for this reason.

Visuals can multiply queries. Filtering one source by values from another can produce queries with long lists of literal values, queries at a detailed level aggregated afterwards, or one query per group. These are fine while the joining columns have a few thousand values, but they degrade as that number grows.

Alternatives to try first

Before committing to a fully DirectQuery model, Microsoft suggests evaluating options that keep most of import's speed:

• Incremental refresh, which refreshes only recent data; data refresh in Power BI explains it.
• Hybrid tables, which keep history imported and the latest period in DirectQuery.
• Aggregations, which answer common summary queries from a small imported table and send only detailed queries to the source; see aggregations for large datasets.
• Direct Lake, for data stored in Microsoft Fabric.

Design checklist for DirectQuery models

1. Make sure the source is fast: indexes on join and filter columns, integer keys, and columnstore indexes where available.
2. Keep Power Query steps simple and check that they fold; right-click a step and choose View Native Query.
3. Enable "Assume referential integrity" on relationships where the data guarantees it, so the source can use inner joins.
4. Limit bi-directional relationships to where they are needed.
5. Turn on query reduction options, such as an Apply button for slicers, and keep the number of visuals per page modest.
6. Test with realistic data volumes and concurrent users before publishing.

For connecting to a database in the first place, see connecting Power BI to SQL Server.

Data import and connectivity, refresh techniques and performance optimisation are part of the Power BI course.

Sources

• DirectQuery in Power BI: when to use, limitations, alternatives (Microsoft Learn) — Microsoft Learn
• Use composite models in Power BI Desktop (Microsoft Learn) — Microsoft Learn
• Table storage mode in Power BI semantic models (Microsoft Learn) — Microsoft Learn