No catalogue items match — clear the filter.
Conceptual model — which dimensions relate to which fact (click a node)
schema for f_sales_line — sourced from SE-001
schema for f_stock_snapshot — sourced from SE-006
Use cases
| ID | Use case | Sponsor | Success criteria | Status | Links |
|---|---|---|---|---|---|
| UC-001 | Weekly trading pack (sales analytics) Commercial exec, trading team, board (monthly) | Sarah WhitfieldChief Commercial Officer | Monday trading pack produced automatically — like-for-like sales, margin and discount by store, channel and category, on desks by 09:00 with data landed by 07:00 | approved | D-0001 D-0003 Q2.2 00:01:12 email |
| UC-002 | Daily stock availability & shrinkage monitoring Supply chain, store operations | Priya NairSupply Chain Director | Previous day's stock position for every store and SKU available before the 08:30 trade call — stock on hand, stockouts, days of cover, shrinkage | approved | D-0002 Q2.3 00:01:05 email |
User stories
| ID | Story | Use case | Owner | Sources | Metrics | Status | Src |
|---|---|---|---|---|---|---|---|
| US-001 | As a trading analyst I want weekly like-for-like sales by store and channel (52-weeks-trading rule) so the Monday pack needs no manual work → superseded by US-008 | UC-001 | James OkonkwoHead of Trading Analytics | S-001 S-003 | M-001 M-002 | confirmed | 00:02:05 00:03:18 |
| US-008 | As a trading analyst I want weekly like-for-like sales by store and channel, counting stores trading ≥52 weeks and excluding stores closed for refit >2 weeks | UC-001 | James OkonkwoHead of Trading Analytics | S-001 S-003 | M-001 M-002 | approved | 00:03:00 email |
| US-002 | As a commercial manager I want gross margin by category computed with the cost in effect at the time of sale, so repriced products don't rewrite history | UC-001 | James OkonkwoHead of Trading Analytics | S-001 S-002 | M-003 | approved | 00:06:24 |
| US-003 | As the CCO I want the online-versus-store revenue split daily, not buried in a monthly deck | UC-001 | Sarah WhitfieldChief Commercial Officer | S-001 | M-001 | approved | 00:10:05 |
| US-004 | As a trading analyst I want markdown spend by category so we know what each promotion actually cost | UC-001 | James OkonkwoHead of Trading Analytics | S-001 S-002 | M-004 | confirmed | 00:11:00 |
| US-005 | As the Supply Chain Director I want yesterday's stock on hand for every store and SKU on one screen before the 08:30 trade call | UC-002 | Priya NairSupply Chain Director | S-004 S-003 S-002 | M-005 | approved | 00:01:05 |
| US-006 | As the Supply Chain Director I want a daily stockout count by store (ranged SKUs with zero on hand at close) so availability problems surface before the Friday call | UC-002 | Priya NairSupply Chain Director | S-004 | M-006 | approved | 00:04:50 00:04:15 |
| US-007 | As the Supply Chain Director I want days of cover per store and SKU as the early-warning number for the trade call | UC-002 | Priya NairSupply Chain Director | S-004 S-001 | M-007 | confirmed | 00:05:00 00:05:40 |
| US-009 | As a marketing lead I want repeat-purchase and customer-segment analytics (DEFERRED to phase 2 — loyalty data too sparse) → superseded by D-0004 | UC-001 | TBDMarketing | S-001 | — | draft | 00:13:30 |
Source systems
S-001 Vista POS + web checkout (order store) Vista Retail Solutions approved
BusinessJames OkonkwoHead of Trading Analytics
TechnicalDan HughesIT Platform Lead
DataJames OkonkwoHead of Trading Analytics
Q4.1 (S-1) 00:14:20
S-002 Merlin product master (PIM) In-house (SQL Server) approved
BusinessJames OkonkwoHead of Trading Analytics (interim — hierarchy ownership OQ-001)
TechnicalDan HughesIT Platform Lead
DataJames OkonkwoHead of Trading Analytics (interim)
Q4.1 (S-2) 00:15:05
S-003 Property store list (SharePoint) SharePoint list (Property team) confirmed
BusinessProperty TeamEstates
TechnicalDan HughesIT Platform Lead
DataProperty TeamEstates
Q4.1 (S-3) 00:15:40
S-004 StockRoom WMS (inventory) In-house approved
BusinessPriya NairSupply Chain Director
TechnicalTom GallagherInventory Systems Manager
DataPriya NairSupply Chain Director
Q4.1 (S-4) 00:02:10
Data-quality findings
| ID | Source | Issue | Sev | Affects | Owner | Remediation | Status |
|---|---|---|---|---|---|---|---|
| DQ-001 | S-001 | Null customer_id on guest checkouts: 45% of online orders have no customer; 64% of all orders overall (in-store captures a customer only on loyalty scan) | medium | US-003 US-009 | Emma RileyData & Insights Lead | Map guest/anonymous orders to the explicit UNKNOWN member on d_customer (D-0005) so revenue always reconciles; do not drop rows | accepted-risk |
| DQ-002 | S-002 | Duplicate natural key in products: 3 SKUs (KW-1017, KW-1042, KW-1058) appear twice — February supplier-feed re-import left stale rows behind when prices changed on 2026-06-10 | high | US-002 US-004 | Emma RileyData & Insights Lead | Dedup on sku keeping the row with the latest updated_at (per Emma — the later row is the live one); raise with Merlin owners for source-side cleanup | open |
| DQ-003 | S-002 | Orphaned order lines: discontinued SKUs are purged from Merlin while still selling through clearance (KW-0817, KW-0823, KW-0844 in the sample extract) — sales rows reference products that do not exist | high | US-002 | James OkonkwoHead of Trading Analytics | Sentinel fallthrough on FK_ProductID in f_sales_line plus an unresolved-FK rate check; longer term, stop purging discontinued SKUs from Merlin | open |
| DQ-004 | S-003 | Stale store list: ST-0011 Croydon Centrale (opened 2026-06-05) trades in POS and snapshots in StockRoom but is missing from the Property list — orphan store_id in both data products | high | US-001 US-008 US-005 | Emma RileyData & Insights Lead | Orphan-store DQ alert on load; interim auto-stub row on d_store for unknown store_ids; fix the Property update process | open |
| DQ-005 | S-004 | Negative qty_on_hand at ST-0006 Glasgow: store posts shrinkage adjustments directly with no zero-floor; rows are real, not load corruption | medium | US-005 US-007 | Tom GallagherInventory Systems Manager | Pass through to bronze/silver unmodified; flag in the availability views and exclude negative rows from days-of-cover; chase stock office to post corrections | open |
| DQ-006 | S-004 | Missing snapshot day: the nightly job failed for ST-0004 Leeds on 2026-06-21 leaving the store-day blank; job does not self-heal (~2×/year) | medium | US-005 US-007 | Tom GallagherInventory Systems Manager | Completeness check (expected store×SKU row count per day) with alert; days-of-cover logic must treat a missing day as unknown, not zero stock | open |
| DQ-007 | S-001 | loyalty_id populated on only ~25% of the customer file — loyalty relaunch stalled; customer-level analytics not viable this phase | low | US-009 | Emma RileyData & Insights Lead | Accepted for phase 1 (drives D-0004 deferral); revisit after the loyalty relaunch completes | accepted-risk |
Conformed silver entities
| ID | Entity | Grain | Business key | SCD | Owner | Sources | Status |
|---|---|---|---|---|---|---|---|
| SE-001 | pos_order | one row per order | order_id | none | James OkonkwoHead of Trading Analytics | S-001 | confirmed |
| SE-002 | pos_order_line | one row per order line | order_line_id | none | James OkonkwoHead of Trading Analytics | S-001 | confirmed |
| SE-003 | product | one row per SKU per validity period (SCD2) | sku | scd2 | James OkonkwoHead of Trading Analytics (interim) | S-002 | confirmed |
| SE-004 | store | one row per store | store_id | scd1 | Property TeamEstates | S-003 | confirmed |
| SE-005 | customer | one row per customer (plus explicit UNKNOWN member) | customer_id | scd1 | James OkonkwoHead of Trading Analytics | S-001 | confirmed |
| SE-006 | stock_snapshot | one row per store per SKU per snapshot day | store_id sku snapshot_date | none | Priya NairSupply Chain Director | S-004 | confirmed |
| SE-007 | stock_receipt | one row per delivery line | receipt_id | none | Priya NairSupply Chain Director | S-004 | confirmed |
Fact × Dimension × Metric matrix
f_sales_line grain one row per order line (transaction fact) · owner James OkonkwoHead of Trading Analytics
| ID | Metric | Definition | Used by | Placement | Owner | Status |
|---|---|---|---|---|---|---|
| M-001 | Net revenue by store / channel / category, week-on-week, like-for-like (per D-0003 store eligibility), vs same week last year | Gross takings excluding VAT (POS unit_price is VAT-inclusive — divide by 1.2), after line-level discounts, before card fees. Returns (negative-quantity lines) net off in the week the return happens. | US-001 US-008 US-003 | Trading pack p.1; daily channel-split view | Mark DaviesFinance Controller | approved |
| M-002 | Units sold by store / channel / category | Sum of order-line quantity, including negative return/exchange lines | US-001 US-008 | Trading pack p.1 | James OkonkwoHead of Trading Analytics | confirmed |
| M-003 | Gross margin margin %, by category | Net revenue minus (units × unit_cost effective at the date of sale, via the SCD2 product dimension — D-0002). Gross of supplier funding pending OQ-002. | US-002 | Trading pack p.2 (category view) | Mark DaviesFinance Controller | confirmed |
| M-004 | Markdown / discount spend discount rate (discount ÷ gross), by category / promotion window | Sum of line_discount excluding VAT (divide by 1.2). Reference case is the mid-season sale, 20% off Womenswear 12–22 June 2026. | US-004 | Trading pack p.2 | James OkonkwoHead of Trading Analytics | confirmed |
f_stock_snapshot grain one row per store per SKU per day (periodic snapshot fact) · owner Priya NairSupply Chain Director
| ID | Metric | Definition | Used by | Placement | Owner | Status |
|---|---|---|---|---|---|---|
| M-005 | Stock on hand by store / SKU / category | qty_on_hand from the nightly snapshot — the closing position of the previous trading day (snapshot job runs ~02:00; NOT the morning opening position). Semi-additive — never sum across days. | US-005 | Morning availability screen (08:30 trade call) | Priya NairSupply Chain Director | approved |
| M-006 | Stockout count by store, by SKU (chronic-stockout ranking) | Daily count of ranged store×SKU positions with qty_on_hand = 0 at close. Zero, not "low". (Sample data reference — KW-1042 Everyday Wrap Dress is out of stock ~56% of store-days.) | US-006 | Morning availability screen; weekly availability summary | Priya NairSupply Chain Director | approved |
| M-007 | Days of cover by store / SKU, thin-cover exceptions (< 7 days) | Stock on hand ÷ average daily units sold over the trailing 28 days (from f_sales_line — cross-product dependency on the conformed store and product dimensions). Missing snapshot days (DQ-006) are unknown, not zero; negative on-hand rows (DQ-005) excluded. | US-007 | Morning availability screen (early-warning column) | Priya NairSupply Chain Director | confirmed |
| M-008 | Shrinkage (units) shrinkage value (units × unit_cost) | Prior-day stock on hand + receipts − units sold − current stock on hand, per store×SKU×day. Caveat — Glasgow's raw negative adjustments (DQ-005) already embed shrinkage; flag, don't double-count. | US-005 | Weekly shrinkage report by store | Priya NairSupply Chain Director | confirmed |
Dimensions
| ID | Dimension | Grain | SCD | Source SE | Owner | Status |
|---|---|---|---|---|---|---|
| D-001 | d_store | store | scd1 | SE-004 | Property TeamEstates | approved |
| D-002 | d_product | SKU | scd2 | SE-003 | James OkonkwoHead of Trading Analytics (interim) | approved |
| D-003 | d_customer | customer (with explicit UNKNOWN member for guest/anonymous orders) | scd1 | SE-005 | James OkonkwoHead of Trading Analytics | approved |
| D-004 | d_channel | sales channel (store / online) | none | — | Sarah WhitfieldChief Commercial Officer | approved |
| D-005 | d_date | calendar day (with UK trading-week and working-day attributes) | none | — | Emma RileyData & Insights Lead | confirmed |
Non-functional requirements
| ID | Category | Requirement | Target | Owner | Status |
|---|---|---|---|---|---|
| NFR-001 | SLA | Data landed and processed by 07:00 UK each weekday; Monday trading pack out by 09:00 | 07:00 data / 09:00 pack, 99% of weeks | Sarah WhitfieldChief Commercial Officer | approved |
| NFR-002 | Data residency | All data remains in the UK; customer PII never leaves UK South | UK South only; no cross-region replication | Dan HughesIT Platform Lead | approved |
| NFR-003 | Retention | Sales transactions 7 years; daily stock snapshots 13 months then monthly aggregates; customer file active + 2 years | per category (Q6.3) | Priya NairSupply Chain Director | confirmed |
| NFR-004 | Concurrency | Support Monday-morning report load | ~80 concurrent report users | Dan HughesIT Platform Lead | draft |
Open questions & contradictions
OQ-001open→ resolve in sources_qualitySarah WhitfieldChief Commercial Officer
Who owns the product hierarchy — trading or merchandising? (Categories restructure every spring and break reporting; S-002 ownership is interim)
surfaced from: transcript kickoff @ 00:18:00: 'who actually owns the product hierarchy — trading or merchandising?'
OQ-002open→ resolve in design_refinementMark DaviesFinance Controller
Is supplier-funded markdown netted off gross margin in the trading pack? (CFO policy call; margin stays gross of supplier funding until resolved)
surfaced from: transcript kickoff @ 00:18:40 + email <KWR-FIN-20260617-0412@kingswayretail.co.uk>: 'Leave margin gross of supplier funding for now'
OQ-003resolved→ resolve in sources_qualityTom GallagherInventory Systems Manager
CONTRADICTION (resolved): is the 02:00 snapshot the morning opening position (Priya's assumption) or the previous day's close (Tom)? Resolution: previous trading day's close, confirmed in writing.
surfaced from: stock-deepdive @ 00:02:48 vs 00:03:05; resolved by email <KWR-OPS-20260624-1187@kingswayretail.co.uk>
OQ-004resolved→ resolve in design_refinementJames OkonkwoHead of Trading Analytics
CONTRADICTION (resolved): like-for-like = 52 weeks trading only (James, kickoff) vs also excluding refit closures (Sarah, design review). Resolution: D-0003 — 52 weeks AND exclude refit closures >2 weeks.
surfaced from: kickoff @ 00:03:18 vs design-review @ 00:02:15; resolved design-review @ 00:03:20
Decision log
| ID | Decision | Status | Decided | Approver | Evidence | Affects |
|---|---|---|---|---|---|---|
| D-0001 | Net revenue = ex-VAT, after line discounts, before card fees | approved | 2026-06-17 | Mark Davies | — | M-001 US-001 UC-001 |
| D-0002 | Product price/cost history kept as SCD2 on d_product (platform-side — Merlin overwrites in place) | approved | 2026-06-30 | Dan Hughes | — | D-002 SE-003 M-003 S-002 |
| D-0003 | Like-for-like definition: stores trading ≥52 weeks, excluding stores closed for refit >2 weeks | approved | 2026-06-30 | Sarah Whitfield | — | US-001 US-008 M-001 |
| D-0004 | Customer-level analytics deferred to phase 2 (loyalty data too sparse) | approved | 2026-06-16 | Sarah Whitfield | — | US-009 DQ-007 |
| D-0005 | Guest/anonymous orders map to an explicit UNKNOWN member on d_customer | approved | 2026-06-30 | James Okonkwo | — | DQ-001 SE-005 D-003 |