Microsoft Fabric
Semantic Models in Microsoft Fabric: How Power BI Fits into a Modern Data Platform
Where semantic models sit in a Fabric architecture: Gold tables vs business contracts, Lakehouse or Warehouse sources, Direct Lake, Import, DirectQuery, star schemas and security.
On this page
A Fabric platform usually begins with ingestion and transformation: land the data in Bronze, clean it in Silver, shape it in Gold. Those layers are well covered. The question that comes next is less often asked explicitly:
After Bronze, Silver and Gold, what should applications and analysts actually consume?
It is tempting to answer “the Gold tables”. Gold is curated, business-ready and stable. But a Gold table is not necessarily the final consumer contract. A table stores rows. It does not know that Total Sales excludes returns, that Customer filters Orders, that fiscal years start in July, or that a regional manager may only see their region.
A semantic model is where that knowledge lives. In Microsoft Fabric, a semantic model (the Power BI item formerly called a dataset) provides:
- business measures such as Total Sales and Average Order Value, defined once
- relationships between facts and dimensions
- business naming instead of engineering column names
- hierarchies such as Year → Quarter → Month
- security: row-level and object-level rules for consumers
- reusable analytical logic that every report shares
This article explains where semantic models fit in a Fabric architecture, how they relate to the Lakehouse, the Warehouse and Power BI, and how to choose between Direct Lake, Import and DirectQuery. It is an architecture guide, not a Power BI tutorial.
Where the semantic model sits
In a medallion architecture, the semantic model is the business-facing serving layer on top of Gold:
- BronzeRaw, as ingested
- SilverCleaned and conformed
- GoldBusiness-ready tables
- Semantic modelMeasures, relationships, security
- ConsumersPower BI, Excel, other clients
The division of responsibility is clean. Bronze, Silver and Gold are owned by data engineering and answer “is the data correct and complete?”. The semantic model is owned by whoever owns the business definitions, sometimes the same team, often a BI or analytics team, and answers “what does this number mean?”. The Medallion Architecture guide covers the engineering layers in depth. This article starts where Gold ends.
Why not query Gold directly?
Sometimes you should. Direct table access is fine when:
- the consumer is an engineer or analyst writing SQL against a well-understood table
- an application or API needs specific rows, not aggregated business metrics
- the question is one-off exploration rather than a repeated report
- there is only one consumer, and the logic lives in one place anyway
A semantic model adds value as soon as the same metrics are used by several reports or several people. Without one, each report re-implements Total Sales in its own way, and eventually two dashboards show two different numbers for the same quarter. A semantic model provides:
- Governed measures: one definition of each metric, versioned and reviewed.
- Consistent definitions: every report that uses Order Count gets the same answer.
- Reusable relationships: report authors do not need to know which keys join which tables.
- Business naming:
Customer Name, notcust_nm_v2. - Time intelligence: year-to-date, prior period and rolling windows, built on a proper date table.
- Security: row-level and object-level rules enforced for every query through the model.
- Discoverability: one endorsed model in the OneLake catalog instead of dozens of similar tables.
The rule of thumb: query Gold for data, query the semantic model for metrics.
Lakehouse or Warehouse as the source
In Fabric, a semantic model typically reads Gold tables from either a Lakehouse or a Warehouse. Both store Delta tables in OneLake, so both work with Direct Lake. The choice is usually made by how the Gold layer is built, not by the semantic model.
| Source | Typical Gold layer | Strengths for the model | Watch for |
|---|---|---|---|
| Lakehouse | Built with Spark notebooks, Spark SQL or Materialized Lake Views | Natural fit for Spark-first teams; Gold tables are Delta tables the model reads directly | Optimize table layout; non-materialized SQL views over the Lakehouse are not Direct Lake sources |
| Warehouse | Built with T-SQL: stored procedures, CTAS, MERGE |
Familiar to SQL teams; multi-table transactions; SQL-level permissions | SQL-level row-level security and views affect Direct Lake behavior, as explained below |
If your Gold layer is Spark-first, keep the model on the Lakehouse. If your Gold layer is a T-SQL dimensional model, keep it on the Warehouse. Do not copy Gold into a second engine just for Power BI. The Lakehouse vs Warehouse guide covers the broader decision.
One practical tip: where you would otherwise build a SQL view to shape data for the model, consider a table or a Materialized Lake View instead. Microsoft’s Direct Lake documentation notes that a model table based on a non-materialized SQL view is not supported in Direct Lake on OneLake and causes DirectQuery fallback in Direct Lake on SQL.
Direct Lake
Direct Lake is a table storage mode for Power BI semantic models, available in Fabric. It reads Delta tables from OneLake directly into the same in-memory engine (VertiPaq) that Import mode uses, without copying the data into the model at refresh time.
What makes it different:
- No data copy on refresh. An Import refresh reloads the data. A Direct Lake refresh is framing: the model reads the Delta table metadata and points at the latest Parquet files. It typically takes seconds.
- Columns load on demand. When a query needs a column, the engine loads the whole column into memory (transcoding). Unused columns never load, so a model can be larger than the memory it actually uses.
- In-memory query performance. Once columns are resident, queries are answered by VertiPaq, comparable to Import.
- Automatic updates. By default, the model reframes when it detects changes in the underlying Delta tables, so new data appears without a refresh schedule. You can switch this off when consumers should only see data after an ETL run completes.
Microsoft currently documents two Direct Lake options:
- Direct Lake on OneLake reads Delta tables from one or more Fabric items and uses OneLake security for permission checks. It does not fall back to DirectQuery. If a query cannot be served, it returns an error. Microsoft recommends it for new semantic models.
- Direct Lake on SQL analytics endpoints reads from a single Fabric item and uses its SQL analytics endpoint for discovery and permission checks. It can fall back to DirectQuery when it cannot serve a table from Delta, for example when the table is a SQL view, when SQL-level row-level security, object-level security or dynamic data masking applies, or when capacity guardrails are exceeded. The
DirectLakeBehaviorproperty controls this: Automatic, DirectLakeOnly or DirectQueryOnly.
Performance considerations that follow from this design:
- Delta table health matters. Capacity guardrails limit Parquet files, row groups and rows per table, per SKU. Too many small files can make framing fail, or cause fallback in Direct Lake on SQL. Regular
OPTIMIZEand sensible file sizes are part of the model’s performance. - The first query after framing is slower for columns that must be transcoded again. Later queries hit memory.
- Memory is still finite. Each SKU has a maximum memory per model. Beyond it, columns are paged in and out and queries slow down.
- Some modeling features differ. Calculated columns and calculated tables over Direct Lake tables are limited, and some are in preview for Direct Lake on OneLake. Push derived columns upstream into Gold instead.
Import, DirectQuery and Direct Lake compared
Storage mode is a property of each table in a semantic model, and a model can mix modes. A model that does is called a composite model. The comparison below summarizes current Microsoft documentation at the level that matters for architecture decisions.
| Mode | Data movement | Freshness | Performance characteristics | Operational complexity | Typical use case |
|---|---|---|---|---|---|
| Import | Full copy into the model at each refresh, compressed in memory | As of the last refresh | Fast in-memory queries; the whole model must fit in memory | Refresh schedules, refresh duration and capacity cost grow with data volume | Small to medium models, any source, Power Query shaping, self-service |
| DirectQuery | None; queries are translated and sent to the source | Live, at query time | Depends on the source; every visual generates source queries | Source must handle report concurrency; some DAX and modeling limits | Very large or near-real-time data where copying is not acceptable |
| Direct Lake | None at refresh; columns load from OneLake Delta files on demand | As of the last framing, automatic by default | In-memory like Import once columns are resident; subject to SKU guardrails | Delta table maintenance, guardrails, fallback behavior on SQL endpoints | Large Fabric Gold layers in Lakehouse or Warehouse, requires Fabric capacity |
Two more facts shape the decision. Direct Lake requires a Fabric capacity (F SKU), while Import and DirectQuery work with any Fabric or Power BI license. Direct Lake works only on Fabric data in OneLake Delta format, while Import and DirectQuery connect to almost anything.
Rule of thumb: if Gold is already in Fabric and the model is large or refreshed often, start with Direct Lake on OneLake. Use Import for smaller models, non-Fabric sources or self-service models that need Power Query. Use DirectQuery when the data must stay where it is and be queried live.
Designing the model
A semantic model on top of a good Gold layer should be small in concept: a few facts, the dimensions that describe them, and the measures people actually use. Keep the examples neutral and the names business-friendly.
Fact tables record events and carry numeric values:
fact_sales: one row per sales line, withsales_amount,quantity,customer_key,product_key,date_keyfact_orders: one row per order, withorder_id,customer_key,date_key
Dimension tables describe the who, what and when:
dim_customer: customer name, segment, regiondim_product: product name, category, branddim_date: date, month, quarter, fiscal year, marked as the model’s date table
Measures encode the business definitions:
- Total Sales
- Order Count
- Average Order Value
In the model, rename tables and columns for consumers (fact_sales becomes Sales, sales_amount becomes Sales Amount) and hide technical keys. The Gold table keeps its engineering names; the model publishes business names.
Star schema
Semantic models work best on a star schema: fact tables in the center, dimension tables around them, one relationship from each dimension to each fact.
- dim_customerWho bought
- dim_dateWhen
- dim_productWhat
- fact_salesOne row per sales line
- MeasuresTotal Sales, Average Order Value
Three properties make this shape work:
- Filter direction. Filters flow from dimensions to facts. Selecting a region in
dim_customerfiltersfact_sales. Keep relationships single-direction by default. Bidirectional filters create ambiguity and slow queries, and they are not supported in Direct Lake when the source relies on OneLake security row-level security. - Cardinality. Dimension-to-fact relationships are one-to-many. The “one” side must be unique: in Direct Lake, queries fail if duplicate values are detected on the one side, so enforce uniqueness of dimension keys in Gold.
- Fact and dimension separation. Facts hold numbers and keys; dimensions hold descriptive attributes. Wide, flattened tables that mix both work for one report but make measures harder to reuse.
If the Gold layer already follows a dimensional design, the model is mostly relationships and measures. If Gold is a set of wide tables, consider adding dimensions upstream rather than reshaping inside the model.
Measures vs calculated columns
Both are written in DAX, but they behave differently.
- A measure is evaluated at query time in the context of the current filters. Total Sales for 2026, for one region or for one product all come from one measure definition. Measures are where business metrics belong.
- A calculated column is computed row by row and stored in the table. It is useful for attributes you slice by, but it increases model size. In Direct Lake it has limited support.
Small examples:
Total Sales = SUM ( Sales[Sales Amount] )
Order Count = DISTINCTCOUNT ( Orders[Order ID] )
Average Order Value = DIVIDE ( [Total Sales], [Order Count] )
Average Order Value reuses the other two measures. Change the definition of Total Sales once, and every dependent measure and report follows. That reuse is the main reason semantic models exist.
A practical guideline for Fabric: if a value can be computed per row, compute it upstream in Gold. If it depends on filters, make it a measure. Price bands, full names and fiscal periods belong in Gold or in a dimension. Ratios, totals, year-to-date values and averages belong in measures.
Security in the semantic layer
A semantic model has its own security layer, and it is easy to misunderstand what it protects.
- Workspace access. Workspace Admin, Member and Contributor roles implicitly have Write permission on the semantic model. Model-level data access rules are not enforced for users with Write permission, so report consumers should be Viewers or receive access through item permissions or an app.
- Semantic model permissions. Read lets a user view reports built on the model. Build lets them create new reports and query the model. Write lets them change it.
- Row-level security (RLS). Roles with DAX filters, such as “a regional manager sees only their region”, applied to every query through the model.
- Object-level security (OLS). Hides tables or columns from specific roles. It is supported for Import, DirectQuery and Direct Lake.
For Direct Lake, also decide whose identity reads the data. By default the model uses single sign-on: each user’s own identity, so each user also needs read access to the source tables. Alternatively, you can bind the model to a fixed-identity cloud connection, so users query the model without any access to the Lakehouse or Warehouse. Microsoft recommends a fixed identity when you rely on semantic-model RLS, because users with direct source access could otherwise bypass the model’s rules.
That leads to the key principle: semantic-model security complements data-layer security; it does not replace it. Rules defined in the model apply only to queries through the model. If the same users can read the tables through the SQL analytics endpoint or OneLake, those paths need their own controls, such as OneLake security, which applies across Fabric engines. The Fabric governance guide explains which control applies at which layer.
Serving multiple consumers
One well-designed model can serve many consumers:
- Gold data
- Semantic model
- Power BI reports
- ExcelAnalyze in Excel, live connection
- Paginated reports
- Other XMLA clientsTools that support the XMLA endpoint
This is the most important architectural idea in this article: the semantic model is a shared analytical contract. When Finance opens Excel and Sales opens a Power BI report, both see the same Total Sales, filtered by the same security rules, from the same endorsed model. Changes to the contract, such as new measures, renamed columns or new relationships, deserve the same care as changes to a Gold table schema: version them in Git, review them, and deploy them through Dev, Test and Prod.
Governance applies directly. A production semantic model is usually the best candidate for certification in Fabric, because it is what consumers actually use. Give it a named owner, a sensitivity label and clean lineage back to Gold.
When not to use a semantic model
Semantic models are an analytical serving layer, not a universal data-access layer. Use something else for:
- Raw engineering exploration: query Bronze and Silver with notebooks or SQL.
- Data science workloads: training data and experiments belong in notebooks reading Delta tables.
- Machine learning feature processing: feature pipelines need row-level data and Spark, not aggregated measures.
- Backend application queries: applications that need specific rows should use the SQL analytics endpoint, a Warehouse or an API layer.
- Operational APIs: transactional, low-latency lookups are not what a semantic model is designed for.
If a consumer needs metrics with consistent definitions and security, use the model. If it needs rows, use the tables.
From concept to practice: Lab 05
This article is the conceptual foundation for a future hands-on lab, Lab 05 — Semantic Models and Power BI, which is not published yet. It will build a Gold model, a semantic model with relationships and measures, the appropriate storage mode (Direct Lake where it fits) and a Power BI report on top. Until then, Lab 01: End-to-End Sales Analytics in Microsoft Fabric builds the Lakehouse and Gold tables that such a model would sit on.
Conclusion
The semantic model is where a data platform stops being a set of tables and becomes a set of answers. Gold makes data correct. The semantic model makes it meaningful, consistent and safe to consume.
In Fabric, that layer sits naturally on top of a Lakehouse or Warehouse Gold layer. Direct Lake removes most of the refresh cost that used to make large Import models painful, provided the Delta tables are well maintained and the security design is deliberate. Design the model as a star, keep business logic in measures, keep row-level computation upstream, and treat the model as a governed, versioned contract, not a by-product of the first report.
References
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- Cheat sheetFabric Medallion ArchitectureDesign a practical Bronze, Silver, and Gold architecture in Microsoft Fabric with clear data contracts, replayability, incremental processing, quality gates, and serving responsibilities.Open cheat sheet
- ArticlePower BI Visualization in Microsoft Fabric: From Semantic Model to Effective ReportHow to turn a governed Fabric semantic model into a Power BI report people act on: page hierarchy, visual choice, KPIs, drillthrough, accessibility and performance.Read article
What should I build next?
I'm building Javi Builds around practical problems data engineers actually face. Tell me what you'd like to see next.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.