I make supply chain data trustworthy enough to fly on.

De Havilland grew by acquisition, Viking Air among others, and each business arrived with its own ERP and its own way of working. Reporting lived in Excel. Even the Power BI dashboards were wired to static spreadsheets that someone refreshed by hand.

The result was predictable: two teams could bring two different numbers for the same KPI to the same meeting, and nobody could prove which was right.

1. Model before dashboards. Raw ERP extracts land in a silver layer. I build gold tables on top of them in the Fabric lakehouse and in SSMS, so a part, a supplier, a site and an order mean the same thing in every report.
2. Pick the pattern per source, not per habit. ELT where the platform can transform cheaply after loading. ETL where a source is messy enough that it should be cleaned before it lands. Star schemas where reporting speed matters, snowflake where a shared hierarchy needs a single owner. Choosing per case cut compute metering costs and stopped the refresh jobs that used to crash the server.
3. Derive the business status once. Whether a line is past due, blocked or closed is decided in the gold table, not re-invented in each report.
4. Keep the history the ERP overwrites. Snapshot tables sized to each use case, so trend lines and audits survive daily ERP updates.

The gold tables, snapshots and validation checks, and 28 automated reports on top of them. The leadership scorecard in Microsoft Fabric covers supplier OTD, on-time order placement, PO coverage, contract coverage, NCR burndown, pick-to-PO ratio, parts at shelf-life expiry risk, and PO lines awaiting supplier confirmation.

CREATE OR REPLACE TABLE gold.sales_order_lines USING DELTA AS
SELECT
  CONCAT(l.order_no, '-', l.line_no, '-', l.release_no)     AS so_key,
  l.item_no,
  CASE
    WHEN l.state IN ('Invoiced/Closed', 'Cancelled') THEN 'Closed'
    WHEN o.state IN ('Planned', 'Blocked')            THEN o.state
    WHEN TO_DATE(l.promised_date) < TO_DATE(l.ingested_at)
                                                      THEN 'Past Due'
    ELSE 'Future Due'
  END                                                        AS reporting_status,
  CASE l.priority WHEN 1 THEN 'AOG' WHEN 2 THEN 'Critical'
                  ELSE 'Routine' END                         AS priority_text,
  ROUND(CASE WHEN l.state IN ('Cancelled', 'Invoiced/Closed') THEN 0
             ELSE l.qty_due - l.qty_shipped END, 2)          AS open_qty,
  ROUND(l.qty_due * l.unit_price * l.conv_factor
        * (1 - l.discount / 100.0), 2)                       AS net_amount,
  l.ingested_at                                              AS loaded_at
FROM silver.order_lines l
JOIN silver.orders      o ON o.order_no = l.order_no;

Leadership reviews run on one scorecard instead of competing spreadsheets. Twenty-eight reports refresh on their own, and fourteen KPIs, nine of them Tier 0, each have one shared definition. I chaired the DHC Data Analytics Committee for nine months to keep it that way across business units.

The move to IFS Cloud removed native tracking between customer orders and purchase orders. Overnight, nobody could say which open demand was covered by stock or an inbound PO. Buyers fell back on spreadsheets and memory, AOG orders had no special routing, and leadership had no reliable view of what would ship.

1. Rank demand the way the business already argues about it. Priority 1, the aircraft-on-ground orders, first. Then planned ship date. Writing the order down ended the daily debate about whose order goes first.
2. Turn quantities into intervals. Each part's open backorder becomes a running total in priority order, and its PO supply becomes a running total in receipt-date order. A customer order gets exactly the overlap between its slice of demand and each PO's slice of supply, so no unit is ever promised twice.
3. Allocate in two passes. Orders that can ship go first. Customer-blocked orders only receive what is left, so a block on one account never starves an order that could ship today.
4. Respect the ERP when it's right. If IFS already links an order to a PO, and that PO really contributed quantity, the link stands. Otherwise the order is connected to the PO supplying the largest share.
5. Give every line an owner. Each open line lands in one bucket: on PO (on time, past due, arrived, outside primary sites), not on PO (kits, reserved, PO required), blocked, or picked and delivered. Each bucket is a different team's to-do list.

