Microsoft Fabric and Business Central: A Reporting Blueprint

Almost every Business Central reporting project I inherit starts the same way: someone connected Power BI straight to the OData endpoints, it worked for a month, and then history grew, a version upgrade shifted a field, and the whole thing quietly stopped reconciling. Fabric changes what is possible, but only if you pick the right landing path and build the medallion layers with intent. This is the blueprint I use for asset-heavy operators who need cost-per-asset that finance and maintenance both trust.

The three ways BC data actually reaches Fabric

There are exactly three paths that hold up in production. Everything else is a variation on one of them. I will walk each, then give the practitioner verdict on throughput, how it handles deletions, and what it does the day someone runs a BC version upgrade.

1. The Dataverse / Synapse Link path

If your Business Central is SaaS and you have the Dataverse virtual tables or the newer Link to Fabric surface turned on, you can project BC entities into Dataverse and then link them into a Fabric lakehouse with near-zero copy. Microsoft has been folding the old Azure Synapse Link plumbing into a native "Link to Fabric" experience, so what you configure today is a managed shortcut rather than a pipeline you own.

Verdict: lowest maintenance and the most future-proof, because Microsoft owns the incremental sync. Throughput is fine for ledger-grade volumes but it is not built for you to reload ten years of G/L in one shot. Deletions propagate cleanly because change tracking rides underneath. On a BC version upgrade it is the safest option, since the entity contract is abstracted; the risk is that a table you depended on gets reshaped in Dataverse and you find out at refresh time. This path is evolving fastest, so confirm which BC tables are actually projectable in your tenant before you design on it.

2. The API / OData extraction path

This is the classic: BC exposes standard and custom API pages plus OData v4 endpoints, and you pull them with Data Factory or a notebook. It is the most portable option and the only one that works uniformly across SaaS and on-premises. It is also the one people abuse the most.

Verdict: highest control, lowest ceiling. Throughput is throttled by the BC service tier and API bucket limits, so a naive full pull of Value Entry will crawl and can trip rate limits. Deletions are the real trap: a plain OData read only returns rows that currently exist, so a deleted or corrected entry silently vanishes from your extract with no tombstone. And a BC version upgrade can rename a field, change a page, or retire an endpoint, which breaks the extract with no warning until the pipeline errors. Usable, but only with the watermark discipline I describe later.

3. Direct lakehouse landing via Data Factory

Here you use a Fabric Data Factory pipeline (or a Dataflow Gen2) to land BC data straight into the bronze layer of a lakehouse as Delta tables, on a schedule you control, driven by change-tracking or modified-date columns. It sits between the other two: more work than the managed Link, more robust than raw OData.

Verdict: my default for asset-heavy estates that need custom transforms. Throughput scales because you batch and parallelise per table, and you can stage a heavy historical backfill separately from the daily delta. Deletion handling is whatever you build, which is both the burden and the point: you can implement soft-delete detection with a change-tracking companion query. On a BC upgrade you are exposed to schema drift, but because you own the mapping you can pin column names and fail loudly rather than silently, which is exactly what you want.

For most single-tenant BC estates I land core ledgers via a Data Factory pipeline into bronze and let Link to Fabric cover the slower-changing dimension and master tables. That split gives me control where volume and history matter and low maintenance where they do not. If you are still deciding the wider platform sequence, my Fabric adoption roadmap covers where reporting sits relative to the rest of the estate.

The BC tables that actually matter

You do not need the whole BC schema. For an asset-heavy operator, cost and maintenance reporting comes down to a handful of ledger and dimension tables. Get their grain, volume, and refresh cadence right and everything downstream is arithmetic. Get the grain wrong and every number double-counts.

Two things to flag. Dimension Set Entry looks small conceptually but it is the highest-fan-out table in the model, because every posting can carry several dimension values and each becomes a row keyed by Dimension Set ID. And Value Entry, not G/L Entry, is where your real cost-per-unit lives for inventory and consumables, so if your maintenance parts run through inventory that is the table you reconcile against, not the general ledger summary.

Worked example: cost-per-asset that finally reconciles

Here is the problem every asset-heavy operator hits. Finance knows the cost in BC by dimension. Maintenance knows the work in the CMMS by work order and asset. Nobody can join the two, so cost-per-asset is a guess. In the silver layer of Fabric you can fix this properly. The bridge is a shared dimension: BC posts maintenance cost against a dimension (Asset, Cost Center, or Equipment), and the CMMS records work orders against the same asset code. If those codes agree, the join is trivial. If they do not, that reconciliation gap is the first thing to fix, and it is usually a data-governance problem, not a Fabric one.

The flattening trick that everyone gets wrong the first time is Dimension Set Entry. In BC, a ledger row does not store "Asset = PUMP-014" directly. It stores a Dimension Set ID, an integer that points at a set of dimension values held in Dimension Set Entry. So one G/L Entry row with Dimension Set ID 4471 might expand to three rows in Dimension Set Entry: DEPARTMENT = FM, COSTCENTER = C200, ASSET = PUMP-014. People try to join Dimension Set Entry directly to the ledger and get a three-times row explosion, then wonder why total cost tripled.

The correct pattern is to flatten Dimension Set Entry into one row per Dimension Set ID before you join. You pivot the dimension codes into columns, so set 4471 becomes a single row with columns Department, Cost_Center, Asset. Then you join that flattened bridge back to G/L Entry on Dimension Set ID one-to-one, and no fan-out occurs.

