How can we combine data from multiple business systems into one dashboard?

Why do reports from different departments disagree?

A finance report, CRM pipeline and warehouse screen can all be correct while answering different questions. Finance may count posted invoices; sales may count orders placed; operations may count goods dispatched. Their dates, cancellation rules and treatment of VAT can differ. Putting the three figures next to each other does not resolve those differences. The first task is to explain what each number represents.

Start with a meeting about one recurring business decision. For example, a managing director might want to know whether growth in orders is creating profitable revenue or an expanding delivery backlog. That question establishes the records needed, the time period and the people who should review exceptions. A dashboard becomes useful when it connects the sequence from enquiry to order, fulfilment and payment without pretending those events are interchangeable.

What should the first combined dashboard contain?

Choose a small set of measures that tell a coherent story. Revenue, gross margin, overdue debt, order backlog and delivery performance may be a useful starting set for a distributor. A professional services firm may need utilisation, project margin and unbilled work instead. Avoid importing a standard KPI pack simply because a template contains attractive charts. Each measure needs a person who can explain its meaning and act on a change.

Write down the audience, decision and expected action beside every proposed visual. If a director sees overdue debt rise, should they investigate individual invoices, speak to account managers or change credit decisions? The drill-through detail should support that action. A dashboard with six useful measures and dependable supporting records is often a better first release than twenty measures whose definitions are still disputed.

Which systems and records should we inventory first?

Create a source register before discussing connectors. Record the product and version, hosting location, business owner, reporting access, available history and how often records change. Include spreadsheets that hold budgets, targets or mappings, because they may influence decisions as much as the main accounting application. Identify whether each spreadsheet is a controlled input or an unofficial copy of another system.

Ask for sample records that include difficult cases, such as a cancelled order, partial delivery, credit note and renamed customer. These reveal more about integration requirements than a clean demonstration dataset. Keep personal information out of early design samples wherever possible, and arrange appropriate access when production validation becomes necessary.

Should we connect directly or build a central reporting store?

A small pilot can sometimes connect Power BI directly to supported sources and perform the necessary transformations in a reporting model. That may be sufficient when there are few systems, modest data volumes and predictable refresh requirements. It is still a designed data pipeline: credentials, transformations, relationships and failures need ownership. Direct connection does not mean the source applications share a consistent data model.

A central reporting store becomes worth assessing when several reports repeat the same extraction work, history must survive source changes or different teams need shared prepared data. It can also isolate reporting activity from operational applications. The additional storage, engineering and support have a cost, so avoid treating a warehouse as mandatory for every SME. Describe the limitations of the simpler approach and the evidence that would trigger a move.

Microsoft's Power BI modelling guidance distinguishes business events from descriptive entities and explains when more extensive data preparation may be appropriate. NetMonkeys' system integration services provide a relevant starting point when source access and data movement need to be designed together.

How do we match customers, products and departments?

Use stable identifiers wherever possible. A customer's display name is a weak join key because spelling, punctuation and trading names change. Maintain an explicit mapping between source IDs and the reporting customer record. Give unmapped records an exception status rather than silently excluding them. The report owner should be able to see both the unmatched count and the financial value involved.

Agree how mappings change over time. If a customer moves from one sales region to another, should last year's sales remain with the old region or move into the new structure? Either approach can answer a useful question, but they cannot be mixed without explanation. Similar issues arise when departments merge or product categories change. Keep a change log and decide who can approve amendments to the reporting view.

How do we avoid double-counting when joining systems?

Define the level of detail in each table before creating relationships. An invoice line, order line and delivery line are not necessarily one-to-one. Joining a single order value to three deliveries can multiply the apparent revenue unless the calculation accounts for the relationship. Reconcile both row counts and monetary totals before adding charts. A total that looks plausible is not an adequate test.

Test the dashboard at several levels: company total, month, customer and individual transaction. Include orders with split shipments and invoices with multiple lines. Separate facts that describe different events and connect them through agreed dimensions where appropriate. An analyst should be able to explain why a filter changes a result and which records contribute to it. That explanation matters more than the number of visuals on the screen.

What does a practical KPI definition look like?

A useful definition includes the business meaning, formula, source, date basis, exclusions and owner. For gross margin, explain which costs are included, how credits are treated and whether the figure is provisional before month end. For overdue debt, define the reference date and whether disputed invoices remain included. Keep the definitions close to the dashboard so users do not need to find a separate technical document.

Definitions will evolve as the business learns what it needs. Version them rather than replacing them informally. If a calculation changes, tell users which comparisons are affected and whether historical periods have been restated. This prevents an apparent improvement from being mistaken for operational progress when it is actually a reporting change.

How frequently should the dashboard update?

Set freshness requirements around decisions. Daily refresh may be sufficient for a leadership overview; a dispatch team may need a shorter interval for today's work. The relevant delay includes source entry, extraction, preparation and presentation. A report can refresh successfully while still displaying yesterday's upstream feed, so show the source cut-off as well as the refresh timestamp.

Microsoft documents different Power BI refresh behaviours for different storage modes. Select an approach after testing the complete route. Avoid promising real-time reporting merely because a source has an API. Record the accepted delay, what happens after a failed load and who decides whether a stale dashboard remains available with a warning.

How should sensitive information be protected?

Start with the minimum detail each audience needs. A director may need overall payroll cost without employee-level salaries; a sales manager may need regional margin without unrestricted access to all customer transactions. Separate business requirements from technical convenience. Do not grant broad workspace editing rights simply to make report access easier, and test access using representative viewer accounts.

Include exports and subscriptions in the access design. A protected dashboard can still create uncontrolled copies if users routinely download detailed spreadsheets and circulate them. Decide which exports are legitimate, how recipients are managed and where approved reporting packs are retained. Review access when roles change and remove dependencies on the account of a single employee who happened to build the first report.

How can we prove the numbers are trustworthy?

Run a parallel validation period against agreed source reports. Log differences, investigate them and record the resolution. Do not force the dashboard total to match through an unexplained adjustment. A difference may reveal a source timing issue, missing company, mapping problem or a genuinely different definition. The owner needs to understand the cause before accepting the result.

Keep a compact test pack that can be repeated after changes. Include a known month, several edge-case transactions and access checks. Retain expected results with the reporting specification. This gives the team a repeatable way to assess an application upgrade or revised calculation. Trust develops when users see discrepancies resolved openly and understand the boundaries of the information they are using.

What should the delivery plan look like?

Use stages with clear decisions: agree the business question, prove source access, validate the model, review a prototype, complete user acceptance and introduce support. Each stage should produce something reviewable. Source access is a particularly useful early gate because API limits, unavailable fields or missing history can materially change the design before much visual development has taken place.

Choose one accountable business sponsor and a small working group from the affected departments. Arrange time for them to review definitions and exceptions. A project can stall even when the technical work is progressing if finance cannot review reconciliation or sales cannot approve customer mappings. Plan those activities as part of delivery, with a clear escalation route when a decision is overdue.

What mistakes should an SME avoid?

Avoid beginning with every system and every department. Expanding the scope before proving one useful decision increases the number of unresolved definitions and relationships. Equally, avoid a pilot that depends on repeated manual exports if the stated goal is automated reporting. Temporary manual steps can be sensible, but name them, measure the work and decide how they will be replaced.

Another mistake is measuring success only by dashboard views. A popular dashboard may still leave employees rebuilding the figures in Excel because they cannot trace the totals. Measure reconciliation effort, time spent preparing recurring reports and whether decisions are made with fewer conflicting versions. Ask users which questions remain unanswered. Improvement comes from removing uncertainty as well as reducing preparation time.

How do we keep the dashboard useful after launch?

Treat it as an operating service with a business owner, technical owner and change process. Monitor refresh failures, source schema changes, credential expiry and unresolved data exceptions. A monthly review can check whether the measures are still useful and whether users have created workarounds. Document the support route so the person preparing a board meeting knows what to do when a number looks wrong.

Review the cost and complexity of additional requests before adding them. A new measure may require a new data source, historical restatement or access model. Make those implications visible. Keep a backlog ranked by decision value rather than seniority alone, and retire unused pages where appropriate. A well-maintained dashboard should become easier to understand as the reporting service matures.

What would a worked cross-system example look like?

Suppose a distributor wants to compare invoiced revenue, delivery performance and outstanding orders. Start with three separate facts: invoice lines, delivery events and order lines. An order can create several deliveries and several invoices, so combining the raw records in one flat table risks multiplying values. Agree which measure each fact supports and use controlled customer, product and date mappings to connect the analysis. This is a hypothetical design exercise, not a description of a NetMonkeys client.

Choose one order that was delivered in two parts and invoiced in different periods. Finance should confirm the posted invoice value, operations should confirm quantities dispatched and sales should confirm the remaining order quantity. Then filter the report by customer and month. The expected result is not necessarily the same number in every panel. It is a consistent explanation of the three stages, with no accidental duplication and a traceable route back to the source records.

Add a credit note and a cancelled undelivered line to the test. Decide whether delivery performance follows the original promised date or a revised agreed date, and whether that revision is recorded historically. Document the decision before generalising the calculation. A single carefully chosen example can reveal a requirement that would otherwise remain hidden until management challenges a trend. Keep the approved example as part of the reporting test pack.

What should a reporting handover contain?

Ask for a source inventory, a plain-language measure dictionary, mapping ownership and a description of the refresh route. Include the location of project files, the approved way to change credentials and the support contacts for connected applications. A new operator should be able to identify why yesterday's data is still displayed without having to reverse-engineer every transformation. Document the most likely failures and the evidence needed to investigate them.

The business handover matters too. Explain which measures are provisional, which comparisons have limitations and where users can see unresolved exceptions. Demonstrate how to drill from an overview to a transaction and how to report a suspected discrepancy. Give managers a short practice task using a known example rather than relying entirely on a presentation. Record questions that reveal ambiguity and update the guidance before the reporting service is considered ready.

Finally, agree a first operating review after the team has used the dashboard through a normal reporting cycle. Check support demand, refresh reliability, mapping exceptions and the work users still perform outside the system. That review gives the business a practical opportunity to correct the design while the original decisions are still fresh. It also establishes who will own future improvements and how they will be prioritised.

How can NetMonkeys help combine our reporting?

Bring a sample of the reports you currently prepare, the systems involved and a description of the decisions that are difficult today. Include examples of conflicting figures and the time spent reconciling them. That evidence supports a more useful conversation than a request for a dashboard with a fixed number of pages. Agree what the first release must prove before expanding.

NetMonkeys' Power BI services can support a discussion about reporting requirements, connected data and ongoing ownership. Where financial and operational records sit in ERP, its Business Central services are also relevant. Ask any proposed partner to explain scope, data responsibilities, acceptance checks and support so the dashboard has a dependable foundation.

What should a new report owner receive?

Hand over the source inventory, measure definitions, access model and recovery instructions together. A successor should understand which feed to investigate when a total changes, which business owner can approve a correction and where the last reconciled results are recorded.