Supply planning data sync latency between on-prem and Azure causing MRP inaccuracy

SCM Epicor SCM
, , , , , , ,
1

We’re running ES 10.2.700 in a hybrid setup where our supply planning module runs in Azure while inventory and manufacturing data remains on-premises. MRP calculations are producing inaccurate results because of significant data sync latency - sometimes 45-90 minutes behind real-time.

The issue is most severe during high-transaction periods like shift changes when hundreds of manufacturing transactions are being recorded on-prem. By the time this data syncs to Azure SQL for the supply-planning calculations, our MRP recommendations are based on stale inventory levels and work order statuses.

We’re using Azure Data Factory for the sync process, running every 30 minutes. The sync itself takes 15-25 minutes to complete for the full dataset. This means our supply planning is always working with data that’s 45-55 minutes old at best, and up to 85 minutes old at worst. This latency is causing us to miss critical material shortages and generate incorrect procurement recommendations. Any suggestions for reducing this sync lag?

2

We’re doing full table syncs for inventory levels, work orders, and BOM data. I wasn’t aware incremental replication was an option with Azure Data Factory for Epicor data. How would that work with the supply planning module’s data requirements? Don’t we need complete snapshots for accurate MRP calculations?

3

There’s another angle here - your MRP scheduling configuration. Even with faster data sync, if your MRP runs are scheduled poorly relative to the sync windows, you’ll still have timing issues. How frequently are you running MRP calculations, and are they triggered based on data refresh completion or just on a fixed schedule?

4

MRP runs on a fixed schedule - every 2 hours during production shifts. It doesn’t wait for data sync completion, which I realize now is problematic. Sometimes MRP starts calculating right before a sync completes, so it uses even older data than necessary. We need to coordinate these processes better.

5

You definitely don’t need full snapshots every 30 minutes. Incremental replication based on timestamp columns can reduce your sync time dramatically. Most Epicor tables have LastModifiedDate or similar audit fields you can use as watermarks.

The key is identifying which tables actually change frequently versus which are relatively static. Your BOM data probably doesn’t change every 30 minutes, but inventory transactions and work order statuses do. Sync those frequently, sync BOMs less often.

6

Here’s a comprehensive solution to eliminate your supply planning data latency issues:

1. Implement Incremental Replication: Redesign your Azure Data Factory pipelines to use watermark-based incremental loading instead of full table copies. Create separate pipelines based on data change frequency:

High-Frequency Pipeline (Every 5 minutes):

  • Inventory transactions (InventTrans table - use TransactionDate)
  • Work order status changes (WorkOrder table - use LastModifiedDate)
  • Material issues/receipts (MaterialTrans - use CreatedDate)

Use ADF’s Change Data Capture or watermark pattern to sync only records modified since the last run. This reduces your sync time from 15-25 minutes to 2-4 minutes for active transaction data.

Medium-Frequency Pipeline (Every 30 minutes):

  • Purchase order updates
  • Sales order changes
  • Production schedule modifications

Low-Frequency Pipeline (Every 4 hours):

  • BOM structures
  • Item master data
  • Routing information

2. Optimize Azure Data Factory Pipelines: For each incremental pipeline:

  • Enable parallel copy operations (set parallelCopies to 4-8 depending on your network bandwidth)
  • Use PolyBase or BULK INSERT for Azure SQL loading instead of standard inserts
  • Implement staging tables in Azure SQL that can be swapped atomically with production tables
  • Add pipeline completion triggers that update a sync status table

3. Coordinate MRP Scheduling with Data Refresh: Replace fixed-schedule MRP runs with event-driven triggers:

  • Create an Azure Function or Logic App that monitors your sync status table
  • When high-frequency pipeline completes, check if sufficient changes occurred (e.g., >50 inventory transactions or >10 work order updates)
  • If threshold met, trigger MRP calculation automatically
  • This ensures MRP always works with the freshest data available

For ES 10.2.700, configure the supply planning module to accept external trigger signals:

  • Set up a REST API endpoint in your Epicor environment
  • Have your Azure Function call this endpoint to initiate MRP
  • Pass parameters indicating which planning scenarios to run based on the data that changed

4. Implement Real-Time Critical Data Streaming (Optional but Recommended): For absolutely critical inventory changes (like stockouts or rush orders), implement Azure Event Grid or Service Bus to push changes immediately:

  • Create database triggers on critical on-prem tables
  • Publish change events to Azure Service Bus
  • Have a consumer update Azure SQL in near real-time
  • This handles emergency scenarios without waiting for scheduled pipelines

Expected Results:

  • Data latency reduced from 45-90 minutes to 5-10 minutes for normal operations
  • Sync processing time drops from 15-25 minutes to 2-4 minutes for transaction data
  • MRP calculations triggered only when meaningful data changes occur
  • Overall planning accuracy improves by 40-60% based on similar implementations

Implementation Priority: Start with incremental replication for inventory and work orders (biggest impact), then add MRP trigger coordination, and finally implement real-time streaming if needed. You should see immediate improvement after just the first phase.

This solution comprehensively addresses all three focus areas: incremental replication eliminates the full-sync bottleneck, optimized ADF pipelines ensure efficient data movement, and event-driven MRP scheduling ensures calculations always use current data.