Automated cost rollup for BOM using DB optimization in cost-mgmt module

PLM ENOVIA
, , , , , , , ,
1

We implemented an automated cost rollup solution for BOM structures in the cost management module that reduced calculation time from 8 hours to 45 minutes through database optimization. Our challenge was monthly cost updates for 15,000 assemblies with deep BOM hierarchies (up to 12 levels). The standard ENOVIA cost rollup was performing recursive queries for each assembly individually, causing massive database load. We optimized using composite indexes on BOM relationship tables, refactored the rollup query to use CTEs for batch processing, and implemented materialized views for frequently-accessed cost data. This automation now supports faster financial close processes and enables real-time cost analysis for engineering changes.

2

The real-time cost analysis for engineering changes is a game-changer. We currently can’t give engineers immediate cost feedback when they change BOM structures. They submit changes blind and finance reviews cost impact days later, often requiring rework. If your solution provides near-instant cost recalculation, that would transform our change management process. What’s the typical response time for a single assembly cost query after your optimization?

3

How are you triggering the automated rollup? Event-based (when component costs change) or scheduled batch? We tried event-based cost updates but found it created too much database churn when purchasing updated hundreds of material costs simultaneously. Ended up with a hybrid - immediate update for engineering changes, nightly batch for purchasing updates.

4

Batch processing with CTEs is the right approach for hierarchical rollups. The recursive nature of BOMs makes individual assembly calculations extremely inefficient. We’re planning a similar optimization for our change impact analysis. Did you implement any caching layer for stable assemblies (released/frozen) vs. recalculating everything each time? That could save even more processing time.

5

This is impressive! We’re still running cost rollups manually and it takes our team 3 days each month. How did you handle cost changes that occur mid-month? Do you recalculate everything or just the affected assemblies? Also curious about the materialized view refresh strategy - how often do you rebuild them without impacting user queries?

6

Happy to share the detailed implementation that delivered these results. Our optimization addressed all three key areas - composite indexes, query refactoring, and batch calculation strategies:

Composite Index Usage: We created strategic indexes on the BOM cost relationship tables to eliminate sequential scans:

CREATE INDEX idx_bomcost_parent_child
  ON wtpartusagelink_cost(ida3a5, ida3b5, effectivity_date);
CREATE INDEX idx_cost_part_period
  ON part_cost_history(part_ida2a2, fiscal_period, cost_type);
CREATE INDEX idx_assembly_rollup
  ON assembly_cost_cache(assembly_id, calculation_date);

The first index optimizes BOM traversal during rollup calculations. The second enables fast lookups of component costs by fiscal period. The third supports the caching strategy (more on that below). These indexes reduced query execution time by 70% for individual assembly lookups.

Query Refactoring with CTEs: The original ENOVIA cost rollup used nested SELECT statements that recalculated each BOM level separately. We refactored to use a single recursive CTE that processes the entire BOM hierarchy in one pass:

WITH RECURSIVE bom_costs AS (
  -- Base: leaf components
  SELECT p.ida2a2, p.cost, 0 as level
  FROM part_cost_history p
  WHERE NOT EXISTS (SELECT 1 FROM wtpartusagelink WHERE ida3a5 = p.ida2a2)
  UNION ALL
  -- Recursive: roll up to parents
  SELECT l.ida3a5, SUM(bc.cost * l.quantity), bc.level + 1
  FROM wtpartusagelink l
  JOIN bom_costs bc ON l.ida3b5 = bc.ida2a2
  GROUP BY l.ida3a5, bc.level
)
SELECT assembly_id, SUM(cost) as total_cost
FROM bom_costs
GROUP BY assembly_id;

This single query replaces hundreds of individual queries and allows PostgreSQL to optimize the entire calculation plan. The CTE approach reduced database round-trips from 15,000+ to 1 per rollup batch.

Batch Calculation Optimization: We implemented a three-tier calculation strategy based on assembly lifecycle:

  1. Released/Frozen Assemblies: Calculate once, store in materialized view, never recalculate unless component costs change
  2. Under Development: Recalculate nightly or on-demand
  3. High-Change Assemblies: Real-time calculation with 15-minute cache

The materialized view for stable assemblies:

CREATE MATERIALIZED VIEW mv_released_assembly_costs AS
SELECT a.ida2a2, a.wtpartnumber,
  calculate_bom_cost(a.ida2a2) as total_cost,
  CURRENT_TIMESTAMP as calculated_date
FROM wtpart a
WHERE a.lifecycle_state IN ('RELEASED', 'FROZEN');

CREATE UNIQUE INDEX ON mv_released_assembly_costs(ida2a2);

This view covers 80% of our assemblies and refreshes only weekly (released BOMs rarely change). The remaining 20% use the optimized CTE query.

Automation Implementation: We built a Java service that orchestrates the rollup process:

public class CostRollupService {
  public void executeMonthlyRollup() {
    // Phase 1: Update component costs from purchasing
    updateComponentCosts();

    // Phase 2: Identify changed assemblies
    Set<String> changedAssemblies =
      identifyImpactedAssemblies();

    // Phase 3: Batch rollup calculations
    processBatchRollup(changedAssemblies);

    // Phase 4: Refresh materialized views
    refreshCostViews();
  }
}

The service runs automatically on the 1st of each month but can also be triggered on-demand for engineering change analysis.

Results and Benefits:

  • Monthly rollup time: 8 hours → 45 minutes (89% reduction)
  • Real-time assembly cost query: 30-60 seconds → 2-3 seconds
  • Database CPU load during rollup: 85% → 35%
  • Financial close cycle: 5 days → 2 days
  • Engineering change cost feedback: Next-day → Real-time

Key Success Factors:

  1. Composite indexes eliminated sequential scans
  2. CTE-based queries reduced database round-trips by 99%
  3. Materialized views cached 80% of stable assembly costs
  4. Lifecycle-based calculation strategy focused processing on changing data
  5. Batch processing during off-peak hours minimized user impact

The real game-changer was enabling real-time cost analysis for engineering changes. Engineers now see immediate cost impact when modifying BOMs, allowing them to make informed decisions during design rather than discovering cost issues days later during finance review. This reduced costly design rework by approximately 35%.

Implementation took 6 weeks with one database specialist and one Java developer. The performance gains and business process improvements delivered ROI within the first quarterly close cycle.