These projects show three related controls. One builds shared sales, purchasing and stock tables. One reconciles every item in a complete historical purchase event log. One follows a supplier invoice through an Accounts Payable reviewer workflow.
3 Projects · counts and checks read from retained manifests
Native SQL evidenceThe reporting mart shows row counts, reconciled totals and whether the report can be published alongside the SQL build.
Three engineering decisions
Shared reports, complete reconciliation and human review need different records.
The wholesale model aligns customers, products, dates and totals. The BPI build checks every purchase item. The contained workflow preserves the reviewer question, response, rematch and final invoice decision.
Source-to-decision comparison
Three reporting problems need different controls.
SourceGrainRuleOutputReviewer decision
01Reporting mart
Source
Microsoft Wide World Importers sales, purchase and stock records.
Grain
One posted invoice line, purchase-order line or stock movement in its own fact table.
Rule
Type and key checks reject invalid rows before shared dimensions align the reporting facts.
Output
Monthly commercial mart, product margin report and recorded publish or hold outcome.
Reviewer decision
Release the month or hold it while a named source exception is corrected.
Desktop aligns the five stages for comparison. On smaller screens, each Project keeps the same complete sequence from records to reviewer decision.
01
Operational table contracts
Included files, fields, owners and expected controls are declared before transformation.
02
Declared grain
The row meaning is declared for every transaction, fact, snapshot and exception table.
03
Staging and marts
File preparation remains separate from the reporting tables used by the business.
04
Matching rules
Document joins, reason precedence and tolerances are visible in executable SQL.
05
Tests
Counts, values, keys, dates and exception treatments are checked on each run.
06
Lineage
A result can be followed back through the model to the records that produced it.
07
Reproducible execution
Retained records, code, database and verification scripts rebuild the published results.
The three builds
Different questions require different models.
The first builds reusable reporting tables, the second checks a full research event population and the third follows one invoice through a contained review. Their grains, records and claims are deliberately different.
Shared reporting model
Wholesale Sales, Margin and Stock Reporting Mart
One reporting model for a wholesale month end.
A reporting analyst prepares sales, margin, purchasing and stock figures from Microsoft’s Wide World Importers sample. The build checks each invoice line before it joins shared customers, products and dates.
Decision
Can the May 2016 sales and stock report be released, and which sales, purchase or stock records need correction first?
Declared grain
One posted invoice line, purchase-order line or stock movement in its own fact table.
Checks completed
18 checks passed; InvoiceLineID 1 is followed through 6 stages.
Purchase Order, Receipt and Invoice Reconciliation
Every purchase item receives one supported or held classification.
The complete BPI Challenge 2019 event log records orders, receipts, invoices, reversals and clearing events. The build applies one highest-priority rule to every purchase item and keeps the responsible team beside each hold.
Decision
Can the supplier invoice be released for payment, held for investigation or returned for correction?
Declared grain
One final reconciliation decision for each purchase-order item.
Checks completed
21 checks passed; one purchase-order item is followed through 6 events.
Executed reconciliation of 251,734 purchase-order items, including 34,679 retained payment holds.
Contained reviewer workflow
Supplier Invoice Matching and Review
The reviewer sees the invoice, order and accepted receipt before deciding what happens next.
Finance receives the invoice while Procurement and the warehouse hold the supporting records. Each question, owner response, independent rematch and reviewer decision remains attached to the invoice case.
Decision
Should the invoice enter the separate authorised payment process, remain held or be returned to the supplier?
Declared grain
One open review case per invoice and one append-only event for each review action.
Checks completed
27 checks passed; invoice INV-000004 is followed through 12 events.
INV-000004 compares the £81 invoice price with the £75 approved order price, accepted receipt, supported change and independent rematch before the reviewer decision.
Evidence comparison
What a reviewer can inspect in each build.
The source, population, trace and limitation differ. The entries below are taken from the retained manifests rather than repeated from promotional copy.
Reporting mart
Wholesale Sales, Margin and Stock Reporting Mart
Source and population
Microsoft Wide World Importers sales, purchase and stock records. 228,265 posted invoice lines across 41 business months.
Result available to inspect
Monthly commercial mart, product margin report and recorded publish or hold outcome.
Follow one record
InvoiceLineID 1 is followed from Sales.InvoiceLines through staging, shared keys, the sales fact, validation and the monthly revenue measure.
Validation
18 checks passed; InvoiceLineID 1 is followed through 6 stages.
Scope and limitations
The Microsoft sample does not prove a production database, live refresh or customer scale.
Build status
Executed, independently checked and reproducible from the retained files.
Reconciliation control
Purchase Order, Receipt and Invoice Reconciliation
Source and population
BPI Challenge 2019 purchase-order event log and a retained rule configuration. 251,734 purchase-order items and 1,595,923 retained events.
Result available to inspect
Reconciliation summary and a payment-hold queue containing 34,679 purchase items.
Follow one record
Purchase order item 4507000232_00010 is followed through invoice receipt, goods receipt, clearing and the final payment decision.
Validation
21 checks passed; one purchase-order item is followed through 6 events.
Scope and limitations
The historical anonymised event log supports matching and process analysis; it does not prove a live payment control or the anonymised organisation’s current performance.
Build status
Executed, independently reconciled and reproducible from the retained files.
Reviewer workflow
Supplier Invoice Matching and Review
Source and population
Wide World Importers purchasing structure with deterministic non-client invoice-review records. 2,075 invoice review cases and 8,303 append-only workflow events.
INV-000004 is followed through invoice arrival, three-record comparison, a buyer question, approved price change, independent rematch and reviewer approval.
Validation
27 checks passed; invoice INV-000004 is followed through 12 events.
Scope and limitations
The deterministic non-client records do not prove live Accounts Payable operation, payment execution, Power BI or Power Platform.
Build status
Executed, independently checked and published without a Power BI or Power Platform claim.
Continue from the evidence
Related Solutions and Capabilities.
Start with the commercial reporting problem or inspect the technical practices used to build and check these examples.
