Designing and Building Semantic Models

Introduction

This lesson opens the domain Implement and manage semantic models (25–30% of the DP-600 skills measured). It covers the group "Design and build semantic models": choose a storage mode, implement a star schema for a semantic model, implement relationships such as bridge tables and many-to-many relationships, write calculations (the next lesson goes deep), configure the large semantic model storage format, and design and build composite models. This course targets the English syllabus effective October 19, 2026. Microsoft does not publish the question count or the question-type mix, so OnLevelUp mock exams use a 50-question practice length and an unofficial 80% practice target, which is not equivalent to the official 700/1000 scaled passing score.

A semantic model (formerly called a dataset) is the layer between your data and your reports. It holds tables, relationships, measures, hierarchies and security roles, and it is the single place where business logic such as "net revenue" or "active customer" is defined once and reused by every report, Excel pivot table and notebook. After this lesson you will be able to pick the right storage mode for a scenario, shape tables into a star schema, resolve many-to-many requirements with a bridge table, decide when to enable the large storage format, and combine sources in a composite model.

Learning objectives

• Compare Import, DirectQuery, Dual and Direct Lake storage modes and choose one from business constraints.
• Design a star schema (facts, dimensions, keys, grain) for a semantic model and explain why it beats a flat or snowflaked layout.
• Configure relationships: cardinality, cross-filter direction, active and inactive relationships, bridge tables and many-to-many.
• Explain when the large semantic model storage format is needed and what it changes.
• Build a composite model and understand aggregations, Dual tables and the limitations of mixing storage modes.

1. What a semantic model contains

A semantic model in Fabric is a tabular (VertiPaq / Analysis Services) model. Its main parts:

You can author a model in Power BI Desktop, in the web editor of a lakehouse or warehouse, or with external tools that use the XMLA endpoint (Tabular Editor, SSMS). Fabric also lets you save a model as a Power BI Desktop project (.pbip) for source control, as seen in lesson 2.

2. Storage modes

The storage mode of a table decides where the data lives at query time. This is the most tested topic of this lesson, and the exam always gives constraints (freshness, size, source, security, capacity) from which you derive the mode.

2.1 Import

Import is the default and usually the right answer when the data fits in memory, a refresh schedule is acceptable and you need the richest modeling features. Compression of VertiPaq is typically strong (a column with few distinct values compresses extremely well), so tens of millions of rows often fit in a few hundred megabytes.

Example. A retailer's sales mart has 180 million rows in a warehouse and refreshes nightly. Reports must answer in under two seconds, and users accept data that is a day old. Import (or Direct Lake, if the mart is already Delta in OneLake) fits. DirectQuery would send every click to the warehouse.

2.2 DirectQuery

Use DirectQuery when the data must be current to the second, when the data is too large or too regulated to copy, or when the source enforces its own security per user. Every visual generates one or more queries, so performance is the source's performance. Typical constraints:

• Power Query transformations must be foldable to the source.
• Some DAX functions (certain time-intelligence patterns, some iterators) can be slow or unsupported.
• Many concurrent users multiply the load on the source.

2.3 Dual

A Dual table behaves as Import when queried together with Import tables and as DirectQuery when queried together with DirectQuery tables. You set it on dimension tables in a composite model so that a slicer on the dimension does not force a cross-source join evaluated by the engine on millions of key values. Dual is not something you choose for a fact table.

2.4 Direct Lake

Direct Lake reads Delta (Parquet) files in OneLake directly into the VertiPaq engine. There is no import copy and no scheduled refresh in the classical sense: a refresh of a Direct Lake model is a framing operation, which only updates metadata so the model points at the current Delta table versions. Columns are then loaded (transcoded) into memory when a query first needs them and evicted under memory pressure.

Two flavors exist and the exam expects you to distinguish them (the next lesson covers fallback in depth):

Direct Lake suits large Delta tables already produced by pipelines and notebooks, where you want Import-like speed without a duplicate copy and without waiting for an import to finish. It is not the answer when the data is not in OneLake as Delta, for example a SaaS source you have not ingested.

2.5 Worked example: choosing a storage mode

Scenario. A logistics company streams parcel-scan events into an eventhouse and also keeps a 3 TB history of shipments as Delta tables in a lakehouse. Dispatchers want a dashboard that is at most one minute behind for the current day, plus analysts want fast history analysis by region and carrier. Headcount for modeling is small and the capacity is F64.

Reasoning, step by step.

