Semantic model

What is a semantic model?

A semantic model is a structured layer between your raw data and the reports users build on top of it. It holds the tables relevant to a business domain, the relationships between those tables, the calculations (measures and calculated columns), and the security rules. Instead of every analyst figuring out how "revenue" or "active customer" is defined, the definition lives in one place.

You can think of a semantic model as a shared language for the organisation. Everyone reporting on revenue gets the same number automatically, because the definition is centralised. Without that layer, every report starts from its own interpretation and every board meeting turns into a discussion about which figure is right.

In the Microsoft ecosystem, semantic model is now the official name for what used to be called a Power BI dataset. Microsoft renamed it because "dataset" no longer captured the scope: a modern semantic model contains much more than data.

Why use a semantic model?

1. One source of truth for business terms
   What does "revenue excluding discount" mean? What counts as an "active customer"? The definitions live in the model. Every report uses the same one.

2. Reuse across reports
   Dozens of reports can share the same model. Change a calculation once and every report follows.

3. Performance
   A semantic model stores data in an optimised format (columnar, compressed), letting reports respond in milliseconds even over millions of rows.

4. Security at the source
   Row level security and object level security live at the model level. Users without access to certain rows or tables will never see them in any report built on the model.

5. Self-service under control
   Business users build their own reports on top of a certified model instead of each rolling their own data island. Self-service analytics with consistency built in.

Parts of a semantic model

Tables
Fact tables (sales, invoices, tickets) and dimension tables (customer, product, date) that together describe the domain. Usually arranged in a star schema.

Relationships
The links between tables, usually one-to-many from dimension to fact. Correct relationships are crucial for filtering to work as expected in reports.

Calculated columns
Columns computed per row at load time. For example, deriving an age group from a date of birth.

Measures
DAX calculations that run only when a visualisation requests a value. They react to filters in the report. Example: revenue for the current quarter.

Hierarchies
Structures like Year > Quarter > Month > Day or Region > Country > City. They make drill-down in reports straightforward.

Roles and security
Row level security filters rows per user, for example so a regional manager only sees their own region. Object level security hides entire tables or columns for certain roles.

Calculation groups
Reusable patterns of calculations (year-to-date, year-over-year, same period last year) that you define once and apply to every measure. They prevent dozens of near-duplicate measures.

Perspectives and translations
Subsets of the model for specific audiences, and translations for multilingual organisations.

Shared semantic model versus ad-hoc model

Two patterns exist side by side in practice.

Shared ("enterprise") model
Built centrally by a BI team, certified, thoroughly documented. Multiple reports and users share the same model. Ideal for shared KPIs and audit-sensitive reporting.

Ad-hoc model
Built by an analyst for one specific report or project. Faster to ship, more flexibility, but risk of duplicate definitions and weak governance.

Healthy organisations combine both: a core of certified models for the major domains (finance, sales, operations), and room for ad-hoc models as long as they don't grow into uncontrolled shadow IT. Power BI's workspace structure and endorsement labels ("Certified", "Promoted") help draw that line.

Semantic model in Microsoft Fabric

Within Microsoft Fabric the semantic model holds a central place. A few features deserve attention:

Direct Lake
Fabric lets you put a semantic model directly on Delta tables in OneLake without copying the data. You get import-like speed without import-like latency. This is one of the biggest reasons Fabric was adopted so quickly in existing Power BI environments.

TMDL (Tabular Model Definition Language)
A text format for describing semantic models. Combined with the Power BI Project format (PBIP), you can version models in Git and run review workflows like you would for code.

Composite models
A model can import data from one source and at the same time use DirectQuery to pull data from another. Handy for combining current data with historical aggregates.

Calculation groups
Now central in Fabric for cutting down on measure sprawl. Effectively standard practice in new projects.

Good practices

• Star schema
  Keep the table structure as close to a Kimball star schema as you can. Power BI and DAX are built around it. Complex schemas show up later as slow queries and unexpected filters.

• Measure over calculated column
  Where possible, pick a measure over a calculated column. Measures only compute when they appear and are lighter on the model.

• Central date table
  Always use a dedicated date table with a continuous range of days. Build it yourself instead of relying on auto-date logic.

• Naming and folder conventions
  Consistent naming for tables, measures, and folders. Users find what they need faster and new analysts ramp up more quickly.

• Documentation in the model itself
  Use descriptions on tables and measures. They show as tooltips in Power BI and lower the barrier to self-service.

• Version control via PBIP
  Keep models in PBIP format in Git. That makes reviews, rollbacks, and collaboration between multiple developers finally workable.

A good semantic model is less spectacular than a polished dashboard, but it is usually the piece that decides whether a BI implementation scales or gets stuck two years in on duplicate definitions and mistrusted numbers.