The engine is Spark SQL in the Fabric lakehouse. It writes a gold table that the data pipeline refreshes right after the customer order lines load, and a Power BI breakdown sits on top. Pictured above with altered figures.

WITH demand AS (                       -- open backorder, ranked
  SELECT so_key, item_no, open_qty,
         SUM(open_qty) OVER (
           PARTITION BY item_no
           ORDER BY CASE WHEN priority = 1 THEN 0 ELSE 1 END,
                    planned_ship_date, so_key
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) AS demand_end
  FROM   gold.sales_order_lines
  WHERE  reporting_status NOT IN ('Closed', 'Blocked')
),
supply AS (                            -- open PO quantity, earliest first
  SELECT po_key, item_no, qty_due,
         SUM(qty_due) OVER (
           PARTITION BY item_no
           ORDER BY planned_receipt_date, po_key
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) AS supply_end
  FROM   silver.purchase_lines
  WHERE  line_state NOT IN ('Cancelled', 'Closed') AND qty_due > 0
)
SELECT d.so_key, s.po_key,
       LEAST(d.demand_end, s.supply_end)
     - GREATEST(d.demand_end - d.open_qty,
                s.supply_end - s.qty_due)   AS allocated_qty
FROM   demand d
JOIN   supply s
  ON   s.item_no = d.item_no
 AND   d.demand_end - d.open_qty < s.supply_end    -- ranges overlap
 AND   s.supply_end - s.qty_due  < d.demand_end;
-- Pass 2 runs the same logic for blocked orders
-- against the PO quantity pass 1 left behind.

The engine contributed to more than $60M in accelerated revenue in the first five months of the fiscal year. It became the backbone of aftermarket order management: sourcing, buying, expediting and payables each work from their own bucket. Tab A in the AI Lab is a working miniature of the same idea.

Order data reaches the reporting layer from several systems, and nobody was checking it line by line. Bad records surfaced only when someone downstream noticed that a number looked wrong, if anyone noticed at all.

1. Check the data before reporting on it. I introduced an error-checking process in SSMS with rules for order lines missing a dollar value, unit-of-measure mismatches, and parts assigned to inactive buyers.
2. Treat an exception as a question, not a cleanup task. A line with no value could have been a typo. I wanted to know why each one was empty before anyone corrected it.
3. Follow it to the money. The investigation showed those lines had never been billed. The list of lines became the evidence for re-billing them.

SELECT order_no, line_no, item_no,
       qty_shipped, line_value, invoice_no
FROM   gold.sales_order_lines
WHERE  qty_shipped > 0
  AND  COALESCE(line_value, 0) = 0
ORDER  BY order_no, line_no;

Identifying the lines let the company re-bill them and recover $300K. The same checks improved inventory record accuracy across the reporting layer.

The DHC-515 Firefighter ramp covers 22 aircraft ordered by European customers. Subassemblies are built in Victoria and final assembly happens in Calgary. Procurement needed to see shortages early enough to expedite them before they reached the line.

1. Evaluate at the part and aircraft level. Part numbers form the rows and aircraft serial numbers form the columns. A total coverage percentage hides which aircraft stops first, and this grid doesn't.
2. Rank the statuses, then show the worst one. When several supply records exist for the same part and aircraft, the highest-severity condition wins the cell: not on order, late to production, late to master schedule, past due, then requisition, WIP, inspection, PO within schedule and stock.
3. Compare the right dates. Late-to-production days compare the supply date with the production demand date. Past-due days compare it with today. A seven-day grace period applies before an open PO is flagged as suspicious.
4. Don't trust a date just because it's in the ERP. An open PO counts as expected supply in IFS. The model checks whether its date still makes sense, and flags it as a suspicious PO date when it doesn't, so the cell stops looking safe.
5. Write the rules down where people can read them. A methodology and legend page ships with the report, so a buyer and a VP read every color the same way.

