Launch Deal · 50% off 1st month ·

3.5 — Large Semantic Models, Composite Models and Aggregations

DP-600 Domain 3 · Skill 3.5

What You Need to Know

Enterprise models outgrow default limits. You can raise the limits with the large semantic model storage format, or keep the detail at the source with composite models and aggregations.

Large semantic model storage format

Fact Detail
Default Import models are capped at 1 GB in the service unless large format is on
With large format The limit is set by the capacity, or by a lower maximum the capacity admin sets
Required for Models that grow beyond 10 GB (Desktop uploads are still limited to 10 GB, so the model grows in the service through refresh, usually incremental refresh)
Also helps XMLA write performance, which is why it's recommended for models managed with Tabular Editor or scripts. OneLake integration for semantic models also uses it
Enable it In the semantic model's settings, or as the workspace default storage format for new models
Licensing Fabric F SKUs, Premium P SKUs and PPU. Not Pro-only workspaces

Composite models

A composite model mixes storage modes, or connects to other semantic models:

  • Import + DirectQuery in one model: for example, a DirectQuery fact for real-time detail, with Import dimensions.
  • DirectQuery for Power BI semantic models and Analysis Services: build a local model on top of a published (certified) model and add your own tables or measures. Users need Build on the source model.
  • Dual storage mode for shared dimensions, so that a dimension can join to both Import and DirectQuery tables efficiently, without limited relationships.

Aggregations

User-defined aggregations put a small Import summary table in front of a large DirectQuery fact table:

  1. Create an aggregation table, for example sales by Date, Product Category and Store, summed and counted.
  2. Set the aggregation table to Import (hidden), the detail fact to DirectQuery, and the dimensions to Dual.
  3. In Manage aggregations, map each aggregation column to its detail column and summarization (Sum, Count, GroupBy).
  4. Queries at or above the aggregation's grain hit the fast in-memory table. Drill-downs below it go to the source.

Automatic aggregations (Premium and Fabric) can create and train aggregations from query history for DirectQuery models.

Which approach?

Situation Approach
A 40 GB Import model on F128 Large format plus incremental refresh
5 billion detail rows that must stay in the source, and dashboards that mostly show daily totals DirectQuery fact plus Import aggregations and Dual dimensions
A department wants to extend the certified enterprise model with its own budget table Composite model on the published model
The data is already Delta tables in Fabric Often simpler: Direct Lake (lesson 3.1)

Key Concept: Large format lifts the size cap (needed above 10 GB, and good for XMLA). Aggregations answer summary queries from memory and send detail queries to DirectQuery. Composite models mix modes, or extend shared models without copying them.

Exam Traps

  • Trying to publish a 15 GB .pbix from Desktop (the upload limit is 10 GB; grow the model in the service)
  • Setting dimensions to Import instead of Dual when they relate to both an Import aggregation and a DirectQuery fact
  • Building aggregations at a finer grain than the visuals use (they won't help)
  • Copying a certified model to add one table, instead of building a composite model on it

Check your understanding

Practice Scenario

🔒 This practice scenario is part of Skillmarked Pro

The lessons stay free. Pro unlocks the other 17 DP-600 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.

See plans

Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.

Cancel anytime. Or €199 a year, which saves 17%.

Not sure yet? Try 10 free mock exam questions.

Already Pro? Log in.