One trusted data layer for Dynamics 365, where the numbers reconcile

A Dynamics 365 organisation needs a data warehouse because reporting alone cannot reconcile numbers that live in different places and are computed differently by each team. A governed warehouse is one data layer where every figure is conformed and computed once, so Power BI, Excel, apps and AI all draw the same answer, rather than each team wiring its own extract off a live ERP that was built to run the business, not report on it.

The report is not the problem. It is what the report is built on.

Query Dynamics directly and you meet four things a report can never fix on its own. Sales sits in one place, revenue in finance, cost in service, and you end up defending three versions of the same number. Growth or an acquisition leaves you with several Dynamics, AX or NAV instances that no report can roll into one group view. The ERP is tuned for live operational state and overwrites as it goes, so the history you need to answer "how did this look last quarter" is simply gone. And running heavy analytical queries against the live system slows the very thing your business runs on.

A data warehouse solves all four at the layer underneath. It is one governed model, computed once, that many consumers query, the place multiple instances are consolidated, and the place history is kept that the ERP does not. Microsoft's own reference architecture frames it exactly this way: publish curated, governed data products that Power BI, Copilot, data agents and operational reporting all consume from one place.

That is the boundary worth being clear about. This warehouse is the data layer. The reports and dashboards sit on top of it. If your job is trusted Power BI reporting on Dynamics, that is the reporting layer on top of this warehouse, and it starts with the model this page builds.

Export to Data Lake for finance and operations apps has been retired by Microsoft, and BYOD is the legacy path Microsoft is moving customers off. The supported successors are Synapse Link for Dataverse and Link to Fabric. If your Dynamics data still flows through the old route, the forced move is the right moment to put a governed warehouse in place, rather than lift-and-shift the same unreliable data onto a new pipe.

If your Dynamics data pipeline was built a few years ago, there is a good chance it runs on Export to Data Lake. Microsoft deprecated that path in October 2023 and ended active use from November 2024. BYOD, the older pattern of pushing finance and operations tables into your own Azure SQL database, is still documented but explicitly positioned as a legacy route that Synapse Link and Link to Fabric replace.

The supported successors are Synapse Link for Dataverse, a managed continuous export of your Dynamics data including finance and operations tables, and Link to Fabric, a no-copy, no-ETL option where the data never leaves the Dataverse governance boundary and authorised users query it directly in Microsoft Fabric.

Here is the trap. Most organisations treat this as plumbing and lift-and-shift the same unreconciled data onto the new pipe. That is a repeated mistake. The migration you have to make anyway is the right moment to put a governed medallion warehouse in place, so you come out of it with a data layer that reconciles rather than the same mess arriving through a newer channel.

The modern Microsoft data warehouse for Dynamics 365 is built on Microsoft Fabric: your Dynamics data lands in OneLake as Delta Parquet, is organised through a medallion architecture into bronze, silver and gold layers, and is served from a Fabric Lakehouse or Warehouse that Power BI then reads directly through Direct Lake. It is the current-generation replacement for hand-built Synapse and Azure SQL warehouses.

The stack, in the order the data moves through it:

OneLake is the single logical data lake that comes with every Fabric tenant, storing all tabular data in an open Delta Parquet format. It holds one copy of the data, and shortcuts give zero-copy access across workspaces without moving anything.

Medallion architecture is Microsoft's recommended way to organise the warehouse: bronze for raw data as it lands, silver for cleaned and conformed data, gold for the curated, reconciled model that reporting and AI consume. This is the layering that turns a lake into a governed warehouse.

Lakehouse and Warehouse are the two Fabric engines, and many Dynamics builds use both. A Lakehouse handles ingestion and engineering; a Fabric Warehouse gives you a full relational warehouse with T-SQL, multi-table transactions and the star schemas finance reporting expects. Both store Delta in OneLake.

