Every reporting cycle required analysts to manually extract data from more than 20 separate ERP 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. When two business units presented conflicting numbers for the same KPI in the same leadership meeting, there was no governed layer to resolve it.

With no central data warehouse and no shared semantic layer, cross-brand comparison was unreliable. Strategic decisions on resource allocation, acquisition evaluation, and portfolio performance were being made on data that could not be trusted. Each new acquisition added another ERP source with no standard integration path and no reusable framework to absorb it — meaning the data engineering team spent months on each new brand while reporting quality for existing brands deteriorated in parallel.

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 queried operational ERP databases directly, running slowly and placing analytical load on systems designed for transactions. There were no enterprise-level views available for C-suite or board reporting.

The problem was not visibility — every brand had dashboards. The problem was that no two dashboards could be trusted to agree with each other. Without a conformed Silver layer enforcing shared entity definitions and metric calculations, every team was effectively reporting from a different system of record. Master data for customers, projects, and cost centres was maintained independently by each brand, with no reconciliation process and no canonical identifier that crossed subsidiary boundaries.

As the acquisition pipeline continued to grow, the data infrastructure was moving in the opposite direction — becoming more fragmented with each deal that closed. There was no path to AI-ready analytics without first fixing the foundation.

OptiSol designed and delivered a unified data platform on Microsoft Fabric, implementing a full Medallion architecture across Bronze, Silver, and Gold layers to consolidate all ERP sources into a single governed analytics foundation.

Bronze preserves raw data exactly as ingested from every source system, maintaining full auditability and replay capability. 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. Automated monitoring, alerting, and data quality validation run at ingestion time for every pipeline.

Silver 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. Inconsistencies accumulated across 20 different ERP configurations are resolved here — one canonical way to represent a customer, a project, a cost centre, regardless of which subsidiary originated the record.

Gold is purpose-built per analytical domain: financial consolidation, project profitability, workforce utilization, and energy performance. Consistent business definitions are enforced so that revenue, margin, and project completion rate carry the same meaning regardless of which ERP system originated the data. Where Silver data was already in a reporting-ready state, SQL Views were applied directly to reduce storage cost and avoid unnecessary materialization.

The entire pipeline framework was designed as parameterized, reusable components from day one — so that onboarding a new acquisition into the platform takes days rather than months of bespoke integration work. 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.

We 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.

All disconnected Power BI workspaces were replaced with governed semantic models built on the Gold layer in OneLake, establishing a single source of truth for all reporting across the portfolio. Executive dashboards cover financial consolidation, project portfolio performance, sustainability and energy metrics, workforce utilization, and regional analysis. Row-Level Security was configured so that brand-level users access only their own data while corporate leadership has a full consolidated view across all brands. Automated refresh schedules aligned to pipeline completion ensure dashboards always reflect the latest available data without manual intervention.

The design principle throughout was repeatability. Every pattern, every pipeline template, every data contract was built so that the next acquisition connects to the existing framework rather than triggering a new custom build. The framework grows with the business.

Month-end reporting time dropped by 60%. Leadership has a single authoritative view across all brands for the first time — financial performance, project margins, energy KPIs, and workforce utilization all drawing from the same governed Gold layer. Every team across every brand works from the same underlying numbers, and every figure on every dashboard can be traced back to its originating source system through Fabric Monitor Hub.

The acquisition framework is no longer a liability. Each new brand that joins the portfolio connects to the existing parameterized pipeline framework with minimal additional development — days of onboarding rather than months of custom integration work. The conformed entity model in Silver means master data conflicts are resolved at the layer designed for it, not in a spreadsheet the week before a board presentation.

The governed Gold layer also positions the business to activate Microsoft Fabric's native AI capabilities without requiring platform changes. AI and analytics initiatives that previously had no reliable data foundation to build on can now proceed against certified, consistent data that agrees with itself across every acquired brand.

Identifying details are omitted under client confidentiality.