128
Companies in recorded scope
Multi-ERP reporting router
ERP & group data architecture Internal work sample
One reporting engine brings comparable data from different accounting systems into a shared model. Configuration handles the differences between interfaces and companies.
-
The problem
Every company and accounting interface could have needed its own reporting queries. That would make each change harder to apply consistently across the group.
-
My role
I built one configurable reporting connection and a shared data model. Metadata selects the source, company and entity type for each request.
-
Where it stands
The documented engine covers roughly twenty entity types for 128 companies across three interface generations. The analytics API path remains beta; completed group consolidation is a separate milestone.
Many sources. One reporting route.
Follow a configurable request, then trace the records into the shared reporting model.
Select a layer or play the walkthrough.
AOne configurable request
BSource to reporting
Follow the highlighted route through each layer. Select any box to explore its other connections.
How does the same request reach different accounting systems?
Company and entity metadata choose the source-specific route. The result returns to a common reporting shape.
This is the documented routing method, not a live request. The Advanced Analytics path remains beta; the view does not claim a new census, refresh or production promotion.
The full view includes search, connected paths, presentation mode and export tools.
Read the map in plain English
Choose a part to see its purpose, its connections and supporting detail.
Resolve the request
Request company dataCompany and entity type
The caller requests a company and entity type through the shared function instead of maintaining a separate query for every source.
- To Resolve the metadataResolve source
Resolve the metadataConfiguration selects the route
Metadata identifies the company, source and entity type. Adding configuration avoids duplicating the reporting query for each company.
- From Request company dataResolve source
- To Legacy Web ServicesLegacy route
- To OData v2OData route
- To Analytics APIBeta route
Select the source interface
Legacy Web ServicesSource-specific handling
The router accommodates the legacy Web Services interface while keeping its differences behind the shared function.
- From Resolve the metadataLegacy route
- To Apply the common shapeRetrieved records
OData v2Another supported interface
The OData v2 route handles its interface differences within the same reporting engine. Consumers do not need a second reporting model for this source.
- From Resolve the metadataOData route
- To Apply the common shapeRetrieved records
Analytics APIBeta path
The Advanced Analytics interface is a beta path in the documented implementation. Stabilisation and promotion remain separate work.
- From Resolve the metadataBeta route
- To Apply the common shapeBeta records
Return a shared reporting shape
Apply the common shapeComparable data for the model
Source-specific handling returns data for the shared model. API differences stay contained behind the router.
- From Legacy Web ServicesRetrieved records
- From OData v2Retrieved records
- From Analytics APIBeta records
- To Load the reporting modelConformed output
Load the reporting modelConformed dimensions and facts
The output feeds a conformed star schema so reporting can use consistent measures across the connected sources.
- From Apply the common shapeConformed output
How do separate records become comparable reporting?
Records pass through the shared router into conformed facts and dimensions, then measures and reports. A separate anomaly check identifies patterns for investigation.
The documented scope is a reporting connection and model. It does not establish signed group consolidation, and an anomaly flag is not proof of an accounting error.
The full view includes search, connected paths, presentation mode and export tools.
Read the map in plain English
Choose a part to see its purpose, its connections and supporting detail.
From source records to reporting
Accounting recordsDifferent source interfaces
The source systems hold the records. Their interface differences are handled by the router rather than repeated in every reporting query.
- To Shared routerRetrieve
Shared routerSource, company and entity
The parameter-driven function resolves the request from metadata and returns the selected source records through a maintained path.
- From Accounting recordsRetrieve
- To Conformed modelConform
Conformed modelFacts and dimensions
The shared TMDL star schema gives records a consistent reporting structure. Source access and the reporting model are separate layers.
- From Shared routerConform
- To Consistent measuresModel context
- To Leading-digit checkGL fact patterns
Consistent measuresOne reporting definition
Reports use measures built on the conformed model. The purpose is comparable reporting rather than separate calculations for each accounting interface.
- From Conformed modelModel context
- To Reporting consumersReport
Reporting consumersA common basis for analysis
Consumers use the shared reporting model. Financial sign-off and any consolidation adjustments retain their own decision boundaries.
- From Consistent measuresReport
Investigate patterns
Leading-digit checkPatterns worth investigating
A Benford leading-digit check on the general-ledger fact identifies unusual patterns for follow-up. It does not determine that a transaction is wrong.
- From Conformed modelGL fact patterns
- To Investigate the flagReview the signal
Investigate the flagReturn to source evidence
A flagged pattern needs investigation against the underlying records and business context. The check supports review rather than an automatic accounting conclusion.
- From Leading-digit checkReview the signal
Results and evidence
~20
Recorded entity types
3 reconciled
Interface generations
Explore the detail: my role, approach and source notes
What the work involves
Resolve the right source
One parameter-driven function resolves source, company and entity type from metadata. Source-specific handling covers legacy Web Services, OData v2 and an Advanced Analytics beta API, while the caller uses the shared route.
Source notes
One maintained reporting engine across accounting systems.
Added companies and entity types through configuration rather than new queries. Isolated API differences behind the router so source changes remain contained.
Business impact
Turns fragmented ERP access into one maintainable, auditable source for group reporting.
How I did it
- 01
Loaded the output into a conformed TMDL star schema for consistent measures.
- 02
Added a Benford leading-digit anomaly check to the general-ledger fact.
Tools and working context
The documented implementation covers 128 companies across legacy Web Services, OData v2 and an Advanced Analytics beta API. These figures describe the recorded scope, not a new live census.
- Tools
- Power Query (M) · TMDL · DAX · OData · Python
- Business area
- Group reporting · Data architecture
- Skill type
- Data & Reporting · ERP & Systems
Why I made these choices
- Added companies and entity types through configuration rather than new queries.
- Isolated API differences behind the router so source changes remain contained.
Skills used
- Forensic analytics (Benford's Law) Data & Reporting Applied
What I learned
- Source decoupling lets the group add companies and absorb API changes without rebuilding reports.
What comes next
- Promote the beta API path after stabilisation and extend anomaly controls.
← All case studies · Discuss this work →
How the reporting works
Bringing 128 company ledgers together.
Select a step to see how source data becomes reports people can use. The figures come from the linked case studies.
Sources Data comes from 128 operating companies through three generations of accounting-system interfaces: legacy Web Services, OData v2 and an Advanced Analytics beta. Each source has different formats that can change over time.
Connect One reporting engine uses configuration to select the company, source system and type of data. Adding a company means updating that configuration; changes to an interface are handled in one place.
Organise A shared data model makes calculations consistent across companies, currencies, scenarios and periods. A Benford check looks for unusual first-digit patterns in ledger amounts.
Use Group reports, board papers and acquisition analysis use the same controlled model, so each view starts with consistent numbers.