Composite models in Power BI - Deep Dive

Overview - Composite models

What is it?

Composite models in Power BI let you combine data from different sources into one report. You can mix imported data with direct queries to live databases. This helps you work with large datasets without loading everything into memory. It makes your reports faster and more flexible.

Why it matters

Without composite models, you would have to choose between importing all data or querying live, which limits performance or flexibility. Composite models solve this by letting you pick the best method for each data source. This means better insights faster, even with big or complex data. It helps businesses make smarter decisions without waiting.

Where it fits

Before learning composite models, you should understand basic Power BI data loading methods: import and direct query. After this, you can explore advanced modeling techniques like aggregations and incremental refresh. Composite models are a bridge between simple data loading and complex performance tuning.

Mental Model

Core Idea

Composite models let you mix imported and live data sources in one Power BI report to balance speed and freshness.

Think of it like...

It's like cooking a meal where some ingredients are pre-prepared and stored in the fridge (imported data), while others you pick fresh from the market just before cooking (direct query). You get the best of both worlds: convenience and freshness.

┌─────────────────────────────┐
│       Power BI Report       │
├─────────────┬───────────────┤
│ Imported    │ Direct Query  │
│ Data Source │ Data Source   │
│ (Stored)    │ (Live Access) │
└─────────────┴───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding data import mode

Concept: Learn how Power BI imports data into its own storage for fast access.

When you import data, Power BI copies it into its internal storage. This makes reports fast because data is local. But large datasets can slow down refresh and use more memory.

Result

You get quick report interactions but may face delays when refreshing large data.

Knowing import mode helps you see why sometimes data refresh is slow and memory use is high.

2

Foundation

Understanding direct query mode

Concept: Learn how Power BI queries data live from the source without storing it.

Direct query mode sends queries to the original database each time you interact with the report. This keeps data fresh but can slow down report response if the source is slow.

Result

Reports always show current data but may respond slower depending on the source.

Understanding direct query shows the trade-off between data freshness and speed.

3

Intermediate

What composite models combine

🤔Before reading on: do you think composite models only combine imported data or can they mix imported and live data? Commit to your answer.

Concept: Composite models allow mixing imported and direct query data sources in one report.

With composite models, you can have some tables imported for speed and others connected live for freshness. This lets you optimize performance and data accuracy together.

Result

Reports can be faster and fresher by combining the best of both data loading methods.

Knowing composite models combine modes helps you design smarter reports that balance speed and freshness.

4

Intermediate

Relationships in composite models

🤔Before reading on: do you think relationships between imported and direct query tables behave the same as between imported tables? Commit to your answer.

Concept: Relationships can connect imported and direct query tables but have some limitations.

You can create relationships between tables regardless of their storage mode. However, some relationship types or cross-filter directions may be restricted to keep queries efficient.

Result

You can model data across sources but must design relationships carefully to avoid errors or slow queries.

Understanding relationship limits prevents common modeling mistakes that cause report errors or slowdowns.

5

Intermediate

Using aggregations with composite models

Concept: Aggregations summarize large data in imported tables to speed up queries on direct query sources.

You can create small imported tables with aggregated data that Power BI uses automatically instead of querying large direct query tables. This speeds up reports while keeping detailed data live.

Result

Reports respond faster by using pre-aggregated data when possible and detailed live data when needed.

Knowing how aggregations work with composite models helps optimize report performance on big data.

6

Advanced

Storage mode settings and performance

🤔Before reading on: do you think changing a table's storage mode affects report refresh and query speed? Commit to your answer.

Concept: Each table's storage mode (import, direct query, or dual) affects how Power BI handles data refresh and queries.

Import mode tables refresh by loading data fully. Direct query tables send live queries. Dual mode tables act as import for filtering but direct query for detail. Choosing modes impacts refresh time and interactivity.

Result

You can tune report speed and freshness by setting storage modes per table.

Understanding storage modes lets you balance refresh speed and query performance precisely.

7

Expert

Advanced query folding and composite models

🤔Before reading on: do you think query folding works the same for composite models as for pure direct query? Commit to your answer.

Concept: Query folding pushes data transformations back to the source database to improve performance, but composite models add complexity.

In composite models, query folding happens only on direct query tables. Imported tables do not fold queries. Mixing modes means some transformations run locally, others remotely, affecting performance.

Result

You must design transformations carefully to maximize query folding benefits in composite models.

Knowing how query folding interacts with composite models helps avoid slow queries and inefficient data processing.

Under the Hood

Power BI manages composite models by storing imported tables in its in-memory engine and sending live queries for direct query tables. It coordinates relationships and filters across these modes by translating user interactions into a mix of local and remote queries. The engine decides which data to fetch from memory and which to request from the source, merging results seamlessly for the report.

Why designed this way?

