Skip to main content
Quanta Meridian logo

Solution Example collection 03

KPI Definitions and Data Quality

These projects answer three connected questions. Are the order and delivery records fit to use? Why do SQL, Excel and Power BI show different results? How is a reporting defect fixed, independently retested and cleared for release?

The published projects retain executable checks, individual failed rows and the decisions taken after review.

Metric comparisonFive implementations of on-time delivery are recalculated and bridged to one agreed definition.

Two disciplines

Agree the calculation. Check the records.

Finance and Operations currently calculate on-time delivery differently. The first pair of projects agrees the date, population and treatment of partial orders. The second pair stops contradictory records and keeps a missing product mapping open until a second analyst confirms the correction.

On-time delivery definition comparisonDispatch, partial receipt and final customer receipt dates enter one definition record. Finance and Operations compare their rules before the agreed calculation is used in SQL, Excel and Power BI.ON-TIME DELIVERY DEFINITIONOrder and promisePopulationDispatch eventOperations dateCustomer receiptFinance dateON-TIME DELIVERYMonthly service reviewOrder populationReceipt date rulePartial + cancelledAgreed effective dateSQL resultExcel checkPower BI measure88.9% Finance91.4% Operations
Finance and Operations first agree the date and order treatment. The same calculation can then be tested in every reporting tool.
Validation and issue lifecycleOrder and delivery records pass through named checks. A missing product mapping is corrected by the product data owner and independently retested before report release.ORDER AND DELIVERY CHECKSMonthly recordsOrderDelivery eventProduct codeConfiguredrulesNamed checksChecked ordersService reportMissing product mappingCode addedSecond analyst retests
A failed order remains visible until the missing product code is corrected, a second analyst reruns the check and the reporting owner releases the corrected report.

How the work connects

A definition, a failed check and an open issue are not the same record.

A calculation rule explains what should be counted. A data check tests whether the records can support that calculation. Reconciliation explains why existing reports differ. The issue register follows a specific defect through correction and retest.

  1. 01

    Define

    Agree what on-time delivery means.

    State whether delivery is measured at dispatch or customer receipt and how partial and cancelled orders are counted.
  2. 02

    Test

    Check the orders and delivery events.

    Stop missing receipts, impossible dates, duplicate shipments and unknown products before the monthly service report.
  3. 03

    Reconcile

    Explain why four reports disagree.

    Recalculate SQL, Power BI, Excel and the management pack from the same order population and bridge each difference.
  4. 04

    Close

    Correct and independently retest the defect.

    Add the missing product code, rerun the failed record and release the supplier report only after the test passes.

Metric definitions

Agree when an order counts as delivered.

Finance uses customer receipt, Operations uses dispatch and one report counts delivered lines. The work agrees the population, date and treatment of split, partial and cancelled orders.

Definition flagship

Order-to-Delivery Metric Dictionary

Finance reports on-time delivery at customer receipt. Operations uses dispatch date, while one report counts delivered lines. The project agrees how partial and cancelled orders should be counted.

Organisation
Wholesale distribution
Decision
Which on-time delivery definition belongs in the monthly service report, and how should split, partial and cancelled orders be treated?
One record to trace
Order ORD-0004001 has two lines despatched before promise, then two customer receipts; the final receipt is two days late and determines the agreed result.
Platform
SQL, Excel and Power BI measure definitions
Verified data
120,000 orders, 250,000 lines and 180,000 delivery events, verified
Boundary
The example definition is not automatically right for another organisation.
Evidence status
The native build, validation and record trace passed the publication checks.
Status
Native Excel catalogue and executed SQL verified. Equivalent DAX and TMDL retained; Power BI Desktop execution not claimed.
Inspect implementation and evidence
Native Excel comparison showing Finance at 88.9 percent, the current Power BI report at 91.4 percent and the agreed OTIF rule at 88 percent
Rendered from the retained V3 Excel workbook. The SQL and Excel results are verified; the DAX definition has not been executed in Power BI Desktop. Open full evidence

Supporting reconciliation

On-time Delivery Metric Reconciliation

The SQL mart shows 38.81%, Power BI 38.38%, the Excel management pack 62.86%, the operational extract 38.83% and the manually maintained report 39.37%. The build explains the formula, grain, delivery event, filter or refresh time behind each value.

Organisation
Finance and operations reporting
Decision
Which result should be used in the next review, and what must change in each reporting implementation?
One record to trace
Split order ORD-0000001 is on time when Excel uses the first receipt and late when the approved rule waits for final receipt.
Platform
DuckDB, SQL, Excel, retained DAX and report snapshots
Verified data
120,000 orders, 250,000 lines and 180,000 delivery events, verified
Boundary
The bridge explains implementation differences; it does not establish that one business definition suits every use.
Evidence status
The native build, validation and record trace passed the publication checks.
Status
Native DuckDB, SQL and Excel reconciliation verified across five implementations; 19 of 19 checks pass; retained DAX not executed in Power BI Desktop.
Inspect implementation and evidence
On-time Delivery Metric Reconciliation comparing five published percentages with one approved result
Rendered from the retained V3 build after all five reporting implementations were independently recalculated. Open full evidence

Order data checks

Stop contradictory records before the service report.

Each failed order keeps its reason and responsible team. A missing product mapping remains open until the data owner corrects it and a second analyst reruns the check.

Executable validation flagship

Order and Delivery Data Quality Checks

Incomplete or contradictory order records are stopped before the monthly service report. Each failed row keeps a specific reason, the team that can correct it and the result of the next test.

Organisation
Customer service and warehouse operations
Decision
Can the service report be released, released with a stated limitation or held for correction?
One record to trace
Delivered order ORD-0000001 is held because its customer code is missing, corrected by the customer data owner and admitted only after DQ-01 passes an independent retest.
Platform
Configuration-driven SQL and Python checks
Verified data
120,000 orders, 250,000 lines and 180,000 delivery events, verified
Boundary
A failed check is not automatically a data incident; impact and ownership determine whether longer action is needed.
Evidence status
The native build, validation and record trace passed the publication checks.
Status
Native Python and DuckDB build verified; all 15 rules pass on independent retest.
Inspect implementation and evidence
Order and delivery data quality evidence showing held orders, executed rules and receipt before despatch corrected before release
Generated by the executed Python and DuckDB build using deterministic non-client order and delivery data. Open full evidence

Reporting defect and retest

Reporting Data Issue and Retest Register

A missing product code causes one supplier category to disappear from the monthly report. The product data owner corrects the reference table, a second analyst reruns the failed check and the reporting owner decides whether to release the report.

Organisation
Supplier reporting
Decision
Has the missing mapping been corrected and independently retested well enough to release the supplier report?
One record to trace
Order line OL-0001001 moves from PRD-UNKNOWN to PRD-01001, Product category 06, a passing independent SQL retest and report release.
Platform
Python, DuckDB, SQL and Excel, with Microsoft Lists and Power Automate deployment designs
Verified data
120,000 orders, 250,000 lines and 180,000 delivery events; 60 incidents across 6 reporting cycles, verified
Boundary
The contained example does not prove a live Microsoft 365 workflow, production security or enterprise-wide data observability.
Evidence status
The native build, validation and record trace passed the publication checks.
Status
Native Python, DuckDB, SQL and Excel build verified with 28 of 28 controls passing; no live Microsoft 365 tenant claimed.
Inspect implementation and evidence
Verified Excel reporting issue register showing 60 defects, independent retests and the missing product mapping cleared before release
Rendered from the native Excel register after the DuckDB issue model, SQL retest and 20 build controls passed. Open full evidence

Continue from the evidence

Start with the reporting consequence. The appropriate technical response depends on whether the immediate need is a governed calculation, an executed check or an owned correction.

Capabilities used

SQLRecalculate the order population, compare order records and retain each exception.
PythonExecute configuration-driven rules and preserve run and failed-record evidence.
Power BIImplement explicit measures whose formula and filter context match the approved definition.

Start with the disputed figure or failed report

Bring the definition, order extract and reporting deadline.

The first review can establish whether the problem sits in the measure rule, the order records, an inconsistent implementation or an unresolved owner action.

Discuss the reporting problem