Designing the enterprise semantic layer on Microsoft Fabric

Goal#

Stand up one enterprise semantic layer over OneLake that BI and AI consumers share: interactive and paginated Power BI reports, Analyze in Excel, Copilot, and a natural-language Fabric Data Agent all read the same certified measures, relationships, and row-level security. The layer must scale to large models, stay current with the lake, and define business logic once so every experience agrees.

Recommended architecture#

The layer is three tiers over a single physical copy of the data.

1. Data stores in OneLake. Model the data once as star-schema Delta tables in a lakehouse or warehouse; both persist Delta/Parquet in OneLake, and shortcuts/mirroring can pull external stores in zero-copy. A semantic model is a data model built on Analysis Services tabular technology, and a single model can combine multiple sources ^[S2].
2. Shared certified semantic models. A published semantic model is the container of modeled data that reports, dashboards, and apps are built on ^[S3]. Endorse/certify the enterprise model, define shared measures, relationships, and RLS once, and let consumers live-connect with Build permission rather than each team re-modeling ^[S1].
3. BI and AI consumers. Beyond interactive reports, the same model serves paginated reports, data explorations, ad-hoc DAX, and refreshable Excel pivot/flat tables through Analyze in Excel ^[S1]; Copilot in Power BI and the Fabric Data Agent consume it too ^{[S4] [S9]}.

Inference: Making the semantic model (and, where entity reasoning is needed, a Fabric IQ ontology built from it) the single definition point is the core architectural move — it is what lets one set of business logic reach both the BI and AI consumer tiers without duplication.

Alternative topologies#

• Shared/certified enterprise model (recommended). One endorsed model per subject area; teams live-connect and add only report-level measures locally, which add a calculation to a report without writing anything into the shared model ^[S1]. This is the topology that keeps definitions consistent for both BI and AI.
• Reuse an existing enterprise model. If the organization already has data-warehouse model investments in SQL Server or Azure Analysis Services, live-connecting to that model is a recommended approach rather than rebuilding it inside Power BI ^[S6]; SSAS needs an on-premises data gateway, Azure Analysis Services connects without one, and the report user's identity enforces permissions ^[S6].

Data flow#

Delta tables are written to OneLake by ingestion/engineering. The certified Direct Lake semantic model reads columns from those Delta tables on demand rather than importing all data ^[S7], and framing — a metadata-only refresh that typically finishes in seconds — rebinds the model to the latest Delta version and sets the baseline for future queries ^[S8]. Consumers connect live: reports draw from exactly one semantic model each ^[S5]; Copilot and the Data Agent issue queries that the engine answers from the same model. The Data Agent routes a natural-language question through NL2DAX for a semantic-model source ^[S10] under the requesting user's own credentials, so RLS/OLS still apply ^{[S10] [S11]}.

Storage-mode strategy#

Power BI supports three model storage modes — Import, DirectQuery, and Composite — distinguished by whether data is copied into the model or left in the source at query time ^[S2]. Direct Lake is a fourth storage mode in which the model reads Delta tables straight from OneLake, avoiding both import and DirectQuery, pairing import-like performance with DirectQuery-like freshness ^[S12].

• Prefer Direct Lake on OneLake for the shared enterprise model. It gives OneLake security, richer modeling, and faster queries, never falls back to DirectQuery, and skips SQL-endpoint coupling, which enables more efficient DAX plans because no SQL security check is needed ^{[S8] [S13]}.
• Use Direct Lake on SQL only when you need SQL-endpoint security rules under delegated identity, or when DirectQuery fallback is required ^{[S13] [S14]}. Here the SQL endpoint is consulted only for schema and security discovery — data is still read from OneLake unless a query falls back ^[S15].
• Import serves a snapshot and relies on scheduled or on-demand refresh; DirectQuery forwards queries so results always reflect current source data; both need an on-premises gateway when the source is not reachable over the internet ^[S2]. Reach for these for non-Fabric sources, or shortcut/mirror the data into OneLake to earn Direct Lake.
• Composite lets very large fact tables stay in Direct Lake (avoiding refresh cost) while dimension tables use Import or DirectQuery in the same model ^[S16].

Fallback and framing behaviours#

