Microsoft Fabric Semantic Models - Simply Explained

Welcome to the M365 FM podcast, where we explore the people, ideas and technologies shaping the future of work. What a semantic model actually is. A semantic model is one of those terms that makes beginners close the tab. It sounds like something only a database administrator would need, but it's actually the layer that decides what every number in your reports means. Microsoft gave it a new name because the old word dataset never explained anything. Fabric made that layer something you build and reuse, and it changed how the data gets in. By the end of this, you'll know what it is, how it works, where it breaks, and where it sits next to teams, Excel and copilot. Welcome to the M365 FM podcast where we explore the people, ideas, and technologies shaping the future of work. So let's start with the plain version. Because the name does most of the damage. It's a description of one part of your business written in the words people already use. Microsoft calls it a logical description of an analytical domain with metrics and business friendly names. That phrase is hiding something simple. Take one number your team argues about every month, like revenue. Now count how many ways for different people would calculate it. The semantic model is where you settle that argument once by defining what revenue means, which rows count, and how it connects to everything else. That sounds abstract, so let's make it concrete. When the definition lives in one place, you change it once and every report updates. That beats hunting through a dozen spreadsheets that each invented their own version of the truth. If you've used power BI, you already know this object under a different name. Microsoft used to call it a data set. The name changed because data set was too generic. The new one signals a reusable asset, not a file bolted onto a single report. Plenty of older tutorials still say data set, so you'll see both words in the wild, and they point at the same thing. The cleanest way to hold it in your head is this. The semantic model carries your business definitions, and it sits between your raw data and everything built on top of it. But a model needs data underneath it, and that's where fabric comes in. How power BI and fabric fit together fabric is Microsoft's all in one analytics platform. The easiest way to picture it is one big data lake with a set of tools sitting on top. That lake has a name, one lake and every fabric tenant gets one automatically. Whether or not anyone asked for it, you can't delete it, and you can't create a second one that tells you a lot about the intent behind it. Because Microsoft wants your organization's data in one place instead of scattered across a dozen storage accounts. Fabric organizes all of this into a simple hierarchy. It's worth 30s because the same words show up everywhere. The tenant sits at the top and maps to your whole organization. Under that come workspaces, which act like shared folders for a team or a project. Inside a workspace, you find items and a lake house is one of them, a place where files and tables live together like a database sitting on top of a data lake. One lake is built on Azure Data Lake storage. It keeps tables in open formats called delta, parquet and iceberg. Those two words matter more than they sound, because open formats mean any tool can read the data. You're never locked into one product forever. Power BI lives inside fabric as the reporting tool, and the semantic model sits in the middle. Data lands in the lake. The model describes what the data means and reports read from the model before fabric. That model lived inside power BI attached to whichever report created it fabric, pulled it out, and made it a standalone item with its own name and its own life. One model can now serve many reports instead of hiding inside one. That's the setup, and it's a lot of moving parts to hold at once. So let's open the model itself and look at what's inside the building blocks inside a model, open up a semantic model and you find three things which are tables, relationships, and measures. The tables usually follow a pattern called a star schema. That name sounds mathematical when it's really just a way of separating what happened from how you want to look at it. The tables that record events are called facts, and each row is something that actually occurred like a sale, an order, or a support ticket. The tables that let you slice those events are called dimensions, and they hold the lenses you care about like product, region, customer, and date. A relationship connects the two. Each fact row points back to the dimensions it belongs to. So a single sale can be tied to a product, a store, and a day. Once those links exist, you can ask questions that span them. You could check how many units of a specific product sold in a specific region last quarter. Then there are the measures, and this is where the model earns its keep. A measure is a calculation written in a language called Dax, and it turns rows into numbers. Total revenue units sold and profit margin are all measures. Take a simple one to get total revenue. A measure adds up a revenue column across all the rows. A report is showing it recalculates itself the moment someone clicks a filter. That's the part people underestimate because a measure behaves like a rule. It responds to whatever slice of data is on screen instead of holding a frozen number. There's also a middle layer worth knowing, called a calculated column. That's a value worked out row by row and stored in the table so it behaves more like a normal column than a live calculation. Keeping columns and measures straight in your head saves a lot of confusion later. The quiet superpower here is naming a column called rev underscore edge underscore. LC can become net revenue after currency adjustment. That single rename is the difference between a report people trust and a report They squint at tables and columns, get human names, descriptions get filled in, and the data starts speaking the language of the business. Hold on to that idea, because the shape of the model is only half the story. The other half is how the data actually gets in, and that choice changes everything about how fast the model feels. The three ways data gets into a model. There are three main ways to feed a semantic model, and they trade freshness against speed in different ways. The first is import, which copies the data into the model's memory. When it refreshes, queries fly after that because everything is already loaded in memory. The catch is that the data is only as current as the last refresh. If your refresh runs at six in the morning, your numbers are a snapshot of six in the morning. The second is direct query, which does the opposite. It leaves the data where it lives and queries the source. Every time someone opens a report. You always see the latest numbers, but the source database usually wasn't built to answer thousands of report queries. Everything slows down when traffic picks up. The third one is the interesting one, and it only exists in fabric. It's called direct lake. Instead of copying data or querying a database, it reads the delta parquet files in one lake directly. There's no copy step, no overnight refresh, and no separate database sitting between them. You get the speed of import with the freshness of direct query, which is the combination that used to be impossible. Choosing between them comes down to a few practical things. Import is the safe default for small and medium models because it's fast, flexible, and works on any license. Direct query makes sense when the data has to be live to the second, or when it lives outside fabric in something like an Azure SQL database or snowflake Direct Lake is built for the opposite end of the scale, where the tables are huge and the data is already sitting in one lake at that size. Copying all of it would be slow, expensive, or simply impossible. You can also mix them in. A single model, can hold a giant fact table in Direct Lake and a small lookup table in import. That helps when the small table needs a bit of cleaning. That only Power Query can do. Fabric calls that a composite model, and it's a practical answer to a real problem, because most companies don't get to pick one clean approach for everything they own. That sounds almost too good, and it's fair to be skeptical. The mechanism behind it comes down to two words you'll hear from anyone who works in fabric. Together, they explain why a model can stay fast even when the data is enormous. How direct Lake actually works. The first word is transcoding, and it describes how the model loads data. When a report asks a question, the model works out which columns it needs. Then it loads only those columns from one lake into memory. If your report shows revenue by region, it loads the revenue column and the region column, and it leaves the other one hundred columns alone. The next time someone asks a similar question, those columns are already in memory and the answer comes back instantly. The second word is framing, and it's fabric's version of a refresh. Instead of copying data, framing reads the metadata of the delta tables, notices which files changed and updates the model's pointers to the latest ones. That usually takes seconds because it's moving. Bookkeeping rather than data framing, marks the moment your model trusts, so the data you see is as of the last successful framing that detail matters more than it sounds. There are two versions of Direct Lake, and the difference matters when something goes wrong. Direct leg on one leg, reads the leg files directly and has no fallback. If a query can't run, it fails and someone fixes it. Direct leg on sequel goes through a SQL endpoint instead, and when it hits something it can't handle, it falls back to Direct query. That fallback keeps reports alive, but it also means a report can slow down without anyone changing a thing. There's a setting called keep your direct leg data up to date, and it's on by default. It watches the lake and reframes automatically when something changes, so you don't have to babysit a refresh schedule because only the columns a query needs are pulled into memory, the total data can be far larger than the memory your capacity provides. Freshness has one more wrinkle worth knowing because framing locks in a moment, the model won't show you a data load that's only half finished. That's usually what you want, since a stable number beats a number that keeps moving while someone is reading it. Put those pieces together and the pitch starts to make sense. You point the model at the lake. It grabs only what each question needs and it stays current without copying the world every night. What this looks like in practice. Picture a concrete version, the kind of thing you do on your first day. Imagine a sales team with order data landing in a fabric lakehouse. Someone opens that lakehouse, chooses new power BI semantic model, gives it a name, and picks the tables to include. From there, they add the relationships between orders, products, and dates. Then they write a handful of measures like total revenue and average order value. That's the whole build. And once it exists, reports are the easy part. You can create one right in the browser or open Power BI desktop, connect to the model and build there instead. The important shift is that the model is the thing people share, not the report. One model can feed a finance dashboard, an operations scorecard, and a sales report. All of them pull the same definitions. So a number means the same thing everywhere it appears publishing follows the same logic. You save the model to a workspace, set permissions for the people who need it, and anyone with access can build on it. The model becomes the shared source and the reports become the parts you swap out. That's the order you want. As a team grows and the questions start changing. There's one small warning here. It's tempting to skip the descriptions and leave the cryptic column names in place. That shortcut always comes back to bite you, because the next person to open the model has no idea what anything means. This is also where the beginner friendly side of fabric shows up. You don't need to be a database administrator to add a relationship or write a simple measure, because the tools are visual and the language stays close to plain English. The harder part is the thinking rather than the clicking. Deciding what revenue should mean and which rows should count is a business decision that no tool makes for you. Get that thinking right and the model becomes something the whole company leans on. Get it wrong and you've automated a disagreement. So let's look at where this connects to the rest of the Microsoft tools you already use. Where it fits with Microsoft three. Sixty five and Azure Fabric doesn't sit off to the side of Microsoft three. Sixty five because the whole point is that your data shows up where you already work. Fabric integrates with Excel and teams, so a number built in a semantic model can be analyzed in a spreadsheet or shared in a channel, without anyone exporting a file. For a lot of teams, that's the moment the model stops feeling like an IT project and starts feeling like part of the daily routine. Most people will never open fabric at all, and they'll still benefit from the work done inside it. Underneath one leg speaks the same language as Azure Data Lake storage, so existing Azure services can connect to it. Azure Synapse Storage Explorer, and Azure Databricks can all reach the same data, which means your data engineers and your analysts aren't fighting over two separate copies. Fabric capacity itself is bought and built through Azure. If your company already runs on Azure, the bill lands somewhere your finance team recognizes. Then there's copilot, which makes the semantic model more important than it used to be. Copilot answers questions by reading the measures, relationships, and definitions inside your model. That means the quality of those definitions becomes the quality of the answer. Ask copilot for sales by region and it uses the model's version of sales rather than a guess. If your measures are well named and well described, the answers get sharper. If they're a mess, the AI passes the mess along politely. Microsoft has also started layering something newer on top called fabric IQ. It aims to give AI a shared business vocabulary across the platform. It's still settling in, so treat the details as moving, but the direction is clear. Your model is turning into the grounding layer for AI, not just the engine behind a dashboard. That's the upside. And it's real. Now for the honest part, because none of this comes free and none of it runs without limits. The catcher's limits, licensing and common mistakes start with licensing because it's the first thing that trips people up. Fabric runs on capacity, which is a pool of computing power. And on top of that, you need a per user license to do most of the work. There's a twist that surprises teams on smaller capacities. Anyone who just wants to view a report still needs a paid license. Free viewing only unlocks at x64 and above, which is a big jump from the entry level options. Direct Lake has its own rule, and it's stricter. It needs a fabric capacity or a premium capacity behind it, so the free per user license on its own won't run it. Import and direct query models are more forgiving and work on any license, including free. That's worth remembering. If you're learning on a trial. Then come the capacity limits. And these are the numbers people hit in production. Every capacity size has guardrails for rows per table, files per table, and memory on the smallest capacities from F two to F eight. You get around three hundred million rows per table, one thousand parquet files and three gigabytes of memory. Step up to F sixty four and those limits jump to one and a half billion rows, five thousand files, and twenty five gigabytes. What happens when you blow past them? Depends on which direct lake you're using on direct lake. On one lake, the refresh simply fails, and the model can't be queried until someone fixes the underlying tables on the older direct lake on SQL. The model falls back to direct query, so reports keep working but get slower. Neither one is a problem you can ignore because the fix is real work. The mistakes people make tend to repeat, and they're worth naming. Teams leave behind old default models that nobody owns. They bury heavy data preparation inside the model, where it's invisible to everyone else. They deploy a model and forget to refresh it so it serves stale data. They let one model grow per report instead of sharing one, until there are twenty definitions of the same metric, and they forget that sharing a report doesn't automatically grant access to the data underneath, which produces access errors that are hard to trace. Copilot has limits to it, answers in English only, and it can't do forecasting or spot anomalies for you. Yet it's a helper, not an oracle. None of this should scare you off. A fabric trial gives you sixty days to experiment, and that's usually enough to see whether any of it fits the way your team works. The recap and one question for you. So let's bring it all together. A semantic model holds the business definitions for your data, and it's the layer every report reads from fabric, feeds it straight from one lake using Direct Lake, which reads just the files it needs without copying them all. Framing keeps it current without an overnight refresh. The catches are real and they live in licensing, in capacity limits, and in the everyday discipline of naming things well and sharing one model instead of twenty. None of it is magic, and it isn't beyond a curious beginner. I'll leave you with one question. If you could define one number so that everyone in your company finally agreed on it, which number would it be? If this made the topic feel less intimidating, subscribe to M365 FM to master the tools and the mindset shaping the future of work.