Real-time Analytics Architecture with AWS for ERP Systems

The Challenge: Migrating from Batch to Real-time ERP Reporting

Oldcastle APG faced significant hurdles migrating its critical operational reporting from on-premises systems to Infor Cloud ERP. The legacy environment supported hundreds of complex, real-time reports, which Infor's configuration-based cloud reporting could not adequately replicate. Key challenges included maintaining real-time data access, supporting multi-dimensional analysis across various business functions (customer service, finance, logistics, manufacturing), providing a seamless user experience, enabling advanced analytics like demand forecasting, and scaling efficiently for over 100 concurrent users processing millions of transactions. Additionally, the need to expose data securely via APIs was crucial.

Architectural Solution Overview

The solution leverages Infor Data Fabric Stream Pipelines to stream real-time data to AWS, powering operational dashboards, AI/ML models, and intelligent search. This integration combines ERP data with cloud-native analytics services to provide comprehensive, end-to-end business insights.

Real-time Data Streaming Workflow

1. Data Ingestion: Infor Data Fabric Stream Pipelines capture insert, update, and delete operations from configured ERP tables (e.g., sales orders, inventory) as real-time change events.
2. Load Distribution & Security: Since Infor cannot directly access a private VPC, a Network Load Balancer (NLB) with static Elastic IPs is used. This NLB forwards traffic to Amazon EC2 instances acting as RDS routers, which then use iptables NAT rules to direct traffic to the Amazon Aurora database in a private subnet. Security groups are configured for strict IP and port filtering.
3. Connection Management: Amazon RDS Proxy sits between the RDS routers and the Aurora cluster. It pools and reuses database connections, handles high-frequency streaming data efficiently, and provides automatic failover capabilities for continuous data flow.
4. Data Storage: Amazon Aurora PostgreSQL-Compatible Edition, deployed across multiple Availability Zones for high availability, stores operational data. JSONB columns provide schema flexibility for streaming data, while indexes on frequently queried fields ensure query performance. Automated backups and storage scaling are configured.
5. Analytics & Visualization: Amazon QuickSight connects to Aurora PostgreSQL via VPC connectivity (credentials managed by AWS Secrets Manager) to deliver interactive dashboards and pixel-perfect reports. SPICE caching is utilized for performance, and row-level security ensures data governance.
6. Embedded Integration: QuickSight dashboards are securely embedded within Infor OS using Amazon API Gateway and AWS Lambda. Lambda functions authenticate users, validate Infor tokens, and generate time-limited, signed QuickSight embed URLs with role-based row-level security, offering a seamless user experience within the ERP interface.