DirectQuery fallback exists only in Direct Lake on SQL; documented triggers are querying a SQL-endpoint view, querying a table with SQL-endpoint row-level security, and a Delta table exceeding capacity guardrails ^[S17]. A fallen-back query reads the SQL analytics endpoint directly and returns the latest data — escaping the framing point-in-time — at the cost of typically slower performance ^[S17]. Fallback is governed by the model's DirectLakeBehavior property, which has no effect on Direct Lake on OneLake models ^[S8]. Direct Lake queries reflect the Delta state as of the most recent successful framing, not the live table, so writes landing after the last frame stay invisible until the next one ^[S8]. An automatic-updates setting (on by default) reframes tables whenever OneLake data changes; turn it off to control visibility with manual or scheduled framing during long ETL loads ^[S8]. Framing can fail when a Delta table breaches capacity guardrails, such as exceeding 10,000 Parquet files in one table ^[S8].

Component responsibilities#

Making the layer AI-ready#

A shared semantic layer becomes AI-ready in two grounded steps.

1. Ontology. Fabric IQ delivers business context through two core items — the ontology (preview) and the semantic model ^[S19]. An ontology defines core business entities, relationships, properties, rules, and actions, and exposes an NL2Ontology layer that converts business questions into structured queries ^[S18]. Crucially, ontologies can be generated or aligned directly from Power BI semantic models already in production, so business terms and KPIs stay consistent across reports, agents, and applications instead of being redefined per experience ^[S18].
2. Data Agent grounding. A Fabric data agent is a virtual analyst that answers natural-language questions grounded in semantic models and ontologies, publishable across Microsoft 365, Foundry, Copilot Studio, and custom apps ^[S9]. It routes a semantic-model question through NL2DAX ^[S10], needs only Read permission on the model (Build is not required, and RLS/OLS still apply) ^[S11], executes read-only under the user's own credentials ^[S10], and can combine up to five sources across lakehouses, warehouses, KQL databases, semantic models, ontologies, and Graph ^[S20].

Inference: Because the ontology and the Data Agent both ground on the same certified model, the semantic layer you build for BI is simultaneously the grounding layer for enterprise AI — no separate "AI model" is needed.

Performance#

Model design for scale rests on Direct Lake's memory behaviour and VertiPaq layout. A single model is bound by the SKU memory limit even though dynamic memory management lets the combined size of all models exceed it, and reloading an evicted model adds noticeable query delay ^[S21]. Enabling the large semantic model storage format raises the ceiling from the 1 GB default to the size of the capacity (or an admin-set maximum) and is available on Fabric F, Premium P, Embedded A, and PPU SKUs ^[S22]. Large-format models default to a VertiPaq segment size of 8 million rows, chosen to balance memory against query performance ^[S23], and use on-demand load by default so only the relevant pages are paged in, which makes evicted models answer again much faster ^[S24]. For Direct Lake specifically, prefer column segments of roughly 1–16 million rows — row groups well under a million rows hurt performance ^[S25] — target a 1 GB Delta file BinSize ^[S26], and partition only on low-cardinality columns (under ~100–200 distinct values) to avoid multiplying small files and segments ^[S27].

Governance & security#