1. The history lives as Delta tables in OneLake and is large: Direct Lake avoids copying 3 TB into an import model and avoids long refreshes.
2. The "last minute" requirement is a streaming need. A semantic model is not the right engine for sub-minute streaming; a Real-Time Dashboard on the eventhouse (KQL) covers it.
3. Analysts need history, region and carrier slicing, DAX measures and security: a semantic model in Direct Lake mode on the shipment tables, with small dimension tables from the same lakehouse.
4. Verify the model stays within the capacity guardrails (the maximum rows per table and memory limits depend on the SKU); if a query exceeds a guardrail on the SQL-endpoint flavor, it may fall back to DirectQuery.

Solution. Real-Time Dashboard on the eventhouse for the live view, plus a Direct Lake semantic model for historical analysis.

Why the other choices are weaker. Import of 3 TB is impractical and slow to refresh. DirectQuery on the lakehouse endpoint would send each click to the SQL endpoint and add latency. Putting the stream into a Direct Lake model does not make it real time, because new Delta versions only become visible after a framing operation.

Exam tip. Keywords: "data must be current at all times" and "do not copy data" point to DirectQuery; "Delta tables in OneLake, large, fast, no scheduled import" points to Direct Lake; "needs full DAX/Power Query features, data fits memory, refresh acceptable" points to Import.

3. Star schema for a semantic model

Lesson 4 built star schemas in a lakehouse or warehouse. The semantic model benefits from the same shape because the VertiPaq engine and the DAX filter propagation are designed around it.

A star schema has:

• Fact tables: one row per event at a defined grain (for example one row per order line). They hold foreign keys to dimensions and numeric measures (quantity, amount).
• Dimension tables: one row per business entity (customer, product, date). They hold descriptive attributes used to filter and group.
• Relationships from dimension (one side) to fact (many side), single direction from the dimension to the fact.

Why it works:

Example. A flat export has 40 columns per order line, including customer name, city, product name and category repeated on every row. Importing it as is produces a large model with high-cardinality text columns. Splitting it into FactSales (OrderLineKey, DateKey, CustomerKey, ProductKey, Quantity, NetAmount) and DimCustomer, DimProduct, DimDate reduces memory and gives every slicer one clear home.

3.1 Snowflake versus star

A snowflake normalizes dimensions further (Product > Subcategory > Category as three tables). In a semantic model this lengthens filter paths and complicates the field list. The usual advice is to denormalize into one product dimension in the lakehouse, warehouse or Power Query step. A small snowflake is not an error, but the exam prefers the star.

3.2 Date table

Time intelligence requires a proper date dimension: a contiguous list of dates without gaps covering every date in the facts, marked as the date table, with a unique date column. Disable or avoid the automatic date/time hierarchies when you have your own date table, because the hidden tables add size and ambiguity.

3.3 Role-playing dimensions

A fact table often has several dates (OrderDate, ShipDate, DeliveryDate). You create one active relationship and the others inactive, then activate an inactive relationship inside a measure with USERELATIONSHIP. Alternatively duplicate the date table. Only one active relationship can exist between two tables.

Shipped Amount =
CALCULATE ( [Total Net Amount], USERELATIONSHIP ( FactSales[ShipDateKey], DimDate[DateKey] ) )

4. Relationships

A relationship has three properties that matter for the exam.

4.1 Cross-filter direction

With single direction, selecting a customer filters sales, but selecting sales rows does not filter customers. Both (bidirectional) lets the filter travel the other way. It is tempting but can create ambiguous paths, slower queries and unexpected totals. Prefer single direction and solve specific needs in measures with CROSSFILTER or TREATAS, or with a bridge table designed for the purpose.

4.2 Many-to-many and bridge tables

Real requirements sometimes have many-to-many associations: a customer belongs to several segments, a student takes several courses, a product is sold under several promotions. There are two ways to model them.

Native many-to-many relationship. Power BI lets you define a many-to-many relationship directly between two tables on non-unique columns. It is convenient, but the engine must resolve the relationship at query time, and it behaves with weaker guarantees: blank rows, no referential integrity and totals that can surprise you. Use it for simple cases, such as relating a fact table to a small lookup that is not unique.

Bridge table (recommended). A bridge (junction) table contains the unique combinations of the two keys. The model then has two one-to-many relationships from the dimensions to the bridge, and the bridge propagates filters to the fact table.

DimSegment (SegmentKey) 1 --> * BridgeCustomerSegment (SegmentKey, CustomerKey) * <-- 1 DimCustomer (CustomerKey) 1 --> * FactSales

For a segment to filter sales through the bridge, the filter must travel from DimSegment to the bridge, then from the bridge to DimCustomer, which is "many to one" and therefore requires bidirectional filtering on that relationship (or a measure using CROSSFILTER). That is the one accepted use of bidirectional filtering, and you limit it to that relationship.

