A Dynamics 365 data warehouse that reconciles, and that you own

Point reporting straight at the raw Dynamics 365 transactional tables and it reconciles by luck, not design. Dump Dataverse into a bronze-only lake and the same number means something different in every report. A build engineers a governed, conformed dimensional model at a curated gold layer, a Fabric Warehouse for the SQL-first reconciled finance layer, a Lakehouse for medallion ingestion, commonly both, so the financial-dimension, elimination and multi-entity logic is encoded once and computes the same way every time.

The build exists because Dynamics 365 is a transactional system. Its tables are shaped to run the business, not to report on it. The logic that turns raw transactions into a board-grade number, financial dimensions rolled up in the right combination, intercompany eliminations, reporting-tree hierarchies, multi-entity consolidation across differing charts of accounts, lives in a layer above the transactions. Skip that layer and the numbers drift.

A build lands that logic in a medallion architecture: bronze holds the Dynamics data exactly as it arrives, the preserved source of truth; silver standardises, de-duplicates and matches records across systems; gold is the conformed star-schema layer the reporting tools consume. The finance logic is implemented once, in the silver-to-gold transform, and every downstream tool inherits the same clean shape.

Fabric Warehouse. An enterprise relational warehouse on a data-lake foundation, T-SQL-first, with full multi-table ACID transactions, stored procedures and materialized views. The right target for a SQL-first team and a governed, reconciled finance warehouse, and the shape enterprise Power BI semantic models expect to sit on.

Fabric Lakehouse. Spark-first, holds structured and unstructured data, ingests via notebooks, pipelines, dataflows and shortcuts. The right target for data engineering and the medallion ingestion and transformation. The common pattern lands and transforms bronze and silver in a Lakehouse, then exposes the curated gold layer through a Warehouse, one estate on shared OneLake storage, not two copies.

Where the estate, governance posture or existing investment calls for it, the target is Azure SQL or Synapse instead, and the dimensional-modelling principles carry across.

This is where a Dynamics-native builder shows. The conformed model uses surrogate keys so history and consolidation hold even when business keys clash across instances, and slowly changing dimensions so re-mapping a legal entity or cost centre does not silently rewrite prior-period history. A Power BI model alone cannot produce SCD type 2 history, it needs a pre-loaded SCD table from a warehouse layer, which is a concrete reason the warehouse build sits beneath the BI build rather than being replaced by it.

For the decision itself, laid out plainly, see the data warehouse programme this build sits inside.

A Dynamics 365 data warehouse build runs as a phased delivery: discovery and architecture, extraction and ingestion, the conformed warehouse model, pipelines and incremental load, governance and handover, then run-state readiness. It is delivered incrementally so each phase lands something you can use, land the finance domain first and prove it reconciles before extending, not a two-year data-platform transformation, and it is scoped and fixed before we start.

A build reads as credible when it matches the shape the market has learned to expect, a phased medallion delivery, and then does the one thing a generic Fabric build cannot: make the gold layer reconcile to your Dynamics finance source. Here is the shape, and each phase delivers before the next begins.

1. Discovery and architecture

   We scope the build against your current estate and the exact figures that have to reconcile, and choose the target deliberately, Fabric Warehouse, Lakehouse, the common combination, or Azure SQL where the estate calls for it, rather than defaulting.

2. Extraction and ingestion

   We land your Dynamics data on a supported Dataverse path, Azure Synapse Link for Dataverse or Link to Microsoft Fabric, into the bronze layer, handling the Finance and Operations ingestion details that quietly corrupt a generic builder's source layer.

3. Warehouse model

   We build the medallion transform, bronze to silver to gold, and engineer the conformed star schema at the gold layer, with surrogate keys and slowly changing dimensions, so the financial-dimension, elimination and multi-entity logic is encoded once and history is preserved.

4. Pipelines and incremental load

   We orchestrate the ELT with pipelines and set-based T-SQL, load incrementally on a high-watermark or change-tracking basis rather than truncating and reloading, and log every run, so the warehouse scales and stays supportable.

5. Governance and handover

   We put the warehouse under Git source control, set up deployment pipelines across dev, test and production, document the model and its grain, apply row-level security, and name an owner, so the estate is governable and survives the person who built it.

6. Run-state readiness

   We hand over documented and monitorable, so you can run it yourself, or ladder cleanly into the Managed data service if you would rather we ran the run-state.

A fixed-scope Fabric-readiness accelerator is available beside the full build where speed matters and the scope is tight. Whichever shape fits, the cost is scoped and shown before you commit.

A build lands on a supported Dataverse path, either Azure Synapse Link for Dataverse, where data is exported to your own storage in Delta and Parquet and you own the pipeline, or Link to Microsoft Fabric, where the data stays inside the Dataverse governance boundary with no copy and no ETL. The path is chosen to your governance and Fabric posture, using the FastTrack Fabric Link transition tool to preserve your existing data shapes, never on the retiring Export to Data Lake pipe.

The path underneath the warehouse is where a real Finance and Operations builder separates from a generic Fabric shop, because it is a live decision with a hard deadline attached, and because the details are where a build ships clean or subtly wrong.