Row-level security roles are either dynamic (filtering by the report user's identity) or static (one fixed filter per role) ^[S2], and the Power BI service adds sensitivity labels, usage metrics, and audit logs ^[S28]. Direct Lake on OneLake can use OneLake security, whose tenant-, workspace-, and item-level policies cover data landing in OneLake ^[S13]. Because the Data Agent runs under the user's own credentials and RLS/OLS still apply ^{[S10] [S11]}, the security you define once on the certified model is enforced consistently across BI and AI — no separate AI-side security model. Changing a model's gateway/connection settings requires ownership; non-owners see it read-only ^[S2].

Cost & capacity#

Storage mode is the main cost lever: Import incurs refresh compute and duplicated storage, while Direct Lake avoids the copy and the refresh pipeline ^[S12]. Copilot in Power BI is enabled by default but only functions on a paid Fabric capacity of F2 or higher (or Premium P1+) ^[S4]. Any organization sharing Power BI content needs at least one Pro or PPU licensed user regardless of capacity size ^[S29]. The main levers on a model's capacity impact are where it is hosted, its storage mode, gateway dependencies, imported-data volume, and refresh type/frequency ^[S30].

Inference: On a shared enterprise model, storage-mode choice is largely a memory-and-freshness decision, not merely a query-speed one — under-sizing shows up as evictions and reload latency, not outright failure.

Risks & anti-patterns#

• Model sprawl / duplicated definitions. Without a shared ontology layer, AI systems are described as unfit for high-stakes decisions because every business question otherwise requires a domain expert to manually translate raw tables into business concepts ^[S18]. The fix is one certified model plus, where needed, an ontology aligned to it ^[S18].
• Direct Lake on SQL over a view. Basing a Direct Lake on SQL table on a SQL view forces every query into DirectQuery fallback, which usually slows it down — prefer base tables ^[S16].
• Overwrite / vacuum on framed tables. Loading a Delta table with Overwrite wipes commit history and forces a full cold reload ^[S31]; vacuuming must not remove the Parquet files of a framed version before the next reframe, or queries fail ^[S32].
• Full refresh near memory limits (Import). Refreshing a model whose size approaches half the capacity's memory (e.g. a 12 GB model on a 25 GB capacity) risks running out of memory mid-refresh; mitigate with fine-grained refreshes via the enhanced-refresh REST API or the XMLA endpoint ^[S33].
• Data Agent row/region limits. Agent responses are capped at 25 rows and 25 columns ^[S34], and the agent cannot query when its capacity and the data source's capacity are in different regions ^[S35].

Internals#

Architecture & design#

Under the large-format toggle, a model's service storage mode changes from ABF (Analysis Services backup file) to Premium Files, which is why the feature is offered only in Azure regions that support Azure Premium Files ^[S36]. A Direct Lake model is recognizable over XMLA by a database compatibilityLevel of 1604 or higher, partitions whose mode is directLake, and partitions referencing a shared expression as their data source ^[S37]. In Direct Lake on OneLake the shared expression points straight at the OneLake storage location, and OneLake APIs handle schema discovery, security, and data loading with no SQL endpoint involved ^[S38]; in Direct Lake on SQL the shared expression points at the SQL analytics endpoint, consulted only for schema and security discovery while data is still read from OneLake unless a query falls back ^[S15].

How it works internally#

Direct Lake loads column data lazily — nothing is read until a query first touches a column, and the load set includes columns needed by relationships and measures, not just those the query names ^[S39]. Transcoding remaps Parquet data IDs to VertiPaq IDs; it is usually a direct remap when both sides use RLE/Bit-Packing hybrid encoding, but if a Parquet file used plain or delta encoding, Direct Lake must re-encode the values, slowing column loading ^[S40]. On a cold query the engine merges the per-row-group local Parquet dictionaries of each needed column into one global VertiPaq dictionary, so a table with more row groups takes longer to transcode ^[S41], and a cold multi-table DAX query also builds join indexes from relationship key columns and loads Delta deletion vectors so deleted rows are excluded ^[S42]. On-demand paging: for large-format models, on-demand load pages only relevant data pages rather than the whole model, which makes evicted models answer again much faster ^[S24], and DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS exposes per-column Temperature and Last Accessed statistics revealing which columns are actually paged in ^[S43]. Eviction: a resident column can be evicted for three reasons — a framing refresh after the source Delta table changed, prolonged non-use by queries, or general memory pressure from concurrent operations ^[S44]. During incremental framing, Direct Lake reads each table's Delta log and evicts only the segments tied to removed row groups, keeping dictionaries and merely extending them with new values to avoid re-transcoding ^[S45]. A Direct Lake model occupies one of four residency states — cold, semiwarm, warm, or hot — with hot adding populated VertiScan caches on top of fully resident columns ^[S46].

Performance characteristics#

An Import-mode model must be loaded entirely into memory before it can answer queries ^[S47], whereas Direct Lake pages columns in on demand ^[S7]. Direct Lake does not use Delta or Parquet statistics for row-group or file skipping when loading column data, so pruning optimizations that help other engines do not reduce its transcoding work ^[S48]. The total row-group count across a Delta table's Parquet files maps one-to-one to the number of VertiPaq segments per column, so many tiny row groups produce many tiny segments and degrade query performance ^[S49]. V-Order speeds Direct Lake twice: it raises RLE compression quality so columns stream into memory faster during transcoding, and it lets VertiScan compute directly on compressed data without a decompression step ^[S50]. A model's in-memory footprint can be estimated from VertiPaq DMVs by summing DICTIONARY_SIZE (from DISCOVER_STORAGE_TABLE_COLUMNS) with USED_SIZE (from DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS) over the XMLA endpoint ^[S51].

Assumptions#

• The organization operates on Fabric capacity (F2+ where Copilot is in scope) and stores its analytical data as Delta tables in OneLake.
• A star-schema model already exists or will be built in the lake; this design covers the semantic layer above it, not the ingestion/transformation pipeline.
• Fabric IQ ontology and the operations agent are in preview ^{[S18] [S19]}; treat them as forward-looking where marked.

Open questions#

• Exact per-SKU memory ceilings for large-format models are set by the capacity/admin and are not stated as fixed numbers in the cited claims — size against your specific SKU.
• Whether to centralize on one enterprise model per subject area or federate per domain depends on team autonomy and governance maturity, which are outside the cited claims.

Source legend#