Best practices for separating master and transactional data during demand planning migration

SCM Blue Yonder Luminate
, , , , , , , ,
1

We’re planning a large-scale migration to Blue Yonder Luminate 2022.2 for demand planning and I’m looking for insights on sequencing master vs transactional data loads. Our legacy system has tightly coupled master data (products, customers, locations) and transactional data (forecasts, orders, shipments) in a single database. I’ve seen migrations fail when transactional records reference master data that hasn’t been loaded yet, causing referential integrity violations. What’s the industry best practice for separating these data types during migration? Do you load all master data first, validate referential integrity, then load transactional? Or is there a more sophisticated approach with staging validation? Curious to hear how others have tackled this sequencing challenge.

2

The dependency mapping approach is interesting. How granular do you get with the graph? Do you map at the table level or entity level? And for staging validation, what’s your typical validation failure rate on the first pass?

3

After doing a dozen demand planning migrations to Blue Yonder Luminate, here’s what I’ve learned about master vs transactional sequencing:

Master vs Transactional Sequencing: Always follow this sequence:

  1. Master Data Load (Phase 1):

    • Load in dependency order: locations → product categories → products → customers → suppliers
    • Each entity must be fully loaded and validated before dependent entities
    • For Luminate 2022.2 demand planning, critical master entities include: product hierarchies, customer hierarchies, location hierarchies, planning calendars, demand units
    • Use dependency mapping to determine load order - create a simple directed graph showing which tables reference which

  2. Master Data Validation (Phase 2):

    • Verify all master data loaded successfully with no constraint violations
    • Check for duplicate keys, missing required fields, invalid hierarchies
    • Run data quality checks (product codes follow naming conventions, customer assignments valid, etc.)
    • Generate master data summary report: record counts by entity, validation pass/fail status

  3. Transactional Data Preparation (Phase 3):

    • Extract transactional data (forecasts, orders, shipments, demand history)
    • Validate foreign key references against loaded master data BEFORE attempting load
    • Identify and quarantine orphaned records (transactions referencing non-existent master records)
    • Create mapping tables for legacy IDs → Luminate IDs if dealing with multiple source systems

  4. Transactional Data Load (Phase 4):

    • Load historical transactional data first (lower risk, used for analytics/trending)
    • Validate and load recent transactional data (higher risk, business-critical)
    • Segment by time periods if dataset is large (e.g., load by quarter)

Referential Integrity Checks: Implement comprehensive validation between phases:

-- Check for orphaned product references in forecast staging
SELECT COUNT(*) as orphaned_count
FROM staging_forecasts sf
LEFT JOIN master_products mp ON sf.product_id = mp.product_id
WHERE mp.product_id IS NULL;

-- Check for orphaned customer references
SELECT COUNT(*) as orphaned_count
FROM staging_orders so
LEFT JOIN master_customers mc ON so.customer_id = mc.customer_id
WHERE mc.customer_id IS NULL;

Run these checks in staging before promoting to production. Export orphaned records to exception files for data cleanup. Never load transactional data with unresolved referential integrity issues - it corrupts your demand planning analytics.

Staging Validation: Use a three-tier staging architecture:

  1. Raw Staging: No constraints, accepts all data from source systems
  2. Validated Staging: Business rules applied, referential integrity checked, data quality validated
  3. Production Staging: Full constraints enabled, ready for final load

Between each tier, run validation procedures:

  • Tier 1→2: Data type validation, format standardization, deduplication
  • Tier 2→3: Referential integrity validation, constraint checking, business rule enforcement

Create validation stored procedures that return detailed reports:

  • Total records processed
  • Records passed validation
  • Records failed validation (with failure reasons)
  • Orphaned references by entity type
  • Data quality scores

For demand planning specifically, validate:

  • All forecasts reference valid products, customers, locations
  • Date ranges align with planning calendars
  • Demand units match product UOM definitions
  • Historical orders reference valid products and ship-to locations
  • Planning hierarchies are complete (no broken parent-child relationships)

Practical tips from experience:

  1. Always create a master ID mapping table when migrating from multiple sources
  2. Load master data 48 hours before transactional data to allow for issue resolution
  3. Expect 5-8% validation failure rate on first pass - build in time for cleanup
  4. Use database constraints in production but disable them in staging for flexibility
  5. Archive all validation reports and exception files for audit trail
  6. Test the full sequence on a subset (10% of data) before production migration

The key principle: master data is the foundation, transactional data is the building. You can’t build without a solid foundation. Validate relentlessly at every stage and you’ll avoid the cascading failures that plague poorly planned migrations.

4

Absolutely load master data first. It’s the foundation - products, customers, locations, suppliers must exist before you can reference them in forecasts or orders. We use a four-phase approach: (1) extract and validate master data, (2) load master data to target, (3) validate referential integrity, (4) load transactional data. The key is that phase 3 validation - run queries to ensure every product_id in your transactional staging tables exists in the master product table before you even attempt the load.

5

We map at the table level for simplicity. Entity-level would be more precise but adds complexity. Our validation failure rate on first pass is typically 5-8% for master data (mostly data quality issues like duplicate keys, invalid formats) and 12-15% for transactional data (mostly referential integrity violations). After cleanup and reload, we usually hit 99%+ success rate. That remaining 1% goes to manual review.

6

Don’t forget about cross-system referential integrity. In demand planning migrations, you often pull data from multiple sources - ERP for products, CRM for customers, WMS for locations. Each system might have its own ID schemes. We create a master ID mapping table that translates legacy IDs to Luminate IDs across all source systems. This becomes your single source of truth for referential integrity validation. Without it, you’ll have orphaned references that are nearly impossible to track down.

7

Staging validation is critical but often overlooked. We create staging tables that mirror the target schema but without foreign key constraints enabled. Load everything to staging first, then run comprehensive validation queries. Check for orphaned references, missing master records, data type mismatches, constraint violations. Export all validation failures to exception reports. Only after staging passes 100% validation do we promote data to production tables with constraints enabled.

8

We take it a step further with dependency mapping. Not all master data is equal - some master entities depend on others. For example, product hierarchies depend on category definitions, customer assignments depend on sales regions. Create a dependency graph and load in topological order. This prevents cascading failures where you try to load a product before its category exists. For transactional data, we also segment by time period - load historical data first (less critical), validate, then load recent transactions (business-critical).