Client Overview
A Global Asset Management Firm Operating Across Four Regions
Our client (whose identity we are unable to disclose) is a global asset management firm overseeing multi-billion-dollar investment portfolios and strategic partner relationships across North America, EMEA, Asia-Pacific, and Latin America. Their finance and operations teams depend on accurate, timely cross-regional data to support investment decisions, regulatory reporting, and partner performance oversight.
The Business Challenge
A Growing Excel Ecosystem Was Silently Eroding Analytical Capability
Across four regions, the client's finance and operations teams had independently developed Excel-based reporting workflows, creating a fragmented, unscalable data ecosystem with no single source of truth.
Our Solution
An Agentic Ingestion Framework That Reasons, Routes, and Executes Without Human Intervention
OptroniX designed and delivered a two-layer pipeline that separates AI reasoning from deterministic execution, enabling the system to autonomously handle any Excel file structure without pre-written transformation rules or manual schema mapping.
Agentic File Profiling and Routing
Each file arriving in Azure Blob Storage triggers the Analysis Agent via EventStream. The agent profiles the file's full structure, covering sheets, column layouts, data types, and row geometry, then semantically determines the correct target analytical domain with no pre-configured routing rules required.
AI-Powered Schema Mapping
The Analysis Agent autonomously maps heterogeneous source columns to the gold schema target, handling regional naming variations, multi-sheet joins, FX currency fields, and computed columns. The structured JSON specification produced is validated against the target schema catalog before any data movement begins.
Deterministic Load Execution
A deterministic PySpark executor reads the approved specification from the central metadata registry and performs all data movement without further AI involvement, ensuring every row loaded is fully traceable, reproducible, and auditable. Complex multi-sheet files are handled by a secondary Transformation Agent whose generated code is validated by a static AST guard before execution.
OLAP Gold Layer and Star Schema
All ingested data lands in a governance-ready star schema in Microsoft Fabric's OneLake, comprising a partnership dimension table with SCD Type 2 history and three analytical fact tables covering actual spend, budget allocations, and headcount by region. Power BI connects directly for real-time cross-regional reporting and budget variance analysis.
Technical Architecture
End-to-End Agentic Data Flow
From Azure Blob Storage through the AI Analysis Agent, across dual execution patterns, into the Gold Star Schema in Microsoft Fabric and through to Power BI as the analytics consumer.
Results and Business Impact
Measurable Outcomes, Delivered at Scale
"For the first time, our investment team has a single view of budget performance across all four regions that updates the moment a file is uploaded. What used to be a two-analyst, three-day exercise now runs automatically and is ready before we sit down for our Monday morning review."
Key Takeaways
What This Engagement Taught Us
AI Agents Handle What Rules Cannot
Twelve independently maintained Excel files resisted every rules-based ETL approach evaluated during discovery. Deploying an AI agent to reason about structure, rather than pattern-match against it, was the only approach that scaled across all source files without custom engineering per file.
Separating Reasoning from Execution Builds Auditable Systems
Keeping AI in the reasoning layer and deterministic PySpark in the execution layer means every row loaded into the warehouse can be traced back to the exact specification the agent produced, creating an audit trail that satisfies both internal governance and external compliance requirements without additional tooling.
Event-Driven Ingestion Eliminates the Batch Lag Tax
Triggering the ingestion pipeline the moment a file lands in Blob Storage removed the 2–3 day reporting delay that had become the standard expectation. For a finance team making time-sensitive investment decisions, the shift from day-old data to near-real-time visibility fundamentally changed how leadership operated their Monday review cycles.
An OLAP Star Schema Multiplies the Value of Ingested Data
Landing data into a properly modeled star schema rather than raw storage meant Power BI could immediately serve cross-regional budget variance, partnership performance, and headcount queries that previously required days of manual aggregation. The schema investment paid back visibly on the first reporting cycle after go-live.