There are two supported paths, and the right one depends on how you want to own the pipeline.

Azure Synapse Link for Dataverse. Data is exported continuously to your own storage account in Delta and Parquet, compressed to a fraction of the original size, with incremental ingestion. You own and can extend the pipeline. This is the direct successor to the retiring export, and the right path for IT and data teams who want to own their own pipeline and feed a bronze layer they control. You can also connect an existing Synapse Link to Fabric to gain Direct Lake and Fabric pipelines without re-ingesting.

Link to Microsoft Fabric. A no-copy, no-ETL, fully managed integration. The data stays inside the Dataverse governance boundary while authorised Fabric users query it through OneLake shortcuts, effectively a read-replica of your Dynamics data in Fabric with an auto-generated Lakehouse and SQL endpoint. This is the fastest route to a bronze layer, the path to leverage Fabric without building and running export pipelines yourself.

Under both, the Finance and Operations details decide whether the model reconciles. Change tracking has to be enabled for custom tables, deleted rows are retained and flagged rather than removed so they must be filtered, staging and temporary tables are excluded, and certain system tables refresh on a slower cadence. A build that plans for these produces a bronze layer that ingests clean. One that does not produces one that looks right and poisons everything above it.

Where a pre-built, pre-reconciled foundation fits the job better than a bespoke build, Reveal is our productised option, which reduces implementation time and risk.

A build consolidates multiple Dynamics 365, AX or NAV instances into one group warehouse, mapping the differing charts of accounts to a consolidated one and removing intercompany in the silver-to-gold transform, all encoded in the conformed model so the group numbers reconcile. And the migration off the retiring pipe is the single most valuable moment to build that model right, not re-plumb a broken one onto the modern stack.

These are the two most concrete reasons a warehouse build gets commissioned, and they are the same motion: fix the model while you re-plumb the plumbing.

Consolidation. Growth or an acquisition leaves several Dynamics, AX or NAV instances that all have to reconcile into one group number. A build maps the differing charts of accounts to a consolidated one, removes intercompany before the group total is struck, conforms the shared dimensions, and encodes it all in the gold layer, so the group view holds every time, rather than a set of separate extracts someone stitches together in Excel each month. A multi-entity manufacturer we consolidated into one group warehouse is a typical shape of this work.

Migration. Most organisations lift-and-shift the same unreliable extracts onto the new data path and bake the problem in. The migration off the retiring export, off BYOD, or off an ageing on-prem SQL warehouse, is the moment to put a governed conformed model in place instead, so you land on the supported path with reporting that reconciles, not the old broken reporting on new pipes. The FastTrack Fabric Link transition tool preserves your existing data shapes to minimise the move, and legacy AX, NAV and on-prem SQL sources migrate into the same modern medallion target. Where a D365-to-SQL migration fits a productised route, Reveal powers exactly that motion.

A Dynamics 365 data warehouse build delivers a governed warehouse or lakehouse on a supported Microsoft architecture, Dynamics ingestion on a supported Dataverse path, a conformed dimensional model that reconciles to your finance source, incremental pipelines, governance and CI-CD, and a documented handover to a run-state you own.

Here is the whole of it, so there is no question about what lands.

• Governed target warehouse or lakehouse. A Fabric Warehouse or Lakehouse on OneLake, medallion-layered bronze, silver and gold, or Azure SQL or Synapse where your estate calls for it, chosen deliberately to your team and workload.
• Ingestion on a supported data path. Synapse Link for Dataverse or Link to Fabric feeding the bronze layer, with the Finance and Operations ingestion details handled, built on a supported path, not a retiring one.
• A conformed dimensional model that reconciles. A star schema at the gold layer with surrogate keys and slowly changing dimensions, where the financial-dimension, elimination and multi-entity logic is encoded once, so the group number reconciles by design.
• Pipelines and incremental load. Data Factory pipelines and set-based T-SQL, incremental fact loads on a high-watermark or change-tracking basis, and ETL logging, so the warehouse scales and stays supportable.
• Governance and CI-CD. Git source control, deployment pipelines across dev, test and production, and row-level security, giving you a governed, change-managed, AI and Copilot-ready gold layer.
• Handover to a run-state you own. A documented model with a named owner, ready to run yourself or ladder into the Managed data service if you want the run-state handled.

The build is the middle rung. The flagship or the advisory conversation can feed in first, so you decide and settle build-versus-buy before you build, and the Managed data service can follow, so you build then hand off the run-state. Once the warehouse is built, the Power BI semantic model and reports sit on top of it. You choose the next rung on the evidence, with no obligation.

The build is the middle rung of a three-step ladder. You choose each rung on the evidence.

1. Decide first.

   If you are still weighing the programme or the build-versus-buy question, start with the data warehouse programme, which produces the business case for the build.

2. Build.

   This page: the governed, conformed, reconciled data layer, delivered incrementally so each phase lands something usable.

3. Then run it.

   When the warehouse is live, talk to us about the managed data service for the run-state, handled.

The Power BI boundary. We build the warehouse, the governed data layer. The Power BI semantic model and the dashboards sit on top of it, and we build those too, on the Power BI build that sits on the warehouse. Two cleanly separated builds, one team.