Supplier performance measurement is critical for a resilient supply chain. With Databricks AI/BI, we can build a scorecard that measures supplier quality and delivery performance, powered by four key KPIs: OTD, OTS, Lead Time Adherence, and Defect Rate.
Step 1: Define KPIs in SQL
1. On-Time Delivery (OTD)
Definition: % of deliveries received by the expected delivery date.
CREATE OR REPLACE TABLE otd_kpi AS
WITH on_time_deliveries AS (
SELECT supplier_number,
COUNT(*) AS on_time_deliveries
FROM supplier_sourced
WHERE date_received <= expected_date_received
GROUP BY supplier_number
),
total_deliveries AS (
SELECT supplier_number,
COUNT(*) AS total_deliveries
FROM supplier_sourced
GROUP BY supplier_number
)
SELECT otd.supplier_number,
(COALESCE(otd.on_time_deliveries,0) / COALESCE(td.total_deliveries,1)) * 100 AS on_time_delivery_kpi
FROM on_time_deliveries otd
JOIN total_deliveries td
ON otd.supplier_number = td.supplier_number;2. On-Time Ship (OTS)
Definition: % of shipments shipped by the expected ship date.
CREATE OR REPLACE TABLE ots_kpi AS
WITH on_time_shipments AS (
SELECT supplier_number,
COUNT(*) AS on_time_shipments
FROM supplier_sourced
WHERE date_shipped <= expected_date_shipped
GROUP BY supplier_number
),
total_shipments AS (
SELECT supplier_number,
COUNT(*) AS total_shipments
FROM supplier_sourced
GROUP BY supplier_number
)
SELECT ots.supplier_number,
(COALESCE(ots.on_time_shipments,0) / COALESCE(ts.total_shipments,1)) * 100 AS on_time_ship_kpi
FROM on_time_shipments ots
JOIN total_shipments ts
ON ots.supplier_number = ts.supplier_number;3. Lead Time Adherence
Definition: % of deliveries that met the promised lead time window.
CREATE OR REPLACE TABLE lead_time_kpi AS
WITH lead_time_adherence AS (
SELECT supplier_number,
COUNT(*) AS on_time_lead_times
FROM supplier_sourced
WHERE DATEDIFF(date_received, date_requested)
<= DATEDIFF(expected_date_received, date_requested)
GROUP BY supplier_number
),
total_deliveries AS (
SELECT supplier_number,
COUNT(*) AS total_deliveries
FROM supplier_sourced
GROUP BY supplier_number
)
SELECT lta.supplier_number,
(COALESCE(lta.on_time_lead_times,0) / COALESCE(td.total_deliveries,1)) * 100 AS lead_time_adherence_kpi
FROM lead_time_adherence lta
JOIN total_deliveries td
ON lta.supplier_number = td.supplier_number;4. Defect Rate
Definition: % of defective parts (based on warranty claims) vs. total sourced parts.
CREATE OR REPLACE TABLE defect_rate_kpi AS
WITH defective_parts AS (
SELECT ss.supplier_number,
COUNT(*) AS defective_parts
FROM warranty_claims wc
JOIN supplier_sourced ss
ON wc.part_number = ss.part_number
GROUP BY ss.supplier_number
),
total_parts AS (
SELECT supplier_number,
SUM(quantity_sourced) AS total_parts
FROM supplier_sourced
GROUP BY supplier_number
)
SELECT sm.supplier_number,
sm.supplier_name,
(COALESCE(dp.defective_parts,0) / COALESCE(tp.total_parts,1)) * 100 AS defect_rate_kpi
FROM supplier_master sm
LEFT JOIN defective_parts dp ON sm.supplier_number = dp.supplier_number
LEFT JOIN total_parts tp ON sm.supplier_number = tp.supplier_number;Step 2: Combine into a Supplier Scorecard View
Once we have all four KPI tables, we join them into a single view that feeds the Databricks AI/BI dashboard.
CREATE OR REPLACE VIEW supplier_scorecard AS
SELECT sm.supplier_number,
sm.supplier_name,
otd.on_time_delivery_kpi,
ots.on_time_ship_kpi,
lt.lead_time_adherence_kpi,
dr.defect_rate_kpi
FROM supplier_master sm
LEFT JOIN otd_kpi otd ON sm.supplier_number = otd.supplier_number
LEFT JOIN ots_kpi ots ON sm.supplier_number = ots.supplier_number
LEFT JOIN lead_time_kpi lt ON sm.supplier_number = lt.supplier_number
LEFT JOIN defect_rate_kpi dr ON sm.supplier_number = dr.supplier_number;Step 3: Build the Dashboard
With supplier_scorecard available in Unity Catalog:
- Create KPI Cards: OTD %, OTS %, Lead Time Adherence %, and Defect Rate %.
- Rank Suppliers: Table sorted by performance, with filters for region or category.
- Trend Charts: Monthly OTD/OTS performance.
- AI Narratives: Databricks AI/BI can summarize insights in plain English.
Final Thoughts
By unifying supplier performance into one scorecard, organizations gain visibility into quality, delivery, and risk. With Databricks AI/BI, it’s easy to calculate, govern, and visualize these KPIs at scale, empowering better sourcing decisions.