Worked example: customers in several segments.

Statement. A marketing team tags customers with segments (Gold, Newsletter, At-risk). A customer may have several segments and a segment has many customers. Executives want net revenue by segment and accept that a customer's revenue appears in each of their segments.

Reasoning.

1. A single segment column on DimCustomer cannot hold several values, and duplicating customer rows would break the one-row-per-customer grain of the dimension.
2. Create BridgeCustomerSegment with one row per customer-segment pair.
3. Relate DimSegment[SegmentKey] one-to-many to the bridge; relate DimCustomer[CustomerKey] one-to-many to the bridge, with the cross-filter direction from the bridge to the customer set to both; DimCustomer keeps its single-direction one-to-many to FactSales.
4. A slicer on DimSegment[SegmentName] filters the bridge, the bridge filters the customers, the customers filter the sales.
5. Totals: a grand total across segments counts a multi-segment customer once, while a table broken down by segment counts them once per segment. This is the expected non-additive behavior and should be documented.

Solution. Bridge table with limited bidirectional filtering.

Why not the alternatives. A comma-separated segment column cannot be sliced cleanly. A native many-to-many between DimSegment and FactSales is impossible because the two have no shared column. Bidirectional filtering everywhere would introduce ambiguity.

4.3 Referential integrity and blanks

If the fact table contains a key with no matching dimension row, Power BI shows a blank member in the dimension. It means data quality is off: fix it upstream (add an "Unknown" member with key -1) rather than ignoring it. In DirectQuery, the setting "Assume referential integrity" removes the blank-row check and lets the engine use inner joins, which is faster but wrong if integrity is not real.

5. Large semantic model storage format

Import and Direct Lake models in Fabric capacities and Premium capacities can use the large semantic model storage format. By default a model is limited by the memory limit of the SKU for the refresh operation, and the default storage format has an approximate size limit for published models. The large format removes that limitation, so the model can grow up to the capacity's memory limit.

What it does and what you should remember:

You also need it for models published through the XMLA endpoint that exceed the default size, and it is a prerequisite for scale-out capabilities in some scenarios. Enabling it for a tiny model is harmless but gives no benefit.

Example. An insurer's claim model grows from 6 GB to 14 GB after the history is extended. The nightly refresh fails with a size error on an F64 capacity. The fix is to enable the large storage format, partition the claim fact table by month and refresh only the recent partitions.

Exam tip. "Model exceeds the default size limit" and "XMLA read/write for very large models" point to the large storage format. A table in Direct Lake does not copy data into the model, so size is governed instead by the capacity guardrails.

6. Aggregations and user-defined aggregation tables

An aggregation table stores pre-summarized data (for example sales by date and product category) in Import mode while the detailed fact stays in DirectQuery. Power BI automatically routes a query to the aggregation when it can answer it, and falls back to the detail table otherwise. You configure the mapping with the "Manage aggregations" dialog. The aggregation table is Import, the dimensions it relates to are typically Dual, and the detail table is DirectQuery. This is the standard pattern for a DirectQuery model that must be fast on summary visuals.

7. Composite models

A composite model combines more than one storage mode or more than one source in a single semantic model. For example, a model can have an Import table for a Budget Excel file, a DirectQuery fact table from a warehouse, and Dual dimensions. A second form of composite model is created when a report author connects to a shared semantic model and adds local tables or measures on top (a "DirectQuery for semantic models" chain).

7.1 Table-level storage modes

In Power BI Desktop's model view you set the storage mode per table. The relationship between two tables with different storage modes is a limited relationship (also called weak): the engine cannot guarantee one-to-many integrity and cannot push the join to a single source, so it evaluates the join in the model. Limited relationships do not push RELATED evaluation, they never produce a blank row for unmatched keys in the same way, and they cannot enforce the same filter semantics. Dual tables are the way to avoid them for dimensions.

7.2 Direct Lake and composite models

• Direct Lake on OneLake tables can coexist with Import tables in one model, which is the way to add a small Excel budget to a Direct Lake model.
• Direct Lake on SQL analytics endpoint tables cannot be mixed with DirectQuery or Dual tables in the same model.
• Calculation groups, field parameters and what-if parameters create calculated tables implicitly, and are allowed in all Direct Lake scenarios.

7.3 Worked example: composite model with a budget

Statement. Sales actuals are in a Direct Lake model on OneLake. Finance maintains a budget in Excel by month and product category, and wants a variance report without asking engineering to load the Excel file in the lakehouse.

Reasoning.