Direct Lake is the bridge up to the reporting layer. Power BI reads the Delta tables in OneLake directly into memory, with import speed and live freshness, no separate copy. It is how the governed warehouse feeds trusted Power BI reporting on Dynamics without a second extract.

Getting Dynamics data in is a solved problem. Finance and operations and Business Central data surface through Dataverse, and two Microsoft-native paths land it: Synapse Link for Dataverse for a managed continuous export, and Link to Fabric for the no-copy path where Dataverse generates a Lakehouse, SQL endpoint and dataset automatically and the data stays inside its governance boundary. Business Central lands the same way through Fabric.

One honest note, because buyers ask. Microsoft's Business performance analytics gives finance a ready-made Dataverse-to-Fabric star schema, and for a single-instance finance view it may be enough. It is a fixed finance model, though. It does not consolidate multiple instances or non-Dynamics sources into one governed model, which is exactly the job a bespoke warehouse does.

Building a Dynamics 365 data warehouse runs in three fundable steps: first a data health-check that proves the layer on one reconciliation that matters, then the governed build that consolidates your instances and keeps the history the ERP does not, then a managed data service that keeps it governed and current. Each step delivers something you can use before the next begins.

We do not sell a big-bang platform. We build in the sequence a finance or IT leader can actually fund: prove it small, extend it where it earns the right, then keep it running.

1. Prove the layer.

   Start where the pain is sharpest, usually one group figure or one instance that will not reconcile. We stand up a governed slice of the warehouse underneath it, prove the number holds back to Dynamics, and show you exactly where your data stands today and what the fix will cost before you spend anything on the build. It is deliberately small: the point is to prove the method on something that matters.

2. Build the governed warehouse.

   Extend that slice into the real thing: a conformed, reconciled gold model in Fabric that consolidates your Dynamics, AX and NAV instances into one group view, keeps the history and snapshots the ERP overwrites, and applies governance, lineage and security across every layer. This is the step that ends the three-versions-of-the-truth problem, and it is where a governed medallion warehouse gets built instead of the old extract being re-plumbed. If a pre-built route fits better, this is where the build-versus-buy call with Reveal is made (below).

3. Run it as a service.

   Move the warehouse from a project you finished to a data layer that keeps running. Managed data service means the refresh, the monitoring, the governance and the incident cover are someone's defined job, not one internal person's spare time. The layer stays governed and current as the business grows, and every consumer on top of it, reporting, Excel, apps and AI, keeps drawing the same trusted answer.

Each step stands on its own. Where you enter depends on how far your data has drifted, which is exactly what the health-check tells you. Talk to us about the right first step.

Most organisations should blend: buy a pre-built, reconciled foundation where speed and lower delivery risk matter, and build bespoke where the warehouse has to consolidate multiple instances or govern beyond a template. The scarce skill is not Microsoft Fabric, which is common, but Dynamics-native warehouse modelling, which is not. A short assessment shows you the gap so finance and IT decide from the same neutral read before any budget is committed.

It is a fair question, and it splits into two.

First, does your team have the Dynamics-native modelling skills to build the governed layer that reconciles? Fabric skills are common; the ability to model finance and operations or Business Central data so it consolidates and keeps reconciling as the business changes is scarce. This is not a local shortage. More than half of enterprise application leaders name a lack of skilled staff among their top challenges, and warehouse reliability is exactly the kind of work that stalls when the skill is not there.

Second, do you have the capacity to run it afterwards, month after month, without it becoming one person's fragile side project?

The strongest teams do not choose build or buy. They blend. And there is a productised option to weigh against a bespoke build: Reveal, our pre-built, pre-reconciled data-warehouse connector for Dynamics 365 Finance, Supply Chain and Business Central. It gives you a reconciled foundation faster and with lower delivery risk, supports AX and NAV migration, and comes with consultancy attached rather than a tool licence you are left to run. Where a bespoke governed build is the right call, we build that. Where Reveal gets you most of the way, we start there. The point is to decide with evidence, not guess.