Solution pattern · reference architecture
Governed ERP and finance data mart.
This pattern moves selected ERP and finance data into a governed reporting layer. Accounts, company codes, and posting logic remain traceable while Power BI uses agreed models instead of local exports.
Starting point
Month-end reports are assembled from repeated exports and conflicting definitions.
Finance data is exported from SAP or Dynamics 365 into spreadsheets, manually amended, and reconnected for each report. Differences between accounts, entities, and reports are difficult to trace.
Business value
An agreed data model connects close logic and management reporting.
Finance owners define measures and mappings once in a traceable form. Source, processing, and report usage remain reviewable without replacing the ERP as the system of record for postings.
What becomes measurable
- Data freshness, load failures, and reconciliation differences by source
- Manual adjustments between ERP extract, data model, and report
- Coverage of accounts, entities, and reports by approved definitions
Reference architecture
Components and their purpose
The architecture protects the source ERP, separates raw data, and publishes only business-approved finance models to Power BI.
- 01
Define the ERP interface
A supported and licensed extraction route is selected for SAP or Dynamics 365. Direct database access is not assumed as a convenient shortcut.
- 02
Ingest data under control
Azure Data Factory and, where required, Self-hosted Integration Runtime load selected tables, APIs, or extracts within documented windows.
- 03
Retain the raw snapshot
Azure Data Lake Storage Gen2 and Delta Lake record source state, load time, and technical keys for repeatability and review.
- 04
Harmonise finance logic
Azure Databricks maps charts of accounts, company codes, cost centres, currencies, and periods according to approved rules.
- 05
Publish the data mart
Delta tables and Databricks SQL provide a documented Gold layer with business definitions and appropriate access roles.
- 06
Connect reports and lineage
Power BI uses an agreed semantic model. Unity Catalog, load logs, and report documentation keep the path back to the source visible.
Technology
Concrete services for implementation
The selection is adapted to existing contracts, regions, security requirements, and the actual scope.
- Azure Data Factory
- Self-hosted Integration Runtime
- Azure Data Lake Storage Gen2
- Azure Databricks
- Delta Lake
- Unity Catalog
- Databricks SQL
- Power BI
First project scope
A pilot needs clear boundaries
The first deployment tests data, integration, and the working process in a limited area. It is not a premature enterprise rollout.
Deliberately included
A bounded finance process for one entity, an agreed ERP extraction route, the required master and transaction data, and an existing report as the business comparison.
Deliberately excluded
No ERP replacement, full group consolidation, migration of all historical data, simultaneous rebuild of every Power BI report, or unvalidated real-time posting.
Prerequisites and constraints
Technology does not replace data accountability
Data access, responsibilities, licences, and operations must be clear before implementation. Open points are treated as project risks.
- 01SAP or Dynamics interfaces, extraction licences, and permitted load windows must be clarified before the design starts.
- 02The chart of accounts, entity structure, currencies, and close rules need a named business owner.
- 03Historical changes and manual posting logic can limit comparability and must be documented as rules or exceptions.
- 04Finance, ERP administration, data engineering, Power BI owners, data protection, and information security must decide together.
Germany and the EU
Compliance follows the specific purpose
Finance data can contain information about employees, customers, or suppliers. GDPR, commercial and tax retention duties, segregation of duties, and audit-ready logs must be considered together. Processing and backups should be planned in suitable EU regions and verified contractually. If reports can reveal individual employee performance or behaviour, co-determination under the Works Constitution Act must be clarified. Without an AI component, the data mart itself does not trigger obligations under the EU AI Act.
Free initial assessment
Which finance report creates the most reconciliation questions today?
The discovery call frames ERP access, definitions, report usage, and a viable pilot boundary.
The initial consultation and joint use-case discovery are free and non-binding.
Related use cases
Data reconciliation for month-end close and controlling
ERP, accounting, banking, and specialist systems use different identifiers. Reconciliation remains trapped in spreadsheets, emails, and personal comments.
Compliance and audit evidence retrieval
Evidence sits in tickets, policies, training lists, approvals, and emails. Currency, completeness, and provenance are difficult to see.
Real-estate data hub for operating costs and portfolio reporting
Property, lease, energy, and invoice data use different identifiers. Reports are assembled manually and anomalies surface late.