Automating Manual Reporting Across Dynamics 365 and a Second ERP Without a BI Team

Why does joining Dynamics 365 and the ERP inside a report keep failing?

Because the report becomes the integration. When a Power BI file or a spreadsheet pulls from both systems and matches them on the fly, every mapping rule, currency conversion and calendar adjustment lives inside that one file. The next report repeats the logic slightly differently, refreshes break when either system is slow, and two reports built a month apart disagree for reasons nobody can find.

A shared layer moves that logic out of reports and into one place that is loaded, tested and documented once. Reports then read a single agreed version of customer, period and amount. The report design itself, including star schemas and date tables on Dataverse data, is covered in our Power BI for manufacturing guide; this page is about what sits underneath when there are two systems.

To be plain about scope: Solzet integrates with your existing finance or ERP system and reports across it. We do not implement or configure that system.

What should the canonical reporting layer look like?

Thin, boring and owned. It holds only the entities and measures your reports need, in conformed shapes, with nothing clever hidden in it. Three common options work for a mid-market team without a data platform; the right one depends on what you already run, your Microsoft licensing and who will support it, so treat the table as a starting point and check current Microsoft documentation for product capabilities.

• Raw zone: data as extracted from each system, unchanged, with lineage columns.
• Conformed zone: customer, product, period and currency dimensions shared by both sources, plus facts such as orders, invoices, payments and pipeline in agreed shapes.
• Semantic model: one Power BI semantic model over the conformed zone with the metric definitions, used by every report.
• If Dataverse queries are already hitting limits, the analytical copy options are explained in our guide to Dynamics 365 reporting limits.

Which keys always break when you consolidate CRM and finance data?

The same three, in almost every consolidation. Each needs an explicit mapping table in the layer, maintained by a named person, rather than a lookup buried in a formula.

• Match customers on deterministic identifiers such as the finance account number stored on the CRM account, registration or VAT number. Our guide to consolidating customer data into one source of truth covers the matching method.
• If Dataverse itself is producing wrong numbers because of its data model, fix that first; see Dataverse data model remediation.

How do you prove lineage when an auditor asks where a number came from?

Make every row able to answer the question on its own. An auditor, or a finance director challenging a board figure, needs to go from the number to the rows behind it, to the source records, to the load that brought them in, and to the rule that transformed them.

• Source system and source record ID on every row in the raw and conformed zones.
• Extract timestamp: when the data was read from the source, not when the report refreshed.
• Load batch ID linking every row to a load log with row counts read, loaded and rejected.
• Transformation documented in version control: each mapping and calculation as code or a documented step, with the change history.
• Metric definitions written in plain language beside the semantic model measures, with the owner and last approved date.
• An "as at" timestamp shown on every report page, so nobody reads yesterday's numbers as today's.

How can you get a first consolidated number in weeks rather than quarters?

Pick one metric, one period and one sign-off. The first release is not a dashboard suite; it is a single figure that finance agrees is right, produced by the layer rather than by hand. Revenue by customer for the last closed month, or pipeline against invoiced revenue for one region, are typical choices.

Can the finance team keep working in Excel?

Yes, and they should not have to copy and paste to do it. Excel can connect directly to the governed Power BI semantic model, through Analyze in Excel or by inserting a PivotTable from a Power BI semantic model, so finance builds its own pivots on the same measures every report uses and refreshes them instead of pasting exports. Where a flat table suits them better, publish a governed table or view from the layer and connect Excel to that.

Check the current Microsoft requirements for Excel connectivity to semantic models, including licensing and tenant settings, before promising it. What matters for trust is that the spreadsheet reads the model; nobody edits the numbers and sends the file back as the source.

What governance rules keep the reporting layer trustworthy?

A handful of rules, written down and actually followed, matter more than tooling. Most consolidated reporting loses trust because one unexplained difference appears and nobody owns the answer. When dashboards already disagree with finance, our guide to reconciling dashboard numbers with finance walks through finding the cause.

• One owner per metric definition, usually in finance, who approves any change to it.
• Change control: mapping, calendar, currency and measure changes go through version control and a test load before production.
• Reconciliation checks after every load: row counts and control totals per source compared with the source, failures alerted rather than silently published.
• No ad-hoc edits in the layer. Corrections are made in the source system or in a mapping table with an audit trail, never by updating rows directly.
• Unmapped customers and rejected rows are visible on a data quality page with an owner who clears them.
• Reports use the shared semantic model; new private models over the same data need a reason.

How does build-and-handover work for a team with no BI function?

The build is sized so the people who inherit it can run it. We build the layer, loads, mappings, semantic model and first reports, and document them as we go; your team learns by doing the monthly routine with us before doing it alone.

• Build: extraction from both systems, the conformed layer, lineage, reconciliation checks, the semantic model and the first metric.
• Shadow: one or two month-end cycles run together, with your team clearing unmapped rows, approving mapping changes and checking reconciliation.
• Handover: a runbook for the monthly cycle, the metric catalogue with owners, and a change procedure for adding a metric.
• After handover: your team runs the routine; support for new sources or measures can be agreed as needed rather than kept by default.
• The skills needed internally are an analyst comfortable with Power BI and Excel and a finance owner, not a data engineering team.

Is a Microsoft reporting stack the right choice for consolidated reporting?

Usually, when Dynamics 365 is already the CRM and Microsoft 365 is in daily use, because the connectors, security and Excel integration are already there. It is less compelling if the CRM itself is the misfit, for example when licensing for the users who need it does not work, or the business wants the CRM and reporting database in one system it controls.

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 that is the case, a custom-built CRM on React, Node.js, PostgreSQL or .NET can sit on a database designed for reporting from the start, integrated with the same finance system.

How does Solzet help consolidate reporting across Dynamics 365 and a finance system?

We start with the metric that costs the most manual effort each month, agree its definition with finance, and deliver it from a thin canonical layer with lineage and reconciliation, then add metrics on the same pattern and hand the routine over to your team. Power BI and Power Platform delivery is described on our Power Platform development page. If an undocumented integration between CRM and finance is part of the problem, see our guide to untangling a black-box finance integration.

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.