-- silver: flatten dimension sets to one row per set id
CREATE OR REPLACE TABLE silver.dim_set_flat AS
SELECT
    dimension_set_id,
    MAX(CASE WHEN dimension_code = 'ASSET'      THEN value_code END) AS asset_code,
    MAX(CASE WHEN dimension_code = 'COSTCENTER' THEN value_code END) AS cost_center,
    MAX(CASE WHEN dimension_code = 'DEPARTMENT' THEN value_code END) AS department
FROM bronze.dimension_set_entry
GROUP BY dimension_set_id;

-- silver: BC maintenance cost by asset, joined one-to-one (no fan-out)
CREATE OR REPLACE TABLE silver.bc_cost_by_asset AS
SELECT
    f.asset_code,
    SUM(g.amount) AS maintenance_cost
FROM bronze.gl_entry g
JOIN silver.dim_set_flat f
    ON g.dimension_set_id = f.dimension_set_id
WHERE g.gl_account_no IN (/* maintenance expense accounts */)
GROUP BY f.asset_code;

-- silver: reconcile against CMMS work-order history on the shared asset code
SELECT
    c.asset_code,
    c.maintenance_cost               AS bc_cost,
    w.wo_count,
    w.labour_hours,
    c.maintenance_cost / NULLIF(w.wo_count, 0) AS cost_per_work_order
FROM silver.bc_cost_by_asset c
LEFT JOIN silver.cmms_wo_history w
    ON c.asset_code = w.asset_code;

Now cost-per-asset reconciles because both sides land on the same grain: one asset code, cost from BC, work volume from the CMMS. Rows where BC has cost but the CMMS has no work order are your ghost charges, usually a miscoded dimension. Rows where the CMMS has work but BC has no cost are unposted or uncaptured spend. Both lists are worth their weight in gold at budget time, and neither existed before the join. Whether this silver logic lives in a lakehouse or a warehouse is a separate decision I unpack in lakehouse vs warehouse in Fabric.

Why OData will not survive a full history reload

State it plainly: Business Central's own OData endpoints will not survive a full history reload, and you should never design as if they will. Two reasons. First, throughput. A full read of Value Entry or G/L Entry over the API is paged, throttled by your BC service tier, and competes with live users, so a genuine multi-year backfill can take hours and will intermittently hit rate limits and time out mid-run. Second, correctness. A plain OData query returns only currently existing rows, so any entry that was deleted, reversed, or corrected is simply absent, and you have no tombstone to reconcile against. Reload the whole thing and you get a snapshot that silently disagrees with last month's snapshot, with no audit trail of why.

The pattern that does survive is the incremental watermark. You store the high-water mark of the last successful load, and on each run you pull only rows changed since then. BC exposes two levers for this: the systemModifiedAt column that every table carries, and the change-tracking / delta capability on API pages. The recipe:

1. Keep a small control table in the lakehouse: one row per source table holding last_watermark (a UTC timestamp) and last_run_status.
2. On each run, read that watermark and query BC with a filter of systemModifiedAt gt {last_watermark}, ordered ascending, paging through the delta only.
3. Land the delta into bronze, then MERGE into the silver Delta table on the primary key so updates overwrite and inserts append. This is where Delta's upsert earns its keep.
4. For deletions, run a periodic change-tracking pull (or a reconciliation count of primary keys) and soft-delete the missing keys with an is_deleted flag rather than a physical delete, so history stays auditable.
5. Only after a clean MERGE do you advance the watermark to the max systemModifiedAt in the batch. If the run fails, the watermark does not move and the next run safely re-pulls the same window.

That last point is the whole discipline. The watermark advances only on success, so a failed load is idempotent: rerun it and you get the same result, no gaps and no duplicates. A one-time historical backfill is a separate, chunked job you run once against a quiet window, after which the daily watermark takes over forever. For the Power BI modelling that sits on top of this, I go deeper in Business Central and Power BI.

A refresh-and-cost model for a typical BC estate

Here is how I size a single-tenant BC estate so nobody is surprised by the Fabric capacity bill. Assume one BC company, the seven tables above, a few million ledger rows growing by tens of thousands a day. The refresh design:

The cost lesson is that Fabric capacity (an F SKU, measured in capacity units) is billed for what your jobs consume, so the design choices above are cost choices. A daily incremental delta on a single-tenant estate sits comfortably inside a small capacity because you never re-read history. The two things that blow the budget are unnecessary full reloads and running heavy transforms more often than the business actually looks at the numbers. If nobody reads the report before 8am, do not refresh it at midnight and again at 6am; refresh it once. Direct Lake mode on the gold semantic model then serves Power BI straight off the Delta files with no separate import refresh to pay for.

One honest caveat on the SKU names and licensing: Microsoft renames and re-tiers Fabric capacity, Direct Lake, and Link to Fabric features on a fast cadence. Treat the shapes here as durable and the exact SKU labels as perishable, which is why this article carries a verification date.

Where to start

Pick your landing path by who should own the sync: Link to Fabric if you want Microsoft to own it, Data Factory landing if you need control over volume and history, and never raw OData for anything that must survive a reload. Model the seven tables at the right grain, flatten Dimension Set Entry before you join, and drive everything off a watermark that only advances on success. Do that and cost-per-asset stops being an argument between finance and maintenance and becomes a number both of them trust.

References: Dynamics 365 Business Central documentation · Dataverse Link to Fabric