1. Direct Lake on OneLake allows Import tables, so Finance can add Budget as an Import table in the same model.
2. Relate Budget to the existing DimDate and DimProduct. The relationships between an Import table and Direct Lake tables are regular as long as the model is Direct Lake on OneLake.
3. Add a measure Variance = [Actual] - [Budget Amount].
4. If the model had been Direct Lake on SQL analytics endpoint, mixing with a DirectQuery table would not have been possible, and Import would be the way to add an Excel table only if the feature is supported for that flavor; the clean alternative is to ingest the Excel file into a Delta table with a dataflow.

Solution. Import table for the budget in a Direct Lake on OneLake model.

Why the alternatives are worse. Loading the budget through email and Excel copies creates ungoverned versions. Switching the whole model to Import loses the benefit of Direct Lake on the large fact table.

Common pitfalls

• Choosing DirectQuery "to be safe" for freshness when users only look at yesterday's data: you pay performance for no benefit.
• Bidirectional filtering on every relationship; use it only on a bridge relationship that needs it.
• Leaving the automatic date/time tables enabled next to a custom date table.
• Using a many-to-many relationship when a bridge table would give clear semantics.
• Adding calculated columns to a Direct Lake table; move the logic upstream or use a measure.

Exam tip. When a question describes "ambiguous paths", "unexpected totals" or "slow queries" after relationships were added, look for bidirectional relationships, a snowflake chain or two fact tables joined directly.

Mini case study

Context. Northwind Cycles has a lakehouse with Delta tables fact_orders (210 million rows), dim_customer, dim_product, dim_date, and a table customer_tag (CustomerId, Tag) with several tags per customer. The BI team wants one governed model for all regional reports. Users in Europe must only see European sales. The Marketing team wants revenue by tag. The nightly Power Query-based import of the previous model takes 3 hours.

Questions.

1. Which storage mode would you choose and why?
2. How do you model the tags?
3. How do you give each regional manager their own view?

Solution.

1. Direct Lake (on OneLake) because the data already exists as Delta tables in OneLake, the fact table is large, and there is no need for a 3-hour import. The previous Import approach is replaced by framing after the pipeline writes the tables.
2. Create dim_tag (distinct tags) and use customer_tag as a bridge between dim_tag and dim_customer, with bidirectional filtering only on the bridge-to-customer relationship. Revenue by tag counts a customer once per tag.
3. Define a role with row-level security on dim_customer (filter on the Region column) and assign regional security groups to roles. With Direct Lake on OneLake, role security defined in the model is enforced by the model; for the SQL analytics endpoint flavor, SQL-level row security would trigger fallback to DirectQuery, which is another reason to define the role in the model.

Comment. The solution keeps one star schema, avoids a many-to-many relationship that would be hard to explain, and uses the storage mode that matches the data's location.

Key terms

• Semantic model: tabular model with tables, relationships, measures and security.
• Import / DirectQuery / Dual / Direct Lake: storage modes.
• Framing: Direct Lake refresh that updates the pointers to Delta table versions.
• Star schema: facts joined to denormalized dimensions.
• Grain: what one fact row represents.
• Bridge table: table of key pairs resolving a many-to-many relationship.
• Cross-filter direction: direction in which filters travel across a relationship.
• Large semantic model storage format: setting that lifts the default size ceiling.
• Composite model: model mixing storage modes or sources.
• Limited relationship: relationship between tables of different storage modes.

Summary checklist

• I can pick Import, DirectQuery, Dual or Direct Lake from freshness, size, source and feature constraints.
• I know the two Direct Lake flavors and their differences in fallback and mixing.
• I can draw a star schema with grain, keys and one-to-many single-direction relationships.
• I can model role-playing dates with one active relationship and USERELATIONSHIP.
• I can resolve many-to-many with a bridge table and limited bidirectional filtering.
• I know when to enable the large semantic model storage format.
• I can describe aggregation tables and Dual dimensions in a composite model.

Official sources

• Study guide for Exam DP-600: https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-600 (opens in a new tab)
• Direct Lake overview: https://learn.microsoft.com/en-us/fabric/fundamentals/direct-lake-overview (opens in a new tab)
• How Direct Lake works: https://learn.microsoft.com/en-us/fabric/fundamentals/direct-lake-how-it-works (opens in a new tab)
• Develop Direct Lake semantic models: https://learn.microsoft.com/en-us/fabric/fundamentals/direct-lake-develop (opens in a new tab)
• DirectQuery in Power BI: https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about (opens in a new tab)
• Optimization guide for Power BI: https://learn.microsoft.com/en-us/power-bi/guidance/power-bi-optimization (opens in a new tab)