Each of the five brands maintained its own database — the same schema structure across all of them, but no automated consolidation and no shared reporting layer above brand level. Producing a corporate view of participant counts, visa approval trends, or placement performance required manual extraction from each brand's system, reconciliation in spreadsheets, and a consolidation process that consumed significant analyst time and still produced figures that lagged operational reality by days.

Executive and operational reporting served different audiences with different needs — leadership needed high-level KPIs across the full portfolio, operational teams needed day-to-day visibility into pending placements, visa processing delays, and school allocations. Neither audience had a tool built for their specific view. Both were working from the same manual extract process, adapted for different purposes but drawing from the same fragmented source.

As the business grew and the regulatory environment around J1 Visa programs continued to evolve, the cost of operating without a governed analytics foundation was rising. Decisions on brand resourcing, employer partnerships, and program expansion required data that the existing setup could not reliably produce.

The five brand databases shared a common schema structure — a significant advantage that simplified integration relative to a true multi-ERP consolidation. But sharing a schema is not the same as sharing a data layer. Each database operated independently, with its own records, its own brand-specific configurations, and no automated process to consolidate records across brands into a single corporate view.

There was no Bronze layer to land and preserve source records with full audit trail. No Silver layer to merge brand records into unified entities and apply data quality rules. No Gold layer to publish KPI-ready aggregates that executive and operational users could trust without manual verification. The architecture needed to create these layers from the existing source databases — not replace the brand systems, but build a governed analytics path above them.

OptiSol designed and delivered a centralized corporate data warehouse on Azure, implementing a Bronze-Silver-Gold Medallion architecture that consolidates all five brand databases into a single governed analytics platform.

Bronze stores raw, untransformed copies of source records from each brand database, retained per brand with full lineage from the originating source system. Incremental ETL pipelines built in Python and orchestrated via Azure Functions synchronize each entity using timestamp columns to identify records changed since the previous sync — avoiding full reloads and keeping the warehouse efficiently current. Azure Blob Storage handles logs and staging, with Azure Function execution logs and automated data quality checks providing operational visibility across every sync run.

Silver merges records from all five brands into unified, common entity structures. Data from each brand is cleansed, standardized, and enriched with brand identifiers, source system metadata, and load timestamps. Duplicates are resolved. Formats are standardized. The Silver layer is where five independently operated brand databases become one coherent corporate dataset — participants, placements, employers, visa statuses, and schools all represented in a single consistent structure regardless of which brand originated the record.

Gold contains KPI-ready aggregated data optimized for dashboards and executive reporting, organized around a Star Schema with dimension structures covering brands, participants, employers, jobs, and a standard date dimension — and fact structures capturing placements, visa status changes, and post-placement activity. Executive KPIs include participant volumes, visa approval rates, placement performance, and brand-wise comparisons. Operational metrics cover pending placements, visa and passport processing delays, school allocation status, and post-placement completion.

Power BI and Metabase dashboards are built on the Gold layer with role-based access configured so brand-level users see only their own data while corporate leadership has a full consolidated view across all five brands. Sync frequencies are defined per entity based on business priority — high-priority operational entities sync at shorter intervals, while reference and slower-changing entities sync on a daily cadence.

The architecture was designed explicitly for startup-scale workloads while maintaining a clear upgrade path as the business grows. Azure Functions provide orchestration at near-zero infrastructure cost at current volumes. Azure SQL Database serves as the central warehouse. The total infrastructure cost at current scale sits well within a budget appropriate for a growing multi-brand business — with defined upgrade paths to Azure Synapse Analytics, Azure Data Factory, and real-time streaming when volumes justify it.

Security was addressed from the outset: read-only reporting access for end users, separate production and warehouse environments, secure credential management using Azure Managed Identity, and role-based access restrictions on dashboards. SQL performance monitoring, incremental sync validation, and automated data quality checks run continuously across the pipeline.

The shared schema structure across all five brand databases — which would have been a costly remediation project in a typical multi-ERP consolidation — was treated as an accelerator. The parameterized ETL framework was built to exploit this consistency, meaning the onboarding pattern for each brand was largely repeatable rather than bespoke.

Executive leadership has a consolidated corporate dashboard across all five brands for the first time — participant volumes, placement performance, visa approval trends, and brand-wise comparisons all drawing from the same governed Gold layer. Manual extraction and spreadsheet reconciliation are replaced by automated pipelines that keep the warehouse current without analyst intervention.

Operational teams have purpose-built views covering the day-to-day metrics that matter for a J1 Visa placement operation: pending placements, visa and passport processing status, school allocation, and post-placement completion. The same data layer serves both audiences, with role-based access ensuring each team sees the view appropriate to their function without requiring separate data environments.

The architecture's cost efficiency means the business is running enterprise-grade analytics at a cost appropriate to its current scale — with a documented upgrade path that does not require rebuilding when the portfolio grows or when real-time analytics become a priority.

Specific figures are client-internal. The directional outcome was a governed analytics foundation the business operates independently, with consolidated corporate visibility that was not achievable from the previous brand-siloed reporting setup.