Composite models were created to overcome the limitations of pure import or direct query modes. Import mode is fast but limited by memory and refresh time. Direct query is always fresh but can be slow. Combining them lets users optimize for both speed and freshness. This design balances flexibility, performance, and data accuracy.

┌───────────────┐       ┌───────────────┐
│ Imported Data │       │ Direct Query  │
│   (In-Memory) │       │   Data Source │
└──────┬────────┘       └──────┬────────┘
       │                       │
       │                       │
       │      ┌────────────────┴───────────────┐
       │      │      Power BI Composite Model   │
       │      │  Coordinates queries & filters │
       └─────▶│  Merges data for report visuals │
              └─────────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think composite models always improve report speed? Commit to yes or no.

Common Belief: Composite models always make reports faster because they mix import and direct query.

Reality: Composite models can improve speed but may slow reports if relationships or queries are complex or if query folding is lost.

Why it matters: Assuming composite models always speed up reports can lead to poor design choices that cause slow or unstable reports.

Quick: Can you create any relationship type between imported and direct query tables? Commit to yes or no.

Common Belief: All relationship types work the same between imported and direct query tables.

Reality: Some relationship types or cross-filter directions are restricted between mixed storage mode tables to maintain query performance.

Why it matters: Ignoring these limits causes errors or unexpected filtering behavior in reports.

Quick: Does query folding apply to imported tables in composite models? Commit to yes or no.

Common Belief: Query folding works on all tables in composite models, including imported ones.

Reality: Query folding only applies to direct query tables; imported tables do not fold queries because data is already loaded.

Why it matters: Misunderstanding this can lead to inefficient transformations and slow report refresh.

Quick: Is dual storage mode just a synonym for composite models? Commit to yes or no.

Common Belief: Dual mode is the same as composite models.

Reality: Dual mode is a specific storage mode within composite models that allows a table to act as both import and direct query depending on context.

Why it matters: Confusing these concepts can cause incorrect storage mode settings and performance issues.

Expert Zone

1

Dual storage mode tables can improve performance by acting as import for filtering and direct query for detail, but require careful design to avoid ambiguous query plans.

2

Composite models can cause complex query plans that are hard to debug; using Performance Analyzer and SQL Profiler helps identify bottlenecks.

3

Some advanced features like incremental refresh work differently or have limitations with composite models, requiring special configuration.

When NOT to use

Composite models are not ideal when all data fits comfortably in memory and freshness is not critical; pure import mode is simpler and faster then. Also, if the direct query source is very slow or unreliable, composite models may degrade user experience. Alternatives include full import with incremental refresh or using aggregations without direct query.

Production Patterns

In real-world reports, composite models are used to combine large historical data imported for speed with live transactional data queried directly. Experts often use dual mode tables for lookup dimensions to speed filtering. Aggregations are layered on top of direct query tables to optimize performance. Monitoring query folding and tuning relationships are standard practices.

Connections

Data virtualization

Composite models build on the idea of data virtualization by combining live and stored data sources.

Understanding data virtualization helps grasp how composite models provide a unified view without moving all data.

Database indexing

Composite models rely on efficient database indexing to speed up direct query performance.

Knowing indexing principles helps optimize the live data sources composite models query.

Cooking meal prep

Both composite models and meal prep balance pre-prepared and fresh ingredients for best results.

This cross-domain insight shows how mixing preparation methods optimizes time and quality.

Common Pitfalls

#1 Trying to create a bidirectional relationship between imported and direct query tables without restrictions.

Wrong approach:

Create relationship with Cross filter direction = Both between imported and direct query tables.

Correct approach:

Set Cross filter direction = Single when relating imported and direct query tables to avoid errors.

Root cause:

Misunderstanding relationship limitations in composite models causes invalid or slow queries.

#2 Applying complex transformations on imported tables expecting query folding benefits.

Wrong approach:

Use Power Query steps on imported tables expecting them to push transformations to source.

Correct approach:

Apply heavy transformations on direct query tables to enable query folding; keep imported tables simple.

Root cause:

Confusing query folding applicability leads to inefficient data processing.

#3 Setting all tables to direct query mode to keep data fresh without considering performance.

Wrong approach:

Set storage mode = DirectQuery for all tables regardless of size or usage.

Correct approach:

Import large, stable tables and use direct query only for frequently changing data to balance speed and freshness.

Root cause:

Not balancing storage modes causes slow reports and poor user experience.

Key Takeaways

Composite models let you mix imported and direct query data sources in one Power BI report to balance speed and data freshness.

Understanding storage modes and their impact on refresh and query performance is key to designing efficient composite models.

Relationships between mixed storage mode tables have limitations that must be respected to avoid errors and slowdowns.

Query folding only applies to direct query tables, so transformations on imported tables do not push to the source.

Expert use of composite models involves tuning storage modes, using aggregations, and monitoring query performance carefully.