Compare Import, DirectQuery, and Dual, connect to SQL Server, add aggregation tables, tune relationships, and improve performance while avoiding common pitfalls.
In Composite Models in Power BI, Greg Trzeciak shows you how to combine multiple data sources and storage modes to build flexible, high-performing Power BI solutions. You’ll learn what composite models are, why they matter, and how they differ from traditional single-source models. This course breaks down the key concepts behind Import, DirectQuery, and Dual storage modes so you can choose the right approach based on speed, freshness, and maintainability.
You’ll also build a composite model step by step, connecting to SQL Server, reviewing model performance, and introducing aggregation tables to improve query speed. Greg walks through relationship tuning, expanding the model safely, and where DAX fits into composite scenarios. By the end, you’ll understand how to design composite models that balance performance and real-time needs, while avoiding common pitfalls like broken query folding and unexpected relationship behavior.
Course Outline ( Free Preview)
Module 01 - Intro to Power BI Composite Models
Greg introduces the goal of composite models and why they’re a major step forward for flexible Power BI design. You’ll get a preview of what you’ll build, the performance and real-time tradeoffs you’ll manage, and how each module fits into the final solution. This module sets expectations and gives you the “why” before you start configuring anything.
Module 02 - What are Composite Models
Learn what a composite model is in plain terms, a Power BI model that blends multiple sources or storage modes into one report experience. Greg explains common scenarios where composite models shine, like mixing real-time data with cached history or extending a shared semantic model. You’ll also learn the key limitations to keep in mind so you don’t overcomplicate your model.
Module 03 - DirectQuery, Import, and Dual7 min.
This module breaks down the three storage modes that drive composite behavior. Greg explains what each mode is best at, when it hurts performance, and how it impacts refresh, interactivity, and data freshness. You’ll leave with a decision framework for choosing the right mode per table instead of guessing.
Module 04 - Connecting to SQL Server9 min.
Get hands-on by connecting Power BI to SQL Server as a foundational source for your composite model. Greg walks through connection options and what to consider for authentication, performance, and schema design. This module sets up the real-world environment you’ll use for the remaining steps.
Module 05 - Model Review and Performance Analyzer13 min.
Before you optimize, you need to measure. Greg shows how to review your model structure and use Performance Analyzer to understand what visuals and queries are actually doing. You’ll learn how to spot slow visuals, identify expensive query patterns, and build a baseline so changes can be validated objectively.
Module 06 - Creating the Aggregate Table16 min.
Learn how aggregation tables improve performance by pre-summarizing data at the right grain. Greg walks through building an aggregate table that supports common query paths while reducing load on detailed fact tables. You’ll learn what to aggregate, what columns matter, and how to design tables that Power BI can leverage automatically.
Module 07 - Placing Aggregation Tables into the Composite Model13 min.
Add your aggregation table into the model and configure it so Power BI can route queries to it when appropriate. Greg explains how to align columns, keys, and relationships so aggregation behavior works as intended. This module focuses on turning a good idea into a functioning performance feature.
Module 08 - Adjusting Relationships in the Composite Model16 min.
Relationships behave differently when multiple storage modes are involved. Greg shows how to adjust relationships to avoid ambiguity, unexpected filter propagation, and performance issues. You’ll learn practical rules for relationship direction, cardinality, and how to validate that filters flow correctly.
Module 09 - Expanding the Composite Model15 min.
Now that the core is working, you’ll learn how to expand the model safely. Greg covers how to add tables or extend scope without breaking performance, creating confusion, or introducing conflicting relationships. The focus is on scalable modeling decisions that keep composite designs maintainable.
Module 10 - DAX and Composite Models3 min.
DAX can behave differently depending on storage mode and query behavior. Greg shows how to think about measures in composite scenarios, what to test, and how to avoid surprises when calculations hit DirectQuery sources. You’ll learn how to write DAX that remains accurate and performant across composite patterns.
Module 11 - A Note on Query Folding4 min.
Query folding is critical for performance, especially when DirectQuery or hybrid designs are involved. Greg explains what folding is, why it breaks, and how to recognize when Power BI is pushing work back to the source versus doing it locally. You’ll leave with practical tips for keeping transformations foldable and predictable.
Module 12 - Conclusion2 min.
Wrap up with a clear recap of what you built and the decisions that mattered most. Greg summarizes composite model strategy, storage mode selection, aggregation design, and relationship tuning.
Gregory Trzeciak has his master’s degree in Education from the University of Florida. He has 9 years of teaching experience in high school, college level, and summer programs where he was recognized as a top educator and leader in interactive education. As a trainer at Pragmatic Works, his primary goal is to help individuals gain confidence in using Power BI and the Power Platform. While not in the office, he enjoys fantasy football, walking his dog, and running half-marathons!
