The shared data foundation for BI and AI
The Sales Audit Data Platform organizes source activity into traceable warehouse records, common business definitions, and versioned analytical inputs. BI reports and AI models can then explain their results using the same underlying evidence.
The Sales Audit retail module owns the operational work: assignments, approvals, corrections, and close decisions. The warehouse preserves the source data and decision history needed to report on that work. Approved changes remain owned by the designated operational or financial system.
This reference model describes the data contract to map and validate for a deployment. Source availability determines which datasets, measures, and features can be delivered.
Source contracts before transformation
For every feed, agree on the source owner, record identifier, event type, business date, currency, expected delivery, and correction behavior. Retain both source event time and received time so a late delivery is distinguishable from a late transaction.
| Source family | Required evidence | Contract checks |
|---|---|---|
| POS and cash office | Transaction headers/lines, payment events, counts, floats, deposits and source control totals. | Expected registers, receipt sequences, business-date cutoff, time zone and replay behavior. |
| Orders and fulfillment | Order headers/lines, fulfillment events, cancellations, returns and payment references. | Partial events, cross-channel references and source status meanings. |
| Processors and banks | Settlement batches/lines, fees, refunds, chargebacks and bank-statement lines. | Merchant/account scope, currencies, delivery calendar and statement identifiers. |
| ERP and reference masters | Journal lines, posting responses, account mappings, items, prices, promotions, tax and tender references. | Source authority, effective dates, code mappings and historical versions. |
| Audit application | Store-day status, findings, case history, approvals and reviewed outcomes. | Change timestamps, actor/role, record links, version sequence and ownership. |
From source history to analytical outputs
- 01
Land source records
Keep original values, source identifiers, received times, and batch or message references.
- 02
Validate and conform
Map codes, check completeness and duplicates, resolve references, and quarantine rejected records.
- 03
Link and retain history
Load each record at its declared grain. Connect related events and preserve corrections and versions.
- 04
Publish BI measures
Apply shared definitions, approved filters, aggregation rules, and visible quality status.
- 05
Serve AI features
Create time-bounded features with source lineage, comparison context, and recorded versions.
- 06
Monitor and recover
Track freshness, rejected data, late arrivals and downstream delivery. Replay without duplicate effects.
Nightly batch is a possible baseline; intraday refresh depends on source capability and the agreed schedule. Record a processed-through position for each feed. A successful pipeline run does not prove that every expected record arrived.
Late data and corrected source records should trigger the relevant restatement or feature refresh with history retained. Publish provisional and validated states explicitly so consumers can select the appropriate data for their task.
The data model: connected records, explicit relationships
Audit retains the activity needed to explain a result: sales and returns, voids, drawer events, cash differences, and unresolved payments. Reporting views select the relevant activity for each measure while preserving access to the source evidence.
The five core data areas below have different record structures. A cash session or settlement may cover many tickets; an online order may produce several fulfillments and payments. Link them through scoped source identifiers and documented relationships, rather than assuming they all share one ticket-level key.
| Data area | Record grain | Purpose and relationship |
|---|---|---|
| Transaction headers & lines | One header per source transaction; one line per source line or line event. | Preserve all ticket types, including header-only no-sales. Count distinct transaction IDs within the selected scope. |
| Payment events | One row per source payment event: authorization, capture, refund, or reversal. | Retain event type, tender, amount, currency, status, and related transaction. A tender-column matrix is a reporting view. |
| Cash counts & declarations | One row per count attempt, till, tender, and session; a declaration identifies the accepted attempt. | Keep recount history. Use the latest accepted count, including zero; distinguish missing counts from zero amounts. |
| Ledger journals | One row per journal entry line, identified by entity, journal ID, and line ID. | Retain source links, debit/credit amounts, currency, account mapping version, and posting response. |
| Orders & fulfillment | Separate order headers, order lines, fulfillment events, and links to POS and payment records. | Support split fulfillment, cancellations, and cross-channel returns without assuming one order equals one ticket. |
Identity and aggregation: Retain source system, legal entity, source record ID, business date, and original timestamps as applicable. Define uniqueness per source. Aggregate independent measures to a compatible level before combining them, so multiple payment or journal lines do not multiply sales totals.
Records that make the workflow auditable
The operational module or workflow service owns these decisions and state transitions. Replicate their history into the warehouse for traceability and analysis; a report refresh must not itself approve a correction or close a store-day.
| Record family | Record structure | What it preserves |
|---|---|---|
| Store-day control | One record per legal entity, store, and business date, with status history. | Expected and received feeds, blocking findings, owner, cutoff, approval, reopen reason, and close timestamps. |
| Exceptions & cases | One finding per rule evaluation and affected record; one case may group several findings. | Rule/version, evidence links, severity, exposure, assigned owner, deadline, resolution, and reviewer disposition. |
| Adjustments & approvals | One proposed adjustment with a sequence of decisions and resulting posting references. | Original and proposed values, reason, preparer, approver, timestamps, and correction or reversal links. |
| Settlements & bank receipts | Separate processor settlement batches/lines and bank-statement lines. | Gross and net amounts, fees, refunds, chargebacks, reserves, bank account, currency, expected date, and actual receipt date. |
| Match allocations | One allocation linking eligible source records within a reconciliation group. | Allocated amount and currency, method, rule version, status, reviewer, and unmatched remainder. Reverse allocations with history. |
| Processing history | One event per ingestion, validation, delivery, or retry attempt. | Source record ID/version, batch, event and receipt times, completeness checks, rejection reason, duplicate-prevention key, and downstream acknowledgment. |
Explore the attribute dictionary
Expand a data area to see its principal fields and controls. Names are a reference contract; source mappings, optional fields, and retained history are confirmed for each deployment.
Transaction audit
Keep source activity and audit evaluations distinct. A legitimate sale type such as a return is not, by itself, a failed control.
| Group | Attributes | Control purpose |
|---|---|---|
| Identity & dates | Source system, legal entity, source transaction ID, site, terminal, printed receipt, line ID, continuation reference, business date, event time, time zone, receipt time. | Receipt counters can repeat. Preserve original keys and use a scoped transaction ID for linking and distinct counts. |
| Type & status | Sale, return, void, no-sale, over/short, deposit, layaway, quote; separate source status, audit status, and posting status. | Map source codes explicitly. Posting eligibility depends on the activity and accounting policy, not its display category. |
| People & products | Cashier, credited salesperson, customer/loyalty reference, role, home site; SKU, style, colour, size, department, class, vendor, stock flag. | Keep cashier and credited seller separate. Use approved access to person-level evidence and contextual peer comparisons. |
| Prices & amounts | Quantity, charged amount, regular and original price, raw discount, analytical discount, excluded amount, markdown, tax, cost, currency, reference-data version. | Preserve source amounts. Reconcile analytical exclusions separately; only approved corrections change financial records. |
| Classification & findings | Primary display category, applicable rule findings, reason code, raw reason text, resolved reason, resolution status, data-error flag. | A discounted return may also have an override finding. Do not discard secondary findings. |
| Evaluations & features | Rule/version, evaluation time/result, trading-window version, feature snapshot, comparison group, model version, score, drivers. | Retain the evidence used for the decision even if current reporting views later recalculate flags. |
Payment events & tender reporting
Store individual payment events and derive ticket-level tender summaries from eligible completed events.
| Group | Attributes | Control purpose |
|---|---|---|
| Event identity | Payment event ID, original payment reference, source transaction/order ID, gateway, merchant, terminal, event type, event time, status. | Distinguish authorizations from captured money. Link refunds and reversals to their original event. |
| Tender & amount | Tender code, card brand where available, amount, currency, exchange-rate source/date, settlement reference. | Support repeated uses of the same tender and split payments; retain each event. |
| Reporting view | Transaction total, eligible tender total, variance, deposit application, gift-card and credit-note amounts. | One column per tender can be generated for display without changing the underlying event structure. |
| Reconciliation | Matching group, matched amount, remaining amount, status, reason, reviewer. | Payment balance, processor settlement, and bank receipt are separate checks. Do not count a payout as another customer payment. |
Cash counts & declarations
Keep all count attempts and explicitly identify the accepted declaration.
| Group | Attributes | Control purpose |
|---|---|---|
| Session & attempts | Store, till, tender, currency, session ID, count-attempt ID, timestamp, counting employee. | Maintain source chronology and recount history. |
| Accepted declaration | Accepted-attempt ID, counted amount, acceptance time, accepted by, declaration status. | Use the latest accepted count, including zero. A missing count remains missing; an unapproved recount does not replace an accepted one. |
| Expected cash & deposits | Opening float, cash receipts/refunds, paid-ins, paid-outs, cash drops, retained float, deposit reference. | Reconcile each movement once. Derive deposits from the configured cash process, not an assumed universal formula. |
| Controls | Expected amount, variance, tolerance/version, over-tolerance flag, reviewer, close status. | Compare within the same tender and currency; report converted totals separately. |
Ledger journals & posting
Keep journal-line detail and trace both individual and aggregated postings to their source records.
| Group | Attributes | Control purpose |
|---|---|---|
| Journal identity | Legal entity, journal ID, journal-line ID, account, department, store/cost center, source references, posting date, period. | A posted journal may summarize many transactions. Use explicit links to its contributing records. |
| Amounts & validation | Debit, credit, transaction and functional currency, exchange rate, journal balance, validation result. | Validate each posting unit before release. Day totals supplement entry-level checks; running balances are calculated reporting values. |
| Account mapping | Mapping ID/version, effective dates, revenue, discounts, tax, tender, liability, COGS, inventory and adjustment accounts. | Resolve accounts using approved mappings and retain the mapping used at posting time. |
| Approval & delivery | Adjustment ID, preparer, approver, approval time, export ID, duplicate-prevention key, receiver document ID, response, retry/reversal references. | Exported does not mean accepted. Link rejected deliveries and controlled retries without duplicate posting. |
Orders, fulfillment & cross-channel links
Keep orders and fulfillment records separate from payment events and settlement lines.
| Group | Attributes | Control purpose |
|---|---|---|
| Order & lines | Channel, source platform, order ID, order-line ID, currency, ordered amount, discount, shipping, tax. | Retain source identity without requiring every online order to become a ticket on a dedicated store. |
| Fulfillment & returns | Fulfillment event ID, order-line link, quantity, location, event time, original sale reference, return/refund link. | Support multiple fulfillments per order and returns through another store or channel. |
| Relationships | Order-to-ticket, order-to-payment and fulfillment-to-line links, allocated quantity/amount, link status. | Document one-to-many relationships. Aggregate at the intended reporting level before combining measures. |
| Assurance state | Missing counterpart, partial fulfillment, amount/refund difference, settlement pending, matching status. | A fulfilled order, a captured payment, and a received payout have separate completion states. |
Settlements, bank receipts & matching
Reconcile received funds using separate source records and auditable allocations.
| Group | Attributes | Control purpose |
|---|---|---|
| Processor settlement | Batch ID, line ID, merchant/gateway, event reference, gross amount, fee, refund, chargeback, reserve, net amount, currency, settlement date. | Retain each adjustment type and its original evidence. |
| Bank receipt | Bank account, statement ID, statement-line ID, reference, amount, currency, booking date, value date. | A bank line can cover multiple processor batches; preserve original statement references. |
| Matching allocation | Group ID, linked source IDs, allocated amount/currency, method, rule/version, approval and reversal history. | Support one-to-many and many-to-one matches. Prevent over-allocation and retain unmatched remainders. |
| Open items | Expected receipt date, age, remaining value, exception ID, owner, status. | Keep timing differences visible after store close until settlement is confirmed. |
Store-day, exception & approval records
Persist operational decisions as well as reporting measures.
| Group | Attributes | Control purpose |
|---|---|---|
| Store-day | Entity, store, business date, expected feeds/registers, received feeds, last valid batch, blocking findings, cutoff, owner, close status. | Track completeness, validation, approval, export, and downstream acceptance separately. |
| Finding & case | Finding ID, rule/version, affected-record links, case ID, severity, exposure, owner, due date, evidence, notes, disposition. | Group related findings without losing their individual history or double-counting financial exposure. |
| Correction & approval | Adjustment ID, original/proposed values, reason, preparer, approver, decision, timestamps, applied change and posting links. | Separate preparation and approval where required; preserve rejected proposals and authorized reopenings. |
| Review feedback | Reviewer, outcome reason, evidence, labeling status, review date. | Distinguish legitimate activity, source errors, confirmed issues, duplicate alerts, and insufficient evidence. |
Processing history & reproducibility
Use source lineage and versioned evaluations to reconstruct what an auditor saw.
| Group | Attributes | Control purpose |
|---|---|---|
| Ingestion | Source ID/version, file/message ID, batch, received time, source cutoff, expected/received record counts and control totals. | A successful load does not prove completeness. Compare with source expectations. |
| Validation | Rule/version, reference-data version, evaluation ID/time, outcome, rejected-record reference. | Keep historical evaluations when rules or master data change. |
| Delivery & recovery | Destination, export ID, duplicate-prevention key, acknowledgment, receiver reference, retry count, correction/reversal link. | A replay must preserve lineage and avoid duplicate side effects. |
| ML evidence | Feature snapshot/as-of time, model version, peer group, score, drivers, source links, reviewer outcome. | Evaluate reviewed labels before retraining; record validation and approval for each model release. |
BI subject areas and shared measure definitions
Define the reporting level, eligible records, business date, currency treatment, and denominator for each measure. Dashboards and extracts should use the same definitions rather than reconstruct them independently.
| Subject area | Measures and reporting level | Definition to preserve |
|---|---|---|
| Transaction audit | Sales/return activity, exceptions and affected transactions by store, day, channel and rule. | Count distinct transactions within the selected scope; multiple findings do not become multiple sales. |
| Tender reconciliation | Eligible payment total and ticket variance by transaction and business date. | Separate authorization from captured funds, refunds and reversals. |
| Cash office | Expected cash, accepted declaration and over/short by till, session, tender and currency. | An accepted zero is valid; an absent or unapproved count is not a zero. |
| Settlement and bank matching | Matched and remaining amounts, fees and open-item aging. | Retain allocation detail and avoid counting a processor payout as another customer payment. |
| Close and case operations | Store-day completeness, blocking findings, case age and resolution time. | Distinguish case creation, approval, closure, reopening and the as-of reporting time. |
| Financial posting | Released, accepted, rejected and pending posting units by entity and period. | An exported batch is not necessarily accepted by its destination. |
| Omnichannel and stock context | Order/fulfillment/payment links and referenced inventory movements. | Keep split events separate and state which system owns each movement. |
Aggregation rule: Summarize independent facts at a compatible reporting level before combining measures. Directly joining multiple payment rows to multiple journal rows can multiply amounts. See Kimball's guidance on combining fact-table results.
Data quality, history, and governed access
- Completeness: Compare expected feeds, record counts and control totals with received data. Show missing and rejected activity separately.
- Uniqueness and recovery: Scope identifiers to their source and entity. Distinguish duplicate delivery from a legitimate correction; keep the replay history.
- Raw and analytical values: Preserve raw amounts alongside exclusions, normalized values and reasons. An analytical exclusion is not an authorized financial correction.
- Multiple findings: Keep a primary display category and every applicable rule finding. Classification must not erase overlapping exceptions.
- Reference resolution: Retain effective-dated mappings, original codes and unresolved values. Do not invent reasons or silently substitute missing dates.
- Reproducibility: Store the evaluation, rule, mapping and feature versions used for each decision. Historical results stay attributable when current views change.
- Access and retention: Scope store and entity access by role, restrict person-level detail, record access where required, and apply the agreed retention policy to source history and exports.
AI features with time and evidence attached
Feature preparation should preserve what was available at the time of scoring. A later refund, corrected receipt, or final investigation outcome must not leak into an earlier training example as though it were already known.
| Feature family | Examples | Context and history |
|---|---|---|
| Activity rates | Return share, override rate, discount share and tender variance rate. | Eligible population, denominator, lookback period and minimum sample size. |
| Peer comparisons | Deviation from comparable store, channel, cashier role or promotion patterns. | Peer-group definition, season/calendar context and reference version. |
| Timing and recurrence | After-hours activity, repeated discrepancies and settlement delays. | Trading-window version, event time, receipt time and settlement calendar. |
| Case outcomes | Confirmed issue, legitimate exception, source error, duplicate finding or insufficient evidence. | Reviewer, supporting evidence, disposition reason and label-review status. |
Record feature snapshots, source lineage, model version, score time and explanation drivers. Evaluate label quality and model performance before releasing an update. A dismissed finding is not automatically a negative training label, and a warehouse feature pipeline does not change operational approval rules.
Acceptance evidence for the data foundation
| Check | Evidence to deliver |
|---|---|
| Source reconciliation | Mapped counts and control totals tie to representative source periods, with documented differences. |
| Relationship integrity | Split payments, multiple fulfillments, aggregated postings and partial settlements retain correct totals. |
| History and recovery | Repeated delivery, late records, zero counts, corrections and reversals are handled without loss of evidence. |
| BI consistency | Selected reports and extracts return the same measure under the same filters and reporting date. |
| Feature reproducibility | A sample score can be traced to its source inputs and feature/model versions as of scoring time. |
| Operational observability | Each source has freshness, completeness, rejection and recovery status with an assigned owner. |
Map, validate, and hand over the platform
Agree on deliverables and readiness gates before scheduling a deployment. Duration depends on source access, history, mapping gaps, and delivery interfaces.
- Discover: Inventory feeds, data owners, identifiers, cutoffs, reference masters and analytical requirements.
- Map and load: Establish source contracts, declared grains, lineage, control history and recovery behavior.
- Validate: Reconcile representative periods, test edge cases, and sign off shared measures and feature definitions.
- Hand over: Deliver the mapping dictionary, quality exceptions, acceptance evidence, monitoring and support ownership.
The Sales Audit module rollout uses this foundation to prove the operational workflow. For the business reasoning behind these controls, read the Sales Audit theory article.