Ingest Commodity Market Data into BigQuery
Pull live and historical pricing for all 5 WBS commodities from external market data providers. Land raw data into a dedicated BigQuery dataset on a daily schedule.
- Sources: LME (copper), Eurofer (steel), INSEE / BΓ©ton FR (concrete), vendor APIs (racks, pipes)
- Table:
w26.market_prices_rawβ partitioned by ingestion date - Frequency: Daily pull via Cloud Scheduler + Cloud Functions
- Schema: commodity_id, price_eur, unit, source, valid_date, scenario_flag
- Historical backfill: Jan-2024 β present (matches WBS baseline period)
Join Market Data with WBS Standard Price File
Load the SAP WBS export (our Standard_Price file) into BigQuery. Join planned quantities and base prices against live market prices by commodity key.
- Table:
w26.wbs_standard_priceβ static upload from SAP PS export - Join key:
commodity_idmaps WBS_Element_ID β market ticker - Output:
w26.wbs_vs_marketβ planned price vs current market price per element - Delta column:
price_variance_pct = (market_price - standard_price) / standard_price - Budget impact:
exposure_eur = price_variance_pct Γ planned_qty Γ standard_price
Build a 3-Month Price Forecast Model
Train a time-series model in BigQuery ML using 24 months of historical market data. Generate 90-day forward price predictions per commodity with confidence intervals.
- Model type: ARIMA_PLUS (BigQuery ML built-in time-series)
- Training window: rolling 24-month lookback, retrained monthly
- Output: predicted_price_p50, predicted_price_p10, predicted_price_p90 (bear/base/bull)
- Horizon: T+30, T+60, T+90 days per commodity
- Stored in:
w26.price_forecast_90d
Compute Predicted Budget Overrun Score
Cross the 90-day forecast against each WBS line. Calculate a forward-looking overrun probability and flag elements where forecast price exceeds the WBS standard price by more than the tolerance threshold.
- Overrun trigger: forecast P50 > standard_price Γ 1.05 (5% threshold)
- Severity:
LOW(5β10%) Β·MED(10β15%) Β·HIGH(>15%) - Budget exposure:
Ξβ¬ = (forecast_p50 β std_price) Γ planned_qty - Composite Risk Index: weighted avg across all WBS elements
- Time buffer: alert fires when overrun is predicted >3 months before procurement date
- Materiality filter: suppress alerts below β¬5,000 exposure
- Output table:
w26.overrun_alertsβ updated daily - Alert log stored for audit in
w26.alert_history
Deliver 3-Month Early Warning to Project Managers
Surface the overrun alert through a Looker Studio dashboard and automated email digest. PMs receive a ranked list of at-risk WBS elements with forecast charts and recommended actions.
- Dashboard: Looker Studio report connected live to
w26.overrun_alerts - Email digest: daily at 07:00 CET via Cloud Run + SendGrid
- Alert card: WBS ID Β· commodity Β· current price Β· forecast price Β· Ξβ¬ Β· severity
- PM action buttons: "Approve Forward Buy" / "Accept Risk" / "Defer"
- Response logged back to BigQuery via webhook
Trigger Forward-Buying Process in SAP MM
When a PM approves a forward-buy recommendation, push a pre-filled Purchase Requisition (PR) into SAP MM against the relevant WBS element, locking in the current market price.
- Integration: SAP RFC / BAPI_PR_CREATE called via Python middleware on Cloud Run
- PR fields auto-populated: WBS_Element, Material, Qty, Delivery Date, Info Record price
- Price source: current market price from BigQuery, not SAP standard price
- Approval workflow: standard SAP MM release strategy (2-level: PM β CFO above β¬50K)
- Budget update: actual commitment posted to WBS cost element in SAP PS
Close the Loop: Actuals Feed Model Retraining
Once goods are received in SAP, the actual invoice price is written back to BigQuery. This ground-truth data retrains the forecast model monthly, continuously improving prediction accuracy.
- SAP β BigQuery: MIRO / MIGO actuals extracted nightly via SAP BTP Data Pipeline
- Accuracy KPI: MAPE (Mean Absolute Percentage Error) tracked per commodity
- Model drift alert: retrain triggered if MAPE >8% over rolling 30 days
- Budget variance report: forecast overrun vs actual overrun per WBS element
- Quarterly review: model performance presented to Finance & Procurement steering
WBS Standard Price: β¬9.20/m Β· Forecast P50 at T+90: β¬10.48/m Β· Variance: +13.9%
Planned Qty: 5,000 m Β· Predicted Budget Overrun: +β¬64,000
Recommended Action: Forward-buy 5,000m copper cable before Jun-2026 at current spot β¬9.35/m β Saves approx. β¬56,500 vs forecast.
Alert generated: 05-Mar-2026 Β· Procurement window: 90 days Β· PM: [Assign] Β· Status: PENDING APPROVAL
+β¬64,000
What Gets Created in SAP MM
SAP Transactions Involved
before price spike
saving vs reactive buy
across WBS hierarchy
data model, no silos