Client Overview
A Large, Employee-Owned Multi-Disciplinary Engineering Consultancy
Our client is a large employee-owned engineering and environmental sciences consulting firm with decades of experience across infrastructure, transportation, water resources, environmental services, and facilities engineering. Operating across dozens of U.S. offices and managing hundreds of concurrent projects at any given time, they depend on accurate, timely cross-system data to run their business effectively.
The Business Challenge
Three Mission-Critical Systems. Zero Data Integration. Reporting Done Manually.
Oracle EBS held core project financials, HubSpot managed client relationships, and Workday controlled HR and project labor data. None of these systems talked to each other, and enterprise reporting required days of manual extraction and reconciliation.
Our Solution
A Centralized Multi-Source Data Lake on Microsoft Fabric: Oracle, HubSpot, and Workday Unified
OptroniX designed and implemented a unified data integration architecture that brought all three source systems into a single OneLake environment, then established a curated reporting layer that eliminated manual assembly entirely.
Oracle EBS On-Premises Integration
Oracle E-Business Suite data was ingested into Microsoft Fabric OneLake using Fabric Data Factory pipelines, leveraging the On-Premises Data Gateway to bridge the Oracle on-premises environment to the Fabric cloud platform. Full and incremental load patterns were configured to keep OneLake synchronized without impacting production EBS performance.
HubSpot CRM Integration via PySpark
HubSpot data was ingested using PySpark notebooks connected to the HubSpot API with full pagination handling and rate limit management. Contact records, deal pipelines, company associations, and engagement history were extracted and transformed into a standardized schema aligned with the Oracle project data model.
Workday HR and Cost Integration via PySpark
Workday HR and project accounting data was ingested via PySpark notebooks using Workday's REST API. Employee records, labor allocations, and project cost data were mapped into the unified schema, enabling cross-system analysis of project economics combining Oracle cost, Workday labor, and HubSpot revenue pipeline in a single view.
Entity Resolution Across All Three Systems
PySpark notebooks applied systematic entity resolution: matching client records across HubSpot and Oracle using deterministic and probabilistic key matching, connecting Workday employee IDs to Oracle project resource records, and aligning Oracle billing data with Workday labor costs into a consistent project profit-and-loss structure.
Curated Enterprise Reporting Layer
Business-ready datasets were materialized as reporting views on top of the unified OneLake foundation. These views power Oracle EBS replacement reports recreated on the Fabric layer, cross-system leadership dashboards showing project financials plus CRM pipeline plus HR allocation, and automated schedule-based refreshes replacing the previous 3 to 5 day manual cycle.
Full Data Lineage and Governance
End-to-end data lineage was established, tracing every field in every reporting view back to its source system, transformation logic, and ingestion timestamp. For the first time, the firm could answer "where did this number come from?" for any figure in any enterprise report within seconds rather than hours.
Technical Architecture
Three Sources, One Platform
Oracle EBS on-premises via Data Gateway, HubSpot via PySpark API, and Workday via PySpark API all converge into Microsoft Fabric OneLake, where entity resolution and transformation produce unified reporting views and enterprise dashboards.
Results and Business Impact
Three Systems. One Platform. Real-Time Answers.
"We finally have a platform that connects our Oracle operations data with our CRM and HR systems, and it refreshes automatically. What used to take our team days to pull together, leaders can now see in real time. This changes how we run the business."
Key Takeaways
What This Project Taught Us
Multi-Source Integration Requires Entity Resolution, Not Just Data Movement
Connecting Oracle, HubSpot, and Workday only delivers value when client, project, and employee entities can be reliably matched across all three systems. This is the hard part of the work, and it requires deliberate design before any pipeline is built.
PySpark Is the Right Tool for SaaS API Ingestion at Scale
HubSpot and Workday do not have native Fabric connectors at the same fidelity as SQL Server. PySpark notebooks provide the flexibility to handle complex API pagination, rate limiting, and response parsing that connector-based tools cannot match.
Oracle On-Premises Is Not a Blocker for Cloud Analytics
With the On-Premises Data Gateway and properly designed incremental load patterns, Oracle EBS becomes just another source feeding a modern cloud platform. The on-premises constraint is a connectivity challenge, not an architectural one.
Unified Reporting Views Unlock Business Agility
When the data layer is centralized and governed, the time to build a new enterprise report drops from weeks to days. Every future use case, dashboard, or AI workload built on top of this foundation costs a fraction of what it would without it.