£1.05m
committed only after the invoice arrived
280 PO lines have an invoice dated before the PO. In Indirect & Services, 13% of lines are after-the-fact and 40% have no requisition.
← All work
Case study · Procure-to-pay
Procure-to-pay analytics, from SAP S/4HANA to Microsoft Fabric
An end-to-end build for a UK food manufacturer: SAP CDS extraction views feed a Fabric lakehouse, every KPI is reconciled before the report refreshes, and Power BI answers the questions procurement and finance actually ask.
The problem
Five questions every P2P team asks
Kestrel Foods is a fictional UK food manufacturer with 3 sites, 92 suppliers and about £25m of spend a year. Its procurement and finance teams wanted answers they could trust, not another export from ME2N.
- Are we buying through requisitions and POs, or raising the PO after the invoice arrives?Procurement director
- Which suppliers deliver on time and in full?Supply chain
- How much is sitting on GR/IR, and how old is it?Financial controller
- How many invoices fail first-time match, and are we paying any twice?Accounts payable
- Are we paying on time, or too early and giving away working capital?Finance director
Architecture
Thin extraction in SAP, logic in the lakehouse
A Fabric Data Pipeline runs the four notebooks in order and refreshes the semantic model only if the quality gate passes.
SAP S/4HANA
Source tablesEBAN · EKKO · EKPO · EKET · EKBE · RBKP · RSEG · BKPF · BSEG
ZX_P2P_* CDS viewsextraction-enabled, automatic CDC delta
ODP
Microsoft Fabric lakehouse
01 Bronzeraw Delta tables, lineage, row-count reconciliation
02 Silvertyped and conformed, SAP rules applied once
03 Goldstar schema and KPI snapshot
04 Quality gate54 checks; a failure stops the refresh
pass
Consumption
Direct Lake modelDAX measures over gold flags
Power BI report6 pages, one per stakeholder
What the analysis found
Six findings worth a meeting
Figures come from the generated dataset (seed 42, October 2024 to September 2026). The patterns are the ones real SAP P2P data tends to show.
8 pts
of OTIF lost to short deliveries
On-time is 82.8% but OTIF is 74.6%. 11 of 67 regular suppliers sit below 70% OTIF, and they hold 20% of spend.
£434k
of GR/IR older than 90 days
99 receipts were never invoiced. They need chasing or writing back before year end.
£304k
in duplicate invoices, all paid
60 invoices were keyed in again with a reformatted reference (INV-004512 vs 4512), so SAP's exact-match check missed them. 27 had been blocked and were released anyway.
61%
of blocked invoices paid late
Against 19% for clean invoices. Fixing price variances at source improves both supplier relationships and payment-practice reporting.
£3.7m
paid an average of 25 days early
Working capital handed to suppliers with no early-payment discount in return.
How it's built
Four notebooks, one quality gate
The notebooks are written for Fabric and run end to end on Spark in GitHub Actions on every push, so a broken transformation fails the build before it ever reaches Fabric.
01 · bronze
Land as delivered
Every extract lands as strings with load ID, source file and extraction date. Row counts are reconciled against the extraction manifest.
02 · silver
Handle SAP once
Dates, signs, price units, exchange rates and document links are fixed here, so nothing downstream needs to know SAP's conventions.
03 · gold
Model for questions
A PO-item fact and a supplier-invoice fact carry KPI flags, with conformed dimensions and a UK fiscal calendar.
04 · gate
Prove it's right
Uniqueness, referential integrity, completeness, business rules and reconciliation of all 24 KPIs to an independent calculation.
SAP conventions handled in silver
| SAP convention | Example | Rule |
|---|---|---|
Dates as text, 00000000 = empty | EINDT = 20250314 | Proper date; empty becomes null |
| Debit/credit indicator, not a sign | SHKZG = H on a 102 reversal | Signed quantities and amounts |
| Price unit | NETPR per PEINH = 1000 | Unit price = NETPR ÷ PEINH |
| Inverted rate validity date | GDATU = 79749168 | valid from = 99999999 − GDATU |
| Indirect quotation stored negative | UKURS = −1.17260 | Rate = 1 ÷ |UKURS| |
| Invoice ↔ accounting document | BKPF-AWKEY | Payment linked to the MIRO invoice |
| GR/IR clearing value | EKBE-AREWR, not DMBTR | GR/IR balance at receipt value |
Design decisions
Choices I'd defend in a design review
Table-grain CDS views
Automatic CDC is most reliable at table grain. Joins and KPI rules live in PySpark, where they're versioned, tested and changed without an ABAP transport, and SAP does little extra work.
Bronze stays as strings
A malformed value can't fail a load. Typing happens in silver under explicit, reviewable rules.
KPI logic defined once
Gold facts carry flags like is_otif; DAX measures only aggregate them, so the report and the pipeline can never disagree.
The gate blocks the refresh
If a check fails, users keep yesterday's correct numbers instead of seeing today's wrong ones.
Normalised duplicate matching
Supplier + amount + reference reduced to its digits catches resubmitted invoices that exact matching misses.
Synthetic data with an answer key
The generator calculates every expected KPI independently, so the Spark pipeline is tested against known truth, not just "it ran".
Kestrel Foods and all its data are synthetic, generated to follow real SAP table structures and conventions. No employer or client data, code or screenshots are used. Next in the series: inventory and material movements on MATDOC.
Want the details?
Every line of code is public.
CDS views, notebooks, DAX measures, KPI definitions and the test runner are all in the repository.