i

Description

Passerelle de nuit, dont les cadres lumineux décalés forment un tunnel au-dessus d’une vallée enneigée.

Source

Tony Webster tonywebster · original page

Licence

CC0 Public domain

Original photo, unretouched. The background is a version of it stylised by the site.

Comparison — OneLake / Fabric vs Managed SQL Server for Power BI Reporting

You will be able to position, across nine architecture dimensions, a Fabric / OneLake platform (fed by SaaS mobile applications) against a managed SQL Server database (Azure SQL Database…

In brief

• You will be able to position, across nine architecture dimensions, a Fabric / OneLake platform (fed by SaaS mobile applications) against a managed SQL Server database (Azure SQL Database, Managed Instance, SQL database in Fabric) as a Power BI source.
• You will have a /5 rated heatmap, a decision tree, and typical scenarios to make decisions in meetings, without dogma.
• You will know how to estimate in orders of magnitude (clearly marked as such) what each option costs, and what makes you switch from one to the other.
• You will understand coexistence paths: most often, the right answer is neither one nor the other, but managed SQL + mirroring to Fabric.
• You will leave with links to the detailed dossiers (OneLake 01 to 04, SQL Server 05 to 08).

On this page

1. 1. Framework, interpretation, and the PaaS vs SaaS nuance
2. 2. The compared options
   1. 🟢 Beginner — Four reference architectures
   2. 🟡 Practical — What really distinguishes A from B/C
3. 3. Rating grid and how to read ratings
4. 4. Side-by-side comparison by aspect
   1. 4.1 Freshness
   2. 4.2 Performance
   3. 4.3 Cost
   4. 4.4 Complexity
   5. 4.5 Governance and security
   6. 4.6 Scalability
   7. 4.7 Required skills
   8. 4.8 Technology maturity
   9. 4.9 Lock-in risk
5. 5. Summary heatmap
6. 6. Costs Compared in Orders of Magnitude
   1. 🟡 Practical — Cost Items
   2. 🟡 Practical — Numerical Reasoning (illustrative)
   3. 🔴 Expert — Hidden Costs
7. 7. Decision Tree
   1. 🟢 Novice — Five Questions in Order
   2. 🟡 Practical — Hard Constraints That Bypass the Tree
   3. 🔴 Expert — Make the Decision Reversible
8. 8. Typical Scenarios
   1. 8.1 Scenario 1 — Field sales force, 150 users, existing SQL datamart (the common thread)
   2. 8.2 Scenario 2 — Multi-SaaS (CRM + SFA + support), changing schemas, data engineering team
   3. 8.3 Scenario 3 — Broad distribution (2,000 readers), few developers
   4. 8.4 Scenario 4 — Operational real-time (route tracking, alerts)
   5. 8.5 Scenario 5 — Small organization, 15 users, tight budget
   6. 8.6 Scenario 6 — Strict compliance (regulated sector)
9. 9. Migration and coexistence paths
   1. 🟡 Practice — “Mirroring first” path (the safest)
   2. 🟡 Practice — “SaaS extraction to OneLake” path
   3. 🔴 Expert — Long-term coexistence and dual-source governance
10. 10. What drives the switch
    1. 🟡 Practice — Switch thresholds (orders of magnitude, to calibrate)
    2. 🔴 Expert — Reasons not to switch
11. 11. Decision pitfalls
12. 12. AI contribution to the decision
13. 13. See also

Level: 🟢→🔴

1. Framework, interpretation, and the PaaS vs SaaS nuance#

This document uses the interpretations adopted for the dossier (details in the dossier's README).

• “OneLake + SaaS mobile applications” refers to Power BI reporting whose analytical source is Microsoft Fabric / OneLake (lakehouse, warehouse, Direct Lake, mirroring, shortcuts, pipelines, dataflows Gen2, eventstream), fed by SaaS mobile applications (field SFA/CRM, Dynamics 365, Salesforce, Power Apps, data collection apps). This implies API quotas, drifting schemas, data synchronized after offline usage, latency, and identity questions.
• “SQL Server in SaaS” refers to a cloud managed relational database — Azure SQL Database, Azure SQL Managed Instance, SQL database in Fabric; SQL Server on virtual machine is used as a contrast — used as a Power BI source (Import, DirectQuery, composite, or Direct Lake via mirroring), with the Power BI service itself in SaaS.

The two options are not mutually exclusive: Fabric mirroring of managed SQL (see § 9) is exactly the bridge. The comparison helps choose a center of gravity, not ban a building block.

Illustrative thread (anonymous): a SQL Server datamart fed by a field sales application, with multilingual Power BI reports, versioned in TFVC and delivered as installation kits. It is typically the starting point for a “should we move to OneLake?” discussion.

2. The compared options#

🟢 Beginner — Four reference architectures#

For contrast, E — SQL Server on VM: same principles as B/C, but you operate the OS, patches, high availability, and backups. Keep this only for compatibility reasons (features not available in PaaS, existing licenses): see 06 — SQL Server architecture.

🟡 Practical — What really distinguishes A from B/C#

1. Where transformation happens. In A, in Fabric Spark/SQL, in notebooks, pipelines, and dataflows; in B/C, in T-SQL procedures, with the relational engine enforcing integrity constraints (keys, constraints, transactions).
2. Which engine serves report queries. In A (Direct Lake), the VertiPaq engine reads Delta/Parquet files without copying; in B, it reads its own imported copy; in C, the SQL engine responds, query by query.
3. What the billing unit is. A relies on Fabric capacity (capacity units, shared by all workloads); B/C on a sized database (DTU or vCore) plus Power BI licenses; D combines both.
4. What the relationship is with the SaaS source. Neither A nor B removes the SaaS extraction problem: API quotas, pagination, evolving schemas. The choice changes where it is handled. See 03 — difficulties and pitfalls (OneLake) and 07 — difficulties and pitfalls (SQL Server).

3. Rating grid and how to read ratings#

The nine dimensions are those from the “reporting architectures” brief. A 5/5 rating is always favorable (including for “cost” and “complexity”: 5 = low cost, low complexity). Ratings are contextual expert assessments, not measurements (see Rating: Reading Ratings, Scores, and the Legend).

4. Side-by-side comparison by aspect#

4.1 Freshness#

Option A. Freshness depends on the slowest link in the chain: the SaaS API (quotas, pagination), orchestration, then Direct Lake “framing,” which refreshes model metadata. With mirroring from a supported source, replication is nearly continuous; with pipelines on SaaS APIs, expect from 15 minutes to an hour. An eventstream can get down to a few seconds for event flows, at the cost of added complexity.

Option B. Import refresh is scheduled (8 scheduled refreshes per day in shared capacity, up to 48 on Premium, PPU, or Fabric capacity, according to Microsoft documentation; status 2026-10) and can be triggered by API at the end of loading. Realistic freshness is measured in hours, at best a few tens of minutes on small models or with incremental refresh.

Option C. Freshness matches the database: excellent (real time) but achieved by shifting the load to the database at every user interaction.

Option D. SQL → OneLake mirroring is designed for low latency (on the order of a few seconds to minutes under normal conditions; order of magnitude, to be verified based on load), but the freshness of your datamart is still determined by its own loads.

4.2 Performance#

Option A. Direct Lake combines Import speed (VertiPaq in-memory columns) with no copy. Performance depends on Delta file hygiene: too many small files, no V-Order optimization or compaction, poorly sized row groups. Beyond capacity limits (model size per SKU, Direct Lake guardrails), behavior can switch to DirectQuery fallback: performance then drops without any change to the report. See 02 — OneLake architecture.

Option B. Import provides the best query performance at medium scale, with a well-designed model (star schema, low-cardinality columns, efficient measures). The ceiling is importable model size based on license/capacity.

Option C. DirectQuery makes performance dependent on the SQL engine and indexing: columnstore indexes, statistics, query plans. Well designed (aggregations, aggregated Import-mode tables in a composite model), it fits large fact tables; poorly designed, it produces 10-second visuals and a saturated database.

Option D. Direct Lake on the replica delivers A's performance without migrating processing: the team keeps T-SQL and its procedures.

4.3 Cost#

See details in § 6. In short: B is the most economical for a small scope (a database costing a few hundred dollars per month and Pro licenses); A requires a capacity entry ticket but pools Power BI and analytics; C combines high database cost (sizing must absorb interactive queries); D adds both, partly offset by no replication ETL.

4.4 Complexity#

• B is the simplest: few moving parts, standard skills, familiar diagnostics.
• C adds diagnostics for DAX → SQL generated queries, a source of opacity.
• A multiplies components (lakehouse, warehouse, notebooks, pipelines, dataflows, capacity, workspaces, domains) and a capacity model to monitor (smoothing, throttling).
• D combines B + a thin A layer: more components than B, but without rewriting processing.

4.5 Governance and security#

• A: OneLake provides a single storage point, lineage, sensitivity labels (Microsoft Purview), and “OneLake security” (data roles with RLS/CLS, documented with no preview mention; only integration of authorized third-party engines remains in preview, status 2026-10). The risk is security sprawl: workspace permissions, item permissions, model RLS, SQL endpoint-side security, Direct Lake-side security. An RLS rule misaligned across layers leaks or blocks access.
• B/C: the model is familiar — Entra ID authentication, database roles, native SQL RLS (DirectQuery), and Power BI RLS (Import). In DirectQuery with SSO identity, SQL RLS applies to the end user; without SSO, a service account sees everything and Power BI RLS alone carries security.
• D inherits both: make sure the OneLake replica does not bypass source protections (data masked in SQL must not be readable in clear text in OneLake).

4.6 Scalability#

• A: designed for very large volumes and scaling up capacity by SKU, with the constraint of shared capacity between interactive reports and batch processing (noisy neighbor).
• B/C: vertical scale-up (vCore/DTU), hyperscale for very large databases; B's limit is importable model size, C's limit is the relational engine under interactive load.
• D: analytics scales on the Fabric side, transactional writes on the SQL side.

4.7 Required skills#

4.8 Technology maturity#

Managed SQL and Power BI Import/DirectQuery are very mature. Fabric is GA but evolves quickly: some features move from preview to GA in a few months, and the ecosystem of practices (CI/CD, testing, observability) is solidifying. Azure SQL Database mirroring has been GA since November 2024, and Azure SQL Managed Instance mirroring is GA as well; extensions (gateway for a non-public instance, tables without a primary key) have arrived in stages since 2025 — check the exact status for your case in the up-to-date documentation (status 2026-10).

4.9 Lock-in risk#

OneLake stores Delta/Parquet, an open format, which reduces data lock-in; but lock-in shifts to orchestration, semantic models, permissions, and the capacity model. Managed SQL is locked into T-SQL (but portable to on-prem SQL Server or VM); Power BI remains the lock-in point for presentation in both cases.

5. Summary heatmap#

Legend: 🟥 1–2 · 🟧 3 · 🟨 3.5–4 · 🟩 4.5–5. Scores /5, the higher the better.

Honest reading:

• B wins for modest scope. It has the highest average: that's no coincidence, it's the least sophisticated combination.
• The average doesn't decide. Weight based on your context: if scalability and AI matter three times more than complexity, A and D rise sharply. Recalculate with your weights.
• C is rarely a destination choice. It's a point solution (very large fact table, real-time requirement, no-copy constraint), never a default.
• D is mostly valuable for its optionality: it buys the possibility of Direct Lake, notebooks, and AI without rewrites, at the cost of additional expense.

6. Costs Compared in Orders of Magnitude#

🟡 Practical — Cost Items#

🟡 Practical — Numerical Reasoning (illustrative)#

🔴 Expert — Hidden Costs#

1. Smoothing and throttling (Fabric): undersized capacity slows down everyone; the workaround (move up SKU, split capacities by criticality) has a cost. Plan capacity usage monitoring (Capacity Metrics app).
2. Re-runs: a SaaS load that fails on API quota consumes compute for nothing; idempotency and replay are both a cost and design item.
3. Mirroring: includes OneLake storage up to a capacity-proportional quota, beyond which it is billed as storage; replication compute is, per documentation, free under capacity (verify for your setup). The source carries additional load from transaction log reads.
4. DirectQuery: every user interaction generates queries; on a database billed by compute size, interactive usage requires sizing for peak.
5. Skills debt: without in-house Spark/Delta skills, A turns into recurring external services.
6. Cost of indecision: running two platforms without a clear target often costs more than choosing the reasoned “wrong” option.

7. Decision Tree#

🟢 Novice — Five Questions in Order#

Q1. Do you already have a SQL datamart that works and that the team has mastered?
    ├─ Yes → Q2
    └─ No (new project, dominant SaaS sources) → Q4

Q2. Is anything preventing you from staying with managed SQL + Import?
    (volume beyond imported model limits, need for AI/notebooks, need for
     many readers without individual licenses, freshness requirement < 15 min)
    ├─ No → OPTION B  (managed SQL + Import) — do not change what does not need changing
    └─ Yes → Q3

Q3. Can you keep T-SQL processing as is?
    ├─ Yes → OPTION D  (managed SQL + mirroring + Direct Lake)
    └─ No, you also need to move beyond T-SQL → OPTION A, phased migration (§ 9)

Q4. Is data mostly arriving through SaaS APIs/exports, with changing schemas?
    ├─ Yes, and you have/hire Spark/Delta skills → OPTION A
    └─ Yes, but the team is T-SQL → OPTION D or B (landing in SQL database,
       extraction via lightweight pipeline), A later

Q5. Need strict real-time on a large fact table, without copying?
    └─ Add a single table in DirectQuery as needed (composite model),
       never the whole model — OPTION C as a complement, not by default.

🟡 Practical — Hard Constraints That Bypass the Tree#

🔴 Expert — Make the Decision Reversible#

Always choose the variant where the exit costs the least: land raw SaaS data in open storage (OneLake or object storage), keep processing in a portable language, version models (PBIP/TMDL) and schemas, and hide the source behind a semantic layer referenced by reports. section 7 of dossier 06 and dossier 02 detail the semantic layer on each side.

8. Typical Scenarios#

8.1 Scenario 1 — Field sales force, 150 users, existing SQL datamart (the common thread)#

• Context: SFA application feeding a SQL datamart, multilingual reports, delivery by kits.
• Recommendation: B now (nothing breaks), D if a need for advanced analytics or AI appears. Monitor imported model refresh time.
• What changes the game: demand for near real-time visit data, or growth that exceeds importable model size.

8.2 Scenario 2 — Multi-SaaS (CRM + SFA + support), changing schemas, data engineering team#

• Context: three SaaS platforms, quota-limited APIs, required historization, notebook-based analysts.
• Recommendation: A: medallion lakehouse (raw bronze, cleaned silver, semantic gold), Direct Lake. Preserved raw data absorbs schema drift.
• Guardrails: pipeline idempotency, quota management, data contract. See 03 — OneLake + SaaS Mobile Apps: Difficulties and Pitfalls.

8.3 Scenario 3 — Broad distribution (2,000 readers), few developers#

• Recommendation: imported model or Direct Lake on capacity (readers without individual licenses if capacity threshold is reached), D if the database remains in SQL. License cost dominates (see § 6).

8.4 Scenario 4 — Operational real-time (route tracking, alerts)#

• Recommendation: A with eventstream (or Real-Time Intelligence) for the stream; Power BI reports in DirectQuery/streaming mode on this scope only; all other reporting in B/D. A single end-to-end “real-time” model is an anti-pattern.

8.5 Scenario 5 — Small organization, 15 users, tight budget#

• Recommendation: B, no hesitation. The simplest platform that meets the need is the best one. Reassess annually.

8.6 Scenario 6 — Strict compliance (regulated sector)#

• Recommendation: whichever your security team can audit. B/C offer long-established auditing practices; A requires mastering multilayer security (workspace, item, OneLake security, RLS). A compliance proof that does not exist is not a technical argument.

9. Migration and coexistence paths#

🟡 Practice — “Mirroring first” path (the safest)#

1. Step 0 — Scope: inventory tables, volume, consumers, freshness requirements, and security requirements. Choose a pilot scope (3 to 5 fact tables, 1 report).
2. Step 1 — Mirroring the SQL database to a “mirrored database” item; check prerequisites, data type limits, non-replicated tables, and specifics (primary key, supported DDL), which evolve: check up-to-date documentation (status 2026-10).
3. Step 2 — Direct Lake semantic model on the replica (or on a lakehouse via shortcut), pilot report in parallel with the existing one.
4. Step 3 — Number comparison: acceptance testing “old report vs. new report” on a fixed KPI set (see 11 — checklist).
5. Step 4 — Report cutover one by one, keeping the old Import model as backup for a defined period.
6. Step 5 — Decommissioning of what is redundant only once the coexistence period is validated.

🟡 Practice — “SaaS extraction to OneLake” path#

When the new part is the SaaS source (not the SQL database):

1. Land SaaS extractions as raw, timestamped, immutable in OneLake (bronze zone).
2. Standardize into silver (types, keys, deduplication, logical deletion handling).
3. Publish gold in a warehouse or lakehouse (star schema); the SQL datamart can remain the source for reference data (shortcuts or mirroring).
4. Let the SQL datamart consume gold, or retire it progressively.

🔴 Expert — Long-term coexistence and dual-source governance#

Example SQL-side reconciliation (to compare with the equivalent result on the replica's SQL endpoint):

-- Daily fingerprint of a fact table, comparable between source and replica
SELECT CAST([DateVisite] AS date)         AS [Jour],
       COUNT_BIG(*)                       AS [NbLignes],
       SUM(CAST([Montant] AS decimal(19,4))) AS [SommeMontant],
       CHECKSUM_AGG(CHECKSUM([ID_Visite])) AS [Empreinte]
FROM   [dbo].[FACT_Visite] WITH (NOLOCK)
WHERE  [DateVisite] >= DATEADD(day, -30, CAST(GETDATE() AS date))
GROUP BY CAST([DateVisite] AS date)
ORDER BY [Jour];

10. What drives the switch#

🟡 Practice — Switch thresholds (orders of magnitude, to calibrate)#

🔴 Expert — Reasons not to switch#

• The need is met and the team has no spare time: an architecture change without felt pain is an expense, not an investment.
• The data is small and reports are few.
• Fabric governance is not ready (domains, permissions, workspace lifecycle): migrating disorder to a lake creates a data swamp.
• Client or third-party reports are tied to existing models (measure IDs, links): the impact of a model change goes beyond the team. See 10 — measures and lineageTag.

11. Decision pitfalls#

12. AI contribution to the decision#

Prompt example: “Here are our five measurable requirements and the attached datamart schema. Propose three architectures, with for each one the risks, exit cost, and points I need to verify in the official documentation — without inventing pricing.”

13. See also#

• OneLake series: 01 — overview · 02 — architecture · 03 — difficulties and pitfalls · 04 — AI
• SQL Server series: 05 — overview · 06 — architecture · 07 — difficulties and pitfalls · 08 — AI
• Series continuation: 10 — cross-cutting good practices · 11 — project checklist
• Matrices: BI Development (cost, licensing, capacity: 12 — cost, licensing, and capacity ; performance: 03 — performance and data volume) · Data Project Management
• Methods and tools: CI/CD for data · VCS and SQL/BI data
• Glossary: GLOSSARY · Rating: RATING

✻ Written by Claude Sonnet 5.5