Solution Icon

Solutions

Cutting-edge solutions tailored to everyday issues in the modern digital environment.

Services icon

Services

Innovative tools and services designed to address complex challenges today’s digital landscape.

IT FOR What WE DO MENU

Aerial view of an industrial facility at sunset

Services

Hexagon is reshaping industries through smarter systems, streamlined workflows, and bold digital innovation.

IT FOR HOW WE DO MENU

Aerial view of an industrial facility at sunset

Services

Hexagon is reshaping industries through smarter systems, streamlined workflows, and bold digital innovation.

Ready to clean up your CRM workflow?

Book a CRM Workflow Audit and see where leads, follow-ups, reporting, or disconnected tools can be fixed first.

Data Integration Guide

Data Warehouse Reconciliation: Make Metrics Explainable

How we make warehouse numbers traceable, from the metric definition to the checks that run on every load.

  • Data Integration
  • Data Warehousing
  • Updated October 2026
Dark cover with the words Make Your Metrics Explainable in white and blue text

Data warehouse reconciliation is the routine that proves a dashboard number matches the records and rules behind it. We start with one agreed metric definition, a declared grain, and layered checks that run on every load. A faster platform or more data will not settle an argument about what a number means.

Why do two dashboards show different numbers?

Finance, sales, and operations often pull from the same systems and still report different totals. In most cases nobody made a mistake. They are counting different events, cutting off on different dates, or treating cancellations and credits in different ways.

A warehouse project should bring those differences into the open before it turns one version into the company number. If it skips that step, it simply automates the argument. This guide is the process we propose to clients. It is a method, not a report of results from a specific project.

Which metric should you reconcile first?

Pick one business decision with a named owner. For example, an operations lead may need to see orders that shipped but were never invoiced. That is easier to test than a goal like "one source of truth."

Write the definition down. Name the business event, the records that count, the states that are excluded, the date boundary, the currency, and how corrections are handled. Name the one person who can change that definition. Keep booked value, invoiced value, and collected cash as separate measures instead of folding them into one word like revenue.

Then build a short set of test cases with agreed answers: a canceled order, a partial shipment, a credit, a reopened record, and a transaction that crosses the month-end cutoff. These cases become acceptance tests for both the pipeline and the dashboard.

What is grain, and why does it break totals?

Grain is what one row in a fact table stands for. An order, an order line, a shipment line, and a payment are four different grains. Microsoft's fact table guidance describes this as the level at which facts are defined, and notes that it is hard to split measures to a finer level later.

Mixed grains are the most common cause of inflated totals we see in reporting reviews. One order can have several lines, several shipments, and several payments. Join them without care and the amounts multiply.

So we write the grain in plain words before anyone designs a report. For a fulfillment question, the fact might be the shipment line, with the source order line kept as a reference. Invoices sit in their own fact and are compared through a documented rule. Then we test joins with awkward cases and confirm that adding a customer or region filter does not change the total.

What load evidence should the warehouse keep?

Every number should be traceable back to where it came from. Keep the source system, the source record ID, the extraction time, the business event time, and the version of the transformation that produced it.

Each load also needs a clear boundary. Record the extraction window, the rows received, accepted, and rejected, and whether the run finished. If a job fails halfway, the next run must know whether it is continuing, replacing, or replaying that batch. Stable keys stop a replay from creating duplicate facts.

Handle deletes and corrections on purpose. A pipeline that only adds new rows can leave canceled orders in a metric for months. Traceability does not mean keeping every field forever, though. Limit personal data, restrict access, and set retention with the teams that own it.

How should history behave when records change?

Customers change territory. Accounts change owners. Products move between categories. For each important attribute, decide whether a report should show the value at the time of the event or the value today.

Microsoft's dimension table guidance covers the two common patterns. Type 1 overwrites the old value and keeps no history. Type 2 inserts a new dated version of the row, and the guidance says to reserve it for attributes where the historical view matters.

Use Type 2 only where a decision needs it. Tracking history on every field adds work without better answers. When users need both views, label them as two separate measures so a hidden filter never changes the meaning.

How do you reconcile in layers?

We check in four layers, in this order:

  • Completeness. Did the expected source window arrive in full?
  • Structure. Are required IDs present, types valid, states known, and relationships intact?
  • Business totals. Do counts and amounts match the agreed source report at the same cutoff?
  • Slices. Do they still match by business unit, date, currency, and status?

A matching grand total is not proof on its own. Missing positive and negative records can cancel each other out. That is why the slice check matters.

Keep a reconciliation log with the check name, batch, expected and observed values, tolerance, owner, and resolution. Every tolerance needs a business reason. A rounding tolerance is fine. A tolerance that hides missing rows is not. When a difference is accepted, say why in the log. If unexplained differences keep returning, the metric should not be labeled as trusted.

How do readers know the data is current?

A dashboard can load perfectly while showing stale data. Show the time of the last completed load and the reporting cutoff, not just the time the page opened. Mark partial loads clearly.

Agree in advance what happens when a check fails. The dashboard might show a warning, keep the last accepted result, or hide a sensitive metric until someone reviews it. Also watch for late records. A backdated transaction can change last month even when today's load is healthy, so decide whether closed periods are restated, locked, or adjusted separately.

What does a good first release include?

A strong first release covers one decision, one approved definition, traceability from source to report, automated checks, named owners for exceptions, and a tested recovery step. Track open reconciliation items, freshness misses, manual fixes, and how long it takes to explain a disputed number.

Expand only after the business can use and explain that first metric. The same discipline applies to the feeds that load the warehouse. Our guide to partner API acceptance tests covers how to prove those feeds recover from failure, and our API integration services page explains how we connect source systems.

Frequently asked questions

Why do two dashboards show different totals from the same data?

Usually because of different date cutoffs, included statuses, grains, join logic, or correction rules. Compare the definitions and a few sample records before blaming either tool.

Is a matching total enough to validate a warehouse load?

No. Errors can offset each other. Check counts, IDs, relationships, and key slices as well as the total, all at the same cutoff.

Does every attribute need full history tracking?

No. Keep versions only where a business question needs the view at the time of the event. Overwriting is simpler and fine for most fields.

Should the first warehouse release cover every department?

Usually not. One valuable metric with an accountable owner is easier to validate. Prove reconciliation and support on that, then expand.

Sources

  1. Microsoft Learn: Dimensional modeling in Microsoft Fabric Data Warehouse, fact tables
  2. Microsoft Learn: Dimensional modeling in Microsoft Fabric Data Warehouse, dimension tables
  3. Microsoft Learn: Slowly changing dimension type 2

Two reports that disagree?

Bring both reports and the decision that depends on them. We will map the definitions, the grain, and the checks that make the number explainable.

BUSINESS SYSTEMS

SOFTWARE

AI

INTEGRATIONS

Build, Improve, or Connect the Systems Your Business Depends On.

Whether you need custom software, CRM, ERP, AI, automation, integrations, or modernization, tell us what you’re trying to improve. We’ll review your requirements and recommend the most practical next step.

300+

Software projects delivered

37+

Enterprise apps built

150+

delivery team members

★★★★★ 4.7 on Google · 20 reviewsU.S.-led since 200721 awards & certifications →
Client results
BMG: cut the time lost on follow-ups by 70% with our CRM.
Utah Dental Association: convention registration streamlined from months to one month.

Tell Us About Your Project

No sales pressure. Tell us what you’re trying to build, improve, integrate, automate, or replace. We’ll review your answers and come back with a clear recommendation within 1 business day.

Prefer to talk now? Call +1 (801) 259-4800 or book a free 30-minute review.

© 2026 Hexagon IT Solutions. All rights reserved.