Composite Models in Power BI: Mixing Import and DirectQuery

For years, Power BI practitioners had to choose: import mode (fast, but data is only as fresh as the last refresh) or DirectQuery (always current, but potentially slow and query-limited). Composite models changed this calculus by allowing both storage modes to coexist in a single model — with relationships that cross the mode boundary.

This capability unlocks scenarios that were previously impossible: a dashboard that shows yesterday's full transaction history from an import partition alongside today's real-time data from a DirectQuery source, all joined to a live Salesforce opportunity table queried on demand. Understanding how composite models work — and when they create more problems than they solve — is essential knowledge for any advanced Power BI practitioner.

Key Takeaways

• Composite models mix import, DirectQuery, and Dual storage modes within a single semantic model
• Import mode provides VertiPaq compression and in-memory query performance for historical data
• DirectQuery mode queries the source in real time — freshness is excellent, but performance depends on the source
• Dual mode tables can act as either import or DirectQuery depending on the query context
• Relationships crossing storage mode boundaries add query complexity and can cause performance issues
• Aggregation tables in composite models dramatically improve DirectQuery query performance
• DirectQuery for Power BI datasets (chaining) enables composite models built on top of shared semantic models
• Limited relationships between import and DirectQuery tables restrict certain DAX functions

---

Storage Modes: Import, DirectQuery, and Dual

Before understanding composite models, each storage mode must be understood individually.

Import mode loads data from the source system into Power BI's in-memory VertiPaq storage engine. Data is compressed (often 10:1 or better) and stored as columnar data that executes analytical queries extremely fast — typically milliseconds for most queries on datasets up to hundreds of millions of rows. The limitation: data is only as fresh as the last scheduled or manual refresh.

DirectQuery mode queries the source system in real time whenever a user interacts with a report. The Power BI engine translates DAX queries into native source queries (SQL for relational databases, etc.) and executes them against the source. Data is always current, but performance is entirely dependent on the source system's query performance. A well-indexed, dedicated analytical database will handle DirectQuery queries well; an OLTP production database under heavy transactional load may produce slow, inconsistent results.

Dual mode is a special hybrid available in composite models. A dual-mode table is physically stored as import (data loaded into VertiPaq) but can also be queried via DirectQuery when the query context requires it. This is primarily used for shared dimension tables that need to participate in relationships with both import and DirectQuery fact tables.

---

When to Use Composite Models

Composite models are appropriate for specific scenarios. They add complexity that isn't justified when simpler architectures meet the requirements.

Use composite models when:

Do not use composite models when:

• A full import model refreshes fast enough and data latency is acceptable (most cases)
• The DirectQuery source cannot handle the query load (production OLTP databases)
• Complex DAX calculations are needed — composite models limit certain DAX functions
• Row-level security needs to span the storage mode boundary (complex implementation)

---

Configuring Storage Modes

In Power BI Desktop, storage mode is set per table. Right-click a table in the Model view → Properties → Advanced → Storage mode.

For a typical composite model with a large fact table in DirectQuery and dimensions in Import:

1. Set FactSales → Storage mode: DirectQuery
2. Set DimDate → Storage mode: Dual (serves both import and DirectQuery queries)
3. Set DimProduct → Storage mode: Import (small table, fully cached)
4. Set DimCustomer → Storage mode: Dual (used in cross-source relationships)
5. Set RealtimeSales (today's data) → Storage mode: DirectQuery

When you configure a table as DirectQuery or change storage modes, Power BI displays warnings about relationships and potential limitations. Review these carefully — they indicate where model behavior may differ from a pure import model.

---

Relationships in Composite Models

Relationships between tables of different storage modes behave differently from same-mode relationships, and understanding these differences is critical for building models that produce correct results.

Regular relationships connect two tables where the "many" side can use the "one" side to filter. In import models, both tables are in memory and the relationship performs in memory — fast. In composite models with one import table and one DirectQuery table, the relationship causes a table scan of one table that's then used to filter the other — potentially generating large cross-mode queries.

Limited relationships are created automatically when a DirectQuery table has a many-to-many relationship with an import table, or in certain other cross-mode scenarios. Limited relationships don't support bi-directional filters and restrict certain DAX functions (for example, functions that rely on the relationship filter path). Power BI reports limited relationships in the model view with a dotted line instead of a solid line.

Cross-source relationships connect tables from completely different data sources (e.g., a table from SQL Server connected via DirectQuery and a table from Salesforce connected via another DirectQuery connection). These relationships require one side to be a Dual-mode table — Power BI needs to be able to materialize one side of the relationship in memory to join to the other.

The practical impact of these relationship types: DAX measures that work correctly in a pure import model may produce unexpected results or errors in a composite model. Test all measures carefully after changing storage modes, particularly those involving USERELATIONSHIP, CROSSFILTER, CALCULATE with relationship-related filter functions, and aggregations over related tables.

---

Aggregation Tables: The Core Composite Model Pattern

The most valuable composite model pattern combines a large DirectQuery fact table with an import-mode aggregation table that pre-summarizes the data at a higher grain.

The problem: A 500-million-row sales fact table in DirectQuery is too large for most source systems to query interactively — every chart takes 10+ seconds as the source executes expensive aggregate queries.

The solution: Pre-build a summary table that aggregates the fact to a daily/monthly/product-category grain and import that summary table into Power BI. Most queries (which are at monthly, quarterly, or category level) hit the fast import aggregation. Only queries that drill down to individual transaction level go back to DirectQuery.

Setting up aggregations:

First, create the aggregation table in your data warehouse:

CREATE TABLE SalesByDayProduct AS
SELECT
    SaleDateKey,
    ProductKey,
    CustomerSegmentKey,
    RegionKey,
    SUM(SalesAmount) as SalesAmount,
    SUM(Quantity) as Quantity,
    SUM(Cost) as Cost,
    COUNT(*) as TransactionCount
FROM FactSales
GROUP BY SaleDateKey, ProductKey, CustomerSegmentKey, RegionKey;

Import this table into Power BI and set storage mode to Import.

Then, configure the aggregation in Power BI:

• Right-click SalesByDayProduct → Manage aggregations
• Map each column to its relationship with the detail table and the summarization function (Sum, Average, Count, etc.)
• Set the "Summarization" column (SalesAmount → Sum maps to FactSales[SalesAmount] → Sum)

Power BI's query engine now automatically routes queries to the aggregation table when possible and falls back to the DirectQuery detail table only when the query requires row-level detail that the aggregation doesn't provide.

The performance result is dramatic: category-level and time-level aggregations that previously took 15 seconds now return in under 1 second, while the option to drill to individual transactions remains available.

---

DirectQuery for Power BI Datasets

Power BI introduced DirectQuery for Power BI Datasets (also called "live connection with composite models" or simply "composite models on shared datasets"). This allows a developer to create a new report or dataset that uses an existing published Power BI dataset as a DirectQuery source — while adding new tables, calculated measures, and local import data.

Key use case: An organization has a certified enterprise semantic model covering core finance and sales data. A team working on a specific analysis needs to add some local data (a CSV file with project codes, an Excel file with targets) without modifying the certified enterprise model. Using DirectQuery for Power BI Datasets, they create a composite model that references the enterprise model via DirectQuery and adds their local tables as import data.

This enables a governed analytics architecture where:

• The central data team maintains certified enterprise datasets
• Business teams extend these datasets with local context without creating separate, inconsistent models
• The enterprise model remains the single source of truth for shared metrics

Limitations: DirectQuery for Power BI Datasets inherits all the limitations of regular DirectQuery — some DAX functions are restricted, row-level security must be properly configured to propagate through the composite model, and the connection to the source dataset adds a layer of query processing.

---

Performance Optimization for Composite Models

Composite models require more careful performance tuning than pure import models. The following optimizations are most impactful:

Reduce cross-mode queries: Every relationship traversal that crosses a storage mode boundary adds latency. Minimize these by keeping dimension tables as Dual mode (they can serve both import and DirectQuery queries without a cross-mode traversal) and by structuring the model so most queries stay within a single mode.

Pre-aggregate at the source: Don't ask the DirectQuery source to do aggregations that Power BI could do more efficiently. Build views or materialized views in the source database that pre-aggregate at the grain your reports actually need.

Monitor query plan with Performance Analyzer: In Power BI Desktop, View → Performance Analyzer records the time taken for each visual's DAX query and the subsequent source query (if DirectQuery). This reveals which visuals are slow and whether the slow query is in the DAX layer or the source query layer.

Use query reduction settings: In Power BI Desktop → Options → Query reduction, enable options to add Apply buttons to slicers and filters. This prevents every slicer interaction from immediately firing a source query — particularly important for DirectQuery reports where each query has network and source execution latency.

Limit the number of DirectQuery connections: Each different DirectQuery source in a composite model creates a separate connection pool. Limit to 1–2 DirectQuery sources where possible; more than 3 significantly increases model complexity and potential performance issues.

---

Row-Level Security in Composite Models

Row-level security (RLS) in composite models requires careful planning, particularly when RLS is defined on an import table that has a relationship with a DirectQuery table.

When a user with an RLS filter queries a report, Power BI applies the filter to the appropriate table. If the filtered table is in import mode and it has a relationship to a DirectQuery table, Power BI must translate the import filter into a filter that can be sent to the DirectQuery source. This works in most cases but can produce unexpected results with complex filter hierarchies.

Best practice: Define RLS on the import-mode dimension tables (not the DirectQuery fact tables). The filter propagates from dimension to fact through the relationship — which works reliably. Defining RLS directly on DirectQuery tables is possible but harder to test and debug.

For composite models using DirectQuery for Power BI Datasets, RLS defined in the source dataset is automatically applied when that dataset is queried. Additional RLS can be defined in the composite model layer. This layered RLS approach requires careful testing to ensure filters compound correctly.

---

Frequently Asked Questions

Can I mix data from completely different database platforms in a composite model?

Yes. A composite model can contain tables from SQL Server (DirectQuery), Salesforce (DirectQuery), an Azure Blob Storage file (Import), and Snowflake (DirectQuery) simultaneously. Each source maintains its own connection. Relationships between tables from different sources must have at least one Dual-mode table to facilitate cross-source joins. Performance and complexity increase with each additional source — limiting to 2–3 sources is practical for most implementations.

What DAX functions don't work in composite models?

Some DAX functions are restricted or behave differently in composite models with DirectQuery tables. Functions that don't work with limited relationships include SUMMARIZE (in certain contexts), TOPN (on DirectQuery tables), and some time intelligence functions. USERELATIONSHIP works but can cause cross-mode queries. The full list of limitations is documented in Microsoft's Power BI documentation under "DirectQuery limitations." Testing all critical measures after adding DirectQuery tables is strongly recommended.

How does incremental refresh work with composite models?

Incremental refresh applies to import-mode partitions within a composite model. Tables configured as DirectQuery don't use incremental refresh — they query the source in real time on every interaction. The most common combination is using incremental refresh on import-mode historical partitions while having DirectQuery for the current-period data — this is the hybrid tables feature, which is a specific form of composite modeling within a single table.

What is the performance impact of composite models vs. pure import?

Composite models with DirectQuery components will always be slower than equivalent pure import models for equivalent queries. The performance gap depends on source system performance and the proportion of queries that hit DirectQuery vs. import partitions. With well-designed aggregation tables, most user queries hit the import aggregation and return in under 1 second, making the performance acceptable. Queries that drill to DirectQuery detail may take 3–15 seconds depending on source performance.

Should I use composite models or just schedule more frequent refreshes?

More frequent refreshes (every 15 minutes for import mode) are sufficient for most use cases where "near real-time" is acceptable. Composite models with DirectQuery add significant complexity — use them only when: (a) you need data that is genuinely current to the minute or second, (b) the dataset is too large to refresh within the available window even with incremental refresh, or (c) you need to combine data from sources that can't be consolidated into a single warehouse refresh.

---

Next Steps

Composite models are a powerful tool for sophisticated Power BI architectures — but they require careful design to avoid performance and correctness pitfalls. The most successful implementations use composite models for specific, justified scenarios rather than as a default architecture.

ECOSIRE's Power BI data modeling services include composite model design, aggregation table implementation, and performance optimization. Contact us to evaluate whether composite models are the right solution for your specific data freshness and performance requirements.