Procurement can see, per aircraft and per build milestone, which parts are not on order, late to production or sitting on a doubtful date, and expedite them before the shortage reaches the line. Health, at-risk lines, not-on-order lines and late-to-production lines are tracked as KPIs that respond to every filter. The report pages above are recreated with altered figures.

Supplier OTD was 38% when I scoped the program. There was no risk model and no consistent tracking, and nothing in the executive reporting gave leadership something to act on. Every escalation was a reaction to a stoppage that had already happened.

1. Agree on the measure before trying to move it. One OTD definition, agreed with procurement, so the number stopped being argued about and started being worked on.
2. Separate their lateness from ours. A PO placed too late can't arrive on time. I built on-time order placement tracking so buyers could see their own lag, apart from supplier performance.
3. Make the next action obvious. A Power BI view of every open PO, with days early or late by supplier and a specific expedite call for each.
4. Put it in front of people who can act. I presented results to directors and VPs every month.

Supplier OTD rose from 38% to 78% in six months. On-time order placement went from 11% to over 60% in the same fiscal year.

Sites were buying parts at full MOQ and lot sizes while the same parts sat unused at other locations or in project stock. Nobody had a consolidated picture, so the company carried excess spend and unfilled customer orders at the same time.

1. Classify before judging. Parts are grouped into sales-volume classes, then into ABC classes using custom parameters, because a fast-moving C part and a slow A part need different rules.
2. Count only stock that can ship. On-hand quantity lies. Stock sitting in quality control, quarantine, scrap, expiry or return-to-vendor locations, or reserved for a project, is excluded, so "available" means available.
3. Bucket every part, then ask the next question. Over max, in spec, below min or stocked out. Then: is it on a PO? Is a requisition already open? Each branch of the tree is a different decision.
4. Move before you buy. Excess is matched to open customer orders by urgency and value before any new purchase is considered.

SELECT site, item_no,
       qty_on_hand - qty_reserved                  AS qty_available,
  CASE
    WHEN avail_code = 'QC HOLD'
      OR (avail_code IS NULL
          AND (loc LIKE 'QC%' OR loc LIKE '%QUARANTINE%'
               OR loc_type = 'QUALITY ASSURANCE'))
      THEN 'Quality hold'
    WHEN (avail_code IS NULL OR avail_code IN ('STD', 'TRADE'))
      AND qty_on_hand - qty_reserved > 0
      AND loc_type IN ('Arrival', 'Picking')
      AND project_id IS NULL
      AND loc NOT LIKE '%SCRAP%'  AND loc NOT LIKE '%EXPIRE%'
      AND loc NOT LIKE '%RTV%'    AND loc NOT LIKE '%TRANSFER%'
      THEN 'Usable in bin'
    ELSE 'Other'
  END                                              AS stock_story
FROM silver.stock_by_location;

$8M in open customer orders were filled from existing inventory with no new procurement spend. The breakdown above, recreated with altered figures, became a standing view for procurement, spend budgeting and sales campaigns for slow-moving stock. Tab B in the AI Lab is a working version of the same pattern.

Moving to IFS Cloud meant deciding what data crossed over and in what shape. Those decisions were being made by business-unit owners under deadline pressure, and some of them would quietly have broken analysis for years.

1. Don't migrate a balance when you need the history. The plan was to move only the remaining open quantity of each purchase order line. That would have made every partly received line look like a smaller order and wiped out supplier spend tracking and purchase history. I made the case and helped the migration team validate full lines before they loaded.
2. Ask what "out of scope" will cost later. Several datasets marked out of scope were the ones behind pricing and sourcing KPIs. I made the case to bring them into scope, and they were moved.

Both decisions changed. Full purchase order lines and the pricing and sourcing data crossed over, which is why the business can still report on cost, supplier agreements, contract coverage ratios and pricing today.