Multi-site reporting
Every site records things differently and my BI team rebuilds reports by hand. How do we get one version of the numbers?
Last updated
Agree what each measure means, fix master data and the keys that link jobs, customers and sites, then feed the field system and ERP automatically into one governed Power BI semantic model. Each regional manager gets a filtered view of the same report through row-level security, so the BI team maintains one model instead of rebuilding thirty reports every month.
Who this is for
This page is for BI leads, finance systems staff and operations managers in multi-site organisations across Australia and New Zealand, in field services, wholesale and distribution, logistics, construction, facilities and professional services. It assumes operational data sits in a field system such as simPRO, Uptick or AroFlo, financials sit in an ERP or ledger such as Business Central, Pronto Xi, NetSuite, SAP or Xero, and reporting is done in Power BI or Excel.
Solve8 does this work in phases: discover one report at one site with a measured baseline, prove a governed version beside the current report, build the feeds and model on your own infrastructure in Australia, then run it with you or hand it over. Solve8 is based in Brisbane and works with clients in Sydney, Melbourne, Perth and across Australia and New Zealand, with workshops on site.
Why the numbers differ between sites
| Cause | What it looks like | Where to fix it |
|---|---|---|
| Different codes per site | One branch books callouts to a service job type, another to a project type, so head office cannot compare revenue by work type | Agree one code list and map each site's codes to it |
| Inconsistent master data | The same customer exists three times in simPRO and twice in Business Central with different names and IDs | Clean the records, pick a system of record for each entity and add a shared key |
| Manual exports | Someone downloads a CSV each month, filters it and pastes it into a workbook, and the cut-off date changes each time | Replace exports with scheduled feeds from the source systems |
| Spreadsheet logic | Gross margin, utilisation or job completion is calculated differently in each regional workbook | Write each measure once in the semantic model and retire the local formulas |
| Timing differences | The field system shows a job as complete before the invoice posts in the ERP, so operations and finance disagree at month end | Define which date each measure uses and show both where it matters |
The order that works
Agree the definitions
Write down what each measure means, which system it comes from and which date it uses: revenue, gross margin, jobs completed, first-time fix, utilisation, debtors. Get operations and finance to sign the same list. Most rebuilt reports exist because nobody agreed these.
Fix master data and keys
Choose a system of record for customers, sites, jobs, assets and cost centres. Give every record a key that both the field system and the ERP carry, such as the ERP customer number held against the customer in simPRO, so the data joins without manual lookups.
Automate the feeds
Pull jobs, timesheets, materials and invoices from simPRO, Uptick or AroFlo, and the ledger, debtors and cost data from Business Central, Pronto Xi, NetSuite, SAP or Xero, on a schedule through their APIs or a staging database. No monthly CSVs.
Build one governed model
Put the agreed measures into one Power BI semantic model that report authors build on. Microsoft documents shared semantic models across workspaces, with Build permission and certification, so report authors can tell which model is the official one.
Give each manager a filtered view
Use row-level security so each regional or site manager sees their own rows in the same report. Microsoft notes that row-level security applies to Viewers, not to workspace Admins, Members or Contributors, so managers should get Viewer access or an app.
Retire the old reports
Run the new view beside each old report for a cycle or two, reconcile the differences, then switch the old one off so two versions do not survive.
Where AI helps and where it does not
Mapping inconsistent codes
AI can propose a mapping from hundreds of site-specific job types, cost codes or customer name variants to the agreed list, with a confidence score. A person approves each mapping.
Matching duplicate records
AI can suggest which customer or site records across simPRO and Business Central are the same. Merges still happen in the system of record.
Flagging anomalies
AI can flag a site whose margin or job volume moves outside its normal pattern, or a feed that arrived incomplete, before managers see it.
Where it does not help
AI cannot decide what a measure means, settle a disagreement between operations and finance, or fix a missing key. Without agreed definitions it produces inconsistent numbers faster.
Start with one report and one site
Pick the report that causes the most rework, often the monthly regional pack, and one representative site. Measure how long it takes today and how often its numbers are questioned, then agree definitions and fix keys for that report only.
Run the governed version beside the existing report until the site manager and finance accept that it reconciles, then add sites in batches and the next report on the same model. Each phase has a fixed fee and you can stop after any phase. If the real issue is the field system to ERP integration, fix that first.
Limits and when not to do this
Statutory consolidation is separate
A governed management model does not replace eliminations and group consolidation controlled by an accountant. See the multi-entity month-end close answer for that.
Definitions stay contested
If leaders will not agree what a measure means, building the model will not settle it. Escalate the decision first.
Row-level security is not column security
Row-level security limits which rows a person sees. Hiding columns such as cost or pay rates needs object-level security or a separate model.
Who should own it
BI lead
Owns the semantic model, the measure definitions in it, refresh schedules and row-level security roles.
Finance systems lead
Owns ERP master data, the chart of accounts mapping and the finance side of each key.
Operations manager
Owns field system data entry rules, job types and site codes, and signs off operational measures.
Applications or integration owner
Owns the feeds from simPRO, Uptick, AroFlo and the ERP, and alerts when one fails.
Sources
Microsoft Learn, Row-level security (RLS) with Power BI: https://learn.microsoft.com/en-us/fabric/security/service-admin-row-level-security
Microsoft Learn, Introduction to semantic models across workspaces, Power BI: https://learn.microsoft.com/en-us/power-bi/connect-data/service-datasets-across-workspaces
Microsoft Learn, Semantic models in the Power BI service: https://learn.microsoft.com/en-us/power-bi/connect-data/service-datasets-understand
Questions
Should mismatched job numbers and customer names between simPRO and Business Central be fixed in the integration or the data model?
Fix the keys at the source and carry them through the integration, so a simPRO job and customer always hold the matching Business Central reference. Use the data model only to map historical records and codes that cannot be corrected. Mapping tables in Power BI work as a bridge, but if new records keep arriving without keys, the mapping never ends.
Do regional managers each need their own Power BI report?
Usually not. One report built on one semantic model, with row-level security that filters rows by the manager's region or site, gives each manager their own view while the BI team maintains a single report. Managers who need extra pages can build on the shared model with Build permission rather than copying the data.
Can AI clean up inconsistent site codes and customer names?
AI is good at proposing matches between code lists and spotting likely duplicate customers across systems, and it saves a lot of manual comparison. It should propose, with a person approving each mapping and each merge. The approved mappings then live in a governed table so the same decision is not made twice.
Do we need a data warehouse before building one Power BI model?
Not always. A few sources with clean keys can feed a Power BI semantic model directly. Once you have several field systems, an ERP, history to keep and many sites, a staging database or warehouse makes refreshes reliable and keeps the mapping tables in one place. Decide based on the number of sources and the volume of history.
How do we stop sites drifting back to their own spreadsheets?
Make the governed report faster and more trusted than the spreadsheet, retire the old report once the new one reconciles, and give managers a clear way to request a new measure. Data entry rules in the field system, such as required job types and site codes, stop the drift at the source.
Who should sign off metric definitions across sites?
Operations and finance leaders should sign off together, with the BI lead recording each definition, its source system and the date it uses. Publish the list where report users can see it, and change a definition only through the same sign-off so every site moves at once.
Related
- Data, BI and analyticsOne version of your numbers.
- We run several entities across Xero, MYOB and Business Central. How do we close month-end faster and get one group report?How groups on Xero, MYOB and Business Central close month-end faster: intercompany matching, one consolidation and AI drafts an accountant checks.
- We key jobs from simPRO, Uptick or AroFlo into Pronto, Xero or MYOB by hand. How do we stop the double entry?How to stop rekeying jobs from simPRO, Uptick or AroFlo into Pronto Xi, Xero or MYOB: which connectors exist, their limits, and where AI helps.
- System integrationConnections between the systems you run.
Have this problem across your branches?
A 30-minute review with a senior consultant. You leave with a written one-page summary you can share internally.
Fixed-fee phases, agreed before we start. Stop after any phase.