Why Your Dataverse Model Is Producing Wrong Numbers, and How to Fix It in Production

What does a Dataverse data model producing wrong numbers look like?

It rarely announces itself as a data model problem. It arrives as three complaints from three departments that nobody connects. Finance says the pipeline or revenue figure in the dashboard does not match their system and cannot explain the gap. Operations says month-end jobs, recalculations or exports run for hours and sometimes fail. Customers say the portal list of their orders or cases times out.

Each complaint gets a local fix: a manual adjustment in the board pack, a longer timeout, a smaller page size. The fixes hide the common cause, which is how the tables and relationships were modelled and how the reports and queries walk them. The table below maps the symptom to the pattern worth checking first.

How do many-to-many relationships double count in reports?

A native many-to-many relationship in Dataverse stores each link as a row in a hidden intersect table. That is fine for saying "this opportunity involves these products" or "this account belongs to these segments". It becomes a reporting problem the moment a query joins through that intersect table and then sums a value that lives on the parent record. An opportunity linked to three segments appears as three rows, and its estimated value is counted three times.

Nobody writes that bug on purpose. It appears when a report author adds a segment filter or a segment column to an existing revenue report, in a FetchXML view with a link-entity, in Power Query when a related table is expanded, or in a Power BI model with a bidirectional relationship across a bridge table. The number looks plausible, so it survives until finance compares it with their own.

The second failure is uncontrolled growth: many-to-many relationships added for every new grouping idea, several of them between the same two tables, with no owner deciding which one is authoritative. When the link needs data of its own, such as quantity, role or a primary flag, a custom junction table is the better model; our guide to linking multiple products to a single case sets out that choice in detail.

• Check every report that sums a parent value while filtering or grouping by something reached through an intersect table.
• In Power BI, keep the bridge table out of the path of parent measures, or allocate the value explicitly, for example to a primary segment only.
• List all many-to-many relationships per table and name an owner and a purpose for each; retire the ones nobody can explain once nothing reads them.

Why do hierarchies queried with Under slow everything down?

Dataverse lets a table have a self-referential relationship marked as hierarchical, such as parent account or parent case, and FetchXML offers hierarchy operators such as under, eq-or-under, above and eq-or-above to query it. They are convenient: "all opportunities for accounts under this group" becomes one condition. They are also expensive when the hierarchy is deep or wide, because the platform has to resolve the whole tree before it can apply the rest of the query.

The cost multiplies when the operator sits in a view used by many people, a portal list filter, a plug-in running on every update, or a month-end process that runs the query once per top-level account. Circular or accidental parents, such as an account set as the parent of its own ancestor through an import, make the tree larger than anyone thinks. Microsoft documents limits and behaviour for hierarchical queries that change over time, so check the current documentation rather than relying on a remembered number.

• Find where the operators are used: saved views, portal lists, FetchXML in plug-ins, flows and reports.
• Measure the actual depth and breadth of the tree and look for loops and orphaned branches before tuning anything.
• Where reports only need the top of the tree, store it: a "group account" lookup maintained on each record turns a hierarchy walk into a simple filter.

When do calculated, rollup and formula columns become the problem?

They behave differently, and most wrong numbers come from treating them as the same thing. Calculated columns and formula columns are evaluated when the row is retrieved, so the value is current but every view, export and report that selects them pays the calculation cost on read, including across thousands of rows. Rollup columns are stored values recalculated by asynchronous system jobs on a schedule, or on demand, so they are cheap to read but can be stale, and a mass recalculation competes with everything else in the environment.

At small volume none of this is visible. At scale it produces exactly the month-end symptoms: a report run straight after a large import shows totals that the next scheduled rollup job will change, a process that reads calculated columns across a large table slows down, and chains of calculated columns that reference other calculated columns multiply the work. Check the current Microsoft documentation for the limits and recalculation behaviour in your environment, because both are revised.

What should you fix first to stop the bleeding?

Give the business one set of numbers it can rely on before touching the model. Changing relationships while finance is still arguing about the board pack adds a moving target to a dispute. A read facade is a layer that consumers read from, which presents correct figures while the tables underneath are still wrong, and which later lets you change those tables without every report breaking.

Choose the lightest facade that answers the disputed questions. Whichever you choose, write down the definition it implements, because that definition is what finance will sign off later.

How do you measure what duplicates are doing to each report?

Quantify before you merge anything. Merging in Dynamics 365 moves child records onto the survivor and there is no unmerge, so a merge done to fix one report changes history in every other report too. The work that comes first is measurement: run duplicate detection jobs with rules tuned to real match keys, such as registration number, normalised email or website domain, and export the candidate groups.

Then count the impact per report rather than per table. For each disputed figure, how much of the value sits on records that belong to a duplicate group, and would merging move it between regions, owners or periods? That turns "we have duplicates" into a list of which merges change which board numbers, which is what finance and the data owners need to approve. The matching rules, reversible merge batches and alternate keys that follow are covered step by step in our duplicate data cleanup guide; this page does not repeat them.

How do you remodel a live Dataverse system without a change freeze?

Change alongside, never in place. The pattern is sometimes called expand and contract: add the new structure next to the old one, fill it, move consumers across one at a time, and remove the old structure only when nothing reads it. Every step is small, deployable through your normal managed solution pipeline, and reversible until the last one. Users keep working throughout.

What cannot safely be changed in place in Dataverse?

Some decisions are fixed when a table, column or relationship is created, and others are technically possible but break things you cannot see from the maker portal. These are the changes that need the alongside pattern above rather than an edit. Where the platform behaviour is revised, confirm it against current Microsoft documentation before planning around it.

How do you prove the numbers are right to finance?

Agree control totals per period with finance before the first change, then run them after every change. A control total is a figure both sides can produce independently: invoiced revenue per month, count of active contracts at period end, value of orders per region. Where revenue is recognised in a finance system, that system owns the number and the CRM model reconciles to it. Solzet integrates Dynamics 365 with finance systems rather than implementing them, so this reconciliation is always done with your finance team.

• Write each control total down with its definition, filters, currency, time zone and owner, and record the agreed figure for several closed periods.
• Take the same totals from the facade before any remodelling, and explain every difference in writing before changing anything.
• Re-run the totals after each backfill batch, each consumer switch and each merge batch, and keep the results as evidence.
• Do not retire the old structure until at least one full close has reconciled on the new model.
• Give disputed figures an "as at" time on every report, so timing differences are not mistaken for model errors.

Is a rebuild or a different platform the better answer?

Usually not. A model producing wrong numbers is fixable in production, and a remediation that keeps the system running is cheaper and less risky than a rebuild that has to reproduce years of data, integrations and user habits before it delivers anything. If the model sits alongside slow forms, failing integrations and nobody who understands the solution, start with an independent Dynamics 365 health check and technical audit so the remediation is scoped against the whole environment.

We recommend the right solution - whether that's Microsoft Dynamics 365, Power Platform, or a custom-built CRM. Some businesses need the Microsoft ecosystem. Others need full control without licensing. We deliver both. Where the audit shows that Microsoft licensing or the platform boundary is the underlying problem rather than the model, a custom CRM on React, Node.js, PostgreSQL or .NET gives you direct control of the database schema and its reporting replicas.

How does Solzet help remediate a Dataverse data model?

We work in the order of this page. First the diagnosis: which reports, processes and portal pages are affected, which of the four patterns cause it, and the agreed control totals. Then the read facade, so the business has numbers it can use this month. Then the duplicate impact per report, and the incremental remodel behind that facade, released through your pipeline one consumer at a time with the reconciliation evidence kept for finance.

Solzet delivers remotely from Yerevan, Armenia, with senior consultants and full-stack developers and 8+ years of Dynamics 365 Customer Engagement and Power Platform work, directly or white-label for Microsoft partners.