Unifying Multi-ERP Data Across an Acquired Portfolio with Microsoft Fabric Medallion Architecture
A US-based building performance and energy transition company had grown through a series of strategic acquisitions, bringing together more than 20 specialized engineering, sustainability, and energy services businesses under one portfolio. Each acquired brand operated its own ERP platform. SAP in one business unit, Oracle in another, Microsoft Dynamics across others, and a range of industry-specific systems throughout the rest. As the portfolio expanded, the data infrastructure fell further behind. There was no consolidated view of financial performance, project margins, or energy metrics across the business, and no reliable way to build one. Every reporting cycle required analysts to manually extract data from more than 20 separate systems, reconcile figures built on inconsistent definitions, and produce a consolidated view that was already outdated by the time it reached leadership. Revenue, gross margin, and project completion rates were each calculated differently depending on which subsidiary produced the report. With no central data warehouse and no shared semantic layer, cross-brand comparison was unreliable. Each new acquisition added another ERP source with no standard integration path and no reusable framework to absorb it. OptiSol designed and delivered a unified data platform on Microsoft Fabric, implementing a Medallion architecture across Bronze, Silver, and Gold layers to consolidate all ERP sources into a single governed analytics foundation. A unified Power BI reporting layer was built on top, giving leadership a single authoritative view across all brands for the first time. Month-end reporting time dropped by 60%. The platform is architected so that each new acquisition connects to the existing framework in days rather than months of bespoke integration work.
Key Outcomes
Challenges and Solutions
No Single Source of Truth Across 20 Independently Operated ERP Systems
Each acquired subsidiary operated its own ERP platform with its own schema, naming conventions, and master data definitions. There was no automated consolidation across brands and no common framework for cross-portfolio comparison. Analysts spent the majority of each reporting cycle extracting and reconciling data by hand across more than 20 systems, with no way to verify that figures produced by one team matched what another team had independently calculated from a different source the same week.
OptiSol built a parameterized ingestion framework using Fabric Data Pipelines, extracting from all ERP source systems into a Bronze stage layer with full data fidelity preserved. Full-load and incremental-load strategies were configured per source, with Change Data Capture applied to high-frequency transactional systems. On-Premises Data Gateway connections were established for ERP platforms running in hybrid environments. Every pipeline includes automated monitoring, alerting, and data quality validation at ingestion time. Onboarding a new acquisition into the platform now takes days rather than months of bespoke integration work.
Inconsistent Metric Definitions Across Every Brand in the Portfolio
Without a central data warehouse or shared semantic layer, each subsidiary built its own reporting using a combination of ad-hoc scripts, spreadsheets, and isolated tools. Revenue, gross margin, and project completion rate were each calculated differently depending on which brand produced the report. When two business units presented conflicting numbers for the same KPI in the same leadership meeting, there was no governed layer to resolve it. Strategic decisions on resource allocation and portfolio performance were being made on data that could not be trusted.
OptiSol built a Silver layer that cleanses, standardizes, and conforms data from all ERP sources, applying master data management to harmonize entity names, fiscal year definitions, currency normalization, and cross-system identifiers across all brands. A fully materialized Gold layer followed, with purpose-built Fact and Dimension tables for financial performance, project profitability, workforce utilization, and energy KPIs. Consistent business definitions were enforced so that revenue, margin, and project completion rate carry the same meaning regardless of which ERP system originated the data.
Fragmented Power BI Workspaces with No Governed Reporting Layer
Power BI was in use across multiple subsidiaries, but each team had built their own workspace connected to their own source data. Reports measuring the same KPIs used different calculations and different underlying datasets. Dashboards ran slowly because they queried operational ERP databases directly. There were no enterprise-level views available for C-suite or board reporting. When reports from two brands produced different answers to the same question, there was no authoritative layer to resolve the conflict.
OptiSol replaced all disconnected workspaces with governed Power BI semantic models built on the Gold layer in OneLake, establishing a single source of truth for all reporting across the portfolio. Row-Level Security was configured so that brand-level users access only their own data while corporate leadership has a full consolidated view. Automated refresh schedules aligned to pipeline completion ensure dashboards always reflect the latest available data without manual intervention. Leadership can access any financial or operational KPI on demand, with every team across every brand working from the same underlying numbers.
No Reusable Integration Framework as the Acquisition Pipeline Continued to Grow
Each new acquisition triggered a bespoke integration effort with no standard process and no shared architecture to build on. The data engineering team spent months integrating each new brand while reporting quality for existing brands deteriorated. As pressure to deliver AI-ready analytics grew, the data estate was moving in the opposite direction, becoming more fragmented with every deal that closed. There was no path to a governed analytics layer without first addressing the underlying infrastructure.
OptiSol designed the Medallion architecture and Common Framework for repeatability from the outset, so that each new ERP source connects to the existing parameterized pipeline framework with minimal additional development. End-to-end data lineage is tracked through Fabric Monitor Hub, providing complete operational visibility across every pipeline and every source system in the portfolio. The governed Gold layer also positions the business to activate Microsoft Fabric's native AI capabilities without requiring platform changes when those initiatives move forward.
Our approach
Mapped Every ERP Source Across All 20 Brands Before Building Anything
OptiSol began with a thorough analysis of every ERP source system across the portfolio, documenting schemas, data quality gaps, naming inconsistencies, and integration complexity brand by brand. This mapping established the prioritization framework for the entire build, ensuring the highest-value reporting domains were identified and sequenced first, and every source system was fully accounted for before a single pipeline was constructed.
Designed a Pipeline Framework Built for Future Acquisitions, Not Just the Current Portfolio
Fabric Data Pipelines were designed as parameterized, reusable components from day one so the framework grows with the business rather than requiring a custom build each time a new brand joins the portfolio. Full-load and incremental strategies were configured per source, with Change Data Capture for transactional systems and On-Premises Data Gateway connections for hybrid ERP environments. Every pipeline includes monitoring, alerting, and data quality validation at ingestion time.
Implemented Medallion Architecture with Deliberate Separation Across Every Layer
The Bronze layer preserves raw data exactly as ingested, maintaining full auditability and replay capability for every source system. The Silver layer applies master data management rules and quality checks, resolving entity mismatches and definition inconsistencies accumulated across 20 different ERP configurations. The Gold layer is purpose-built per analytical domain, covering financial consolidation, project profitability, workforce utilization, and energy performance. Where Silver data was already in a reporting-ready state, SQL Views were applied directly to reduce storage cost and avoid unnecessary materialization.
Replaced Disconnected Workspaces with a Single Governed Reporting Layer
Power BI semantic models were built on the Gold layer as the single source of truth for all reporting across all brands. Executive dashboards cover financial consolidation, project portfolio performance, sustainability and energy metrics, workforce utilization, and regional analysis. Row-Level Security segments data access by brand and function. All data lineage is tracked end-to-end through Fabric Monitor Hub, so every number on every dashboard can be traced back to its original source system.
Building a governed data foundation across multiple ERP systems or acquired brands?
Find out how OptiSol builds data platforms that keep pace with your acquisition strategy, without rebuilding from scratch every time a new brand joins the portfolio.