Data case / 01 · Grain, semantics, time & currency

Designing a governed commercial performance data model

Orders, invoices, targets and customer context do not become a KPI merely because they can be joined.

The case in brief

Current reality

Orders, invoices, targets and customer context arrive at different grains, from different systems, at different times and under different correction rules—so the same performance question gets a different answer in every report.

What must become true

Every commercial metric is reproducible from governed facts whose meaning, grain, identity, period, version, authority, correction behaviour, currency treatment and freshness are explicit—so two consumers asking the same business question receive the same answer.

Design question

What does each commercial fact mean, at what grain and period—and how do we make every downstream metric reproduce the same business meaning?

01The reality · current state

What the data does today.

A commercial organization sells through several of its own companies, each with its own ERP and currency. Commercial teams want one view of performance per customer, product line and period. Today each region exports ERP data into spreadsheets, joins it to the CRM by customer name, phases annual targets its own way and converts currency at its own rate. The problem looks like a dashboard problem. It is a semantic-model problem.

  • Order lines and invoice lines are joined directly, so partial deliveries are counted twice
  • “Actual” means ordered in one report and invoiced in another
  • Customers are matched by name; groups buying through several legal entities split or collide
  • Annual targets are compared with year-to-date actuals, and mid-year revisions overwrite past months
  • Credit notes and cancellations are removed or ignored depending on who built the report
  • Each region converts currency with its own rate, and a corrected rate silently rewrites last year
  • Nobody can say which version of a number a decision was taken on

02The semantic problem

Which meanings are mixed today.

Most disagreements were not calculation errors. They were different meanings travelling under the same word.

  • Ordered vs invoicedA commitment and a recognised sale were both called “actual”.
  • Annual vs phased targetA full-year number was compared with part of a year.
  • Signed target vs planning figureThe commercial commitment and the finance budget shared one column.
  • Open order vs realised revenueWhat is still to be delivered was added to what has been invoiced—or not, depending on the report.
  • Original vs reporting amountConverted numbers replaced the amount that was actually invoiced.
  • Correction vs editCredit notes were netted, deleted or ignored instead of being facts of their own.

03Key dimension · Grain, semantics, time & currency

Every fact is a contract before it is a number

Each fact keeps its native meaning; the contract says what it means, at which grain, who may change it, which time it belongs to, how it is corrected, how currency is treated, how fresh it is—and what it is used for.

Select a fact to read its contract. Arrow keys move between facts.

01Orders

Meaning
Commitment to buy
Grain
Order line × version
Authority
ERP
Time semantic
Order date
Correction rule
New version; cancelled is a status
Currency
Original + reporting
Freshness
Daily
Used for
Open orders, pipeline realism

02Invoices

Meaning
Recognised sale
Grain
Posted invoice line
Authority
ERP (finance)
Time semantic
Posting date
Correction rule
Credit-note line, never an edit
Currency
Original + reporting
Freshness
Daily
Used for
Realised revenue

03Targets

Meaning
Signed commercial commitment
Grain
Account × product line × month
Authority
Commercial planning
Time semantic
Target period, phased
Correction rule
New version from an effective date
Currency
Reporting, at the planning rate
Freshness
On publication
Used for
Performance to target

04Customer context

Meaning
Governed commercial account
Grain
Account × validity period
Authority
CRM & master data
Time semantic
Valid from – to
Correction rule
Merge keeps the surviving key
Currency
—
Freshness
Hourly
Used for
Where facts meet

05Derived performance

Meaning
Actual against target, year to date
Grain
Account × product line × month
Authority
Data platform (derived)
Time semantic
Reporting period, as of
Correction rule
Recomputed; published by version
Currency
Reporting, declared basis
Freshness
Daily; final at close
Used for
Segments, reviews, signals

Compare all 5 in one table
Commercial fact contract: for each fact, its meaning, grain, authority, time semantic, correction rule, currency treatment, freshness and use
OrdersInvoicesTargetsCustomer contextDerived performance
MeaningCommitment to buyRecognised saleSigned commercial commitmentGoverned commercial accountActual against target, year to date
GrainOrder line × versionPosted invoice lineAccount × product line × monthAccount × validity periodAccount × product line × month
AuthorityERPERP (finance)Commercial planningCRM & master dataData platform (derived)
Time semanticOrder datePosting dateTarget period, phasedValid from – toReporting period, as of
Correction ruleNew version; cancelled is a statusCredit-note line, never an editNew version from an effective dateMerge keeps the surviving keyRecomputed; published by version
CurrencyOriginal + reportingOriginal + reportingReporting, at the planning rate—Reporting, declared basis
FreshnessDailyDailyOn publicationHourlyDaily; final at close
Used forOpen orders, pipeline realismRealised revenuePerformance to targetWhere facts meetSegments, reviews, signals
  • FactOrders
    Meaning
    Commitment to buy
    Grain
    Order line × version
    Authority
    ERP
    Time semantic
    Order date
    Correction rule
    New version; cancelled is a status
    Currency
    Original + reporting
    Freshness
    Daily
    Used for
    Open orders, pipeline realism
  • FactInvoices
    Meaning
    Recognised sale
    Grain
    Posted invoice line
    Authority
    ERP (finance)
    Time semantic
    Posting date
    Correction rule
    Credit-note line, never an edit
    Currency
    Original + reporting
    Freshness
    Daily
    Used for
    Realised revenue
  • FactTargets
    Meaning
    Signed commercial commitment
    Grain
    Account × product line × month
    Authority
    Commercial planning
    Time semantic
    Target period, phased
    Correction rule
    New version from an effective date
    Currency
    Reporting, at the planning rate
    Freshness
    On publication
    Used for
    Performance to target
  • FactCustomer context
    Meaning
    Governed commercial account
    Grain
    Account × validity period
    Authority
    CRM & master data
    Time semantic
    Valid from – to
    Correction rule
    Merge keeps the surviving key
    Currency
    —
    Freshness
    Hourly
    Used for
    Where facts meet
  • FactDerived performance
    Meaning
    Actual against target, year to date
    Grain
    Account × product line × month
    Authority
    Data platform (derived)
    Time semantic
    Reporting period, as of
    Correction rule
    Recomputed; published by version
    Currency
    Reporting, declared basis
    Freshness
    Daily; final at close
    Used for
    Segments, reviews, signals
From facts to consumers
  1. 01Source factsKept at native grain, every version
  2. 02Conformed modelDeclared grain, governed identity, time and currency
  3. 03MetricOne definition, one owner, one version
  4. 04ConsumerDecision views and CRM signals, stamped with an as-of date

A metric is not a formula. It is a semantic contract—and a number without grain, time, owner and version is not governed information.

04Grain & contracts

What one row or message means.

Meaning, grain, key, freshness and owner—written down before anything is mapped.

Contract

Order line

A commercial commitment; changes until delivered or cancelled.

Grain
One row per order line per version
Key
Company + order + line + version
Freshness
Daily, before the working day
Owner
ERP data owner
Contract

Invoice line

A recognised sale. Corrected by credit notes, never edited.

Grain
One row per posted invoice line
Key
Company + invoice + line
Freshness
Daily, before the working day
Owner
Finance
Contract

Target

A published plan, phased by month; partial-year targets start at their effective month.

Grain
Account × product line × month × version
Key
Target version + account + line + month
Freshness
On publication
Owner
Commercial planning
Contract

Exchange rate

A rate of a declared type for a date or month.

Grain
Currency pair × rate type × date × version
Key
Pair + type + date + version
Freshness
Monthly; corrections versioned
Owner
Treasury
Contract

Customer dimension

One commercial account as it was on each date, linked to its legal entities and ERP customers.

Grain
One row per account per validity period
Key
Analytical customer key
Freshness
Hourly from CRM events
Owner
CRM data owner
Why this grain
Chosen grain
Account × product line × month, with the version of every input recorded beside it.
Too fine
Line-level joins multiply volume and invite double counting: a partial delivery becomes two sales.
Too coarse
Totals by region or quarter cannot be traced to a customer, segmented or corrected.

05Identity & authority

Who may create, change, read or derive each concept.

Highlight
Data authority by business concept: which system may create, update, read or derive each concept
Business conceptERPPlanningCRMData platform
Order lineThe ERP creates and changes orders. Every version is kept in the data platform.CreateUpdateNo authorityNo authorityRead
Invoice line & original amountPosted invoices are immutable; a correction is a new credit-note line. The original amount and currency are the fact.CreateNo authorityNo authorityRead
Target version & phasingPlanning publishes versions with their phasing. A republished target is a new version, not an edit.No authorityCreateUpdateReadRead
Account ↔ ERP customer linkGoverned identity from the CRM and master data; the data platform never infers it from names.ReadNo authorityCreateUpdateRead
Reporting amountDerived once, with the rate, rate type, rate date and rate version stored beside it.No authorityNo authorityReadDerive
Performance to targetDerived once, from one definition. The CRM shows it; it never computes it.No authorityNo authorityReadDerive
Period statusProvisional until the finance close; final afterwards.No authorityNo authorityReadDerive

No system owns a whole record here. Authority sits with each concept—and sometimes changes hands when the lifecycle does.

06Time, version & currency

A number without a time semantic is incomplete.

Time, targets and currency are not report settings. They are part of the model—and every one of them can change the answer.

Six clocks, kept apart

Time semantics in the commercial performance model: what each time means and the rule that governs it
TimeWhat it meansRule
Transaction dateWhen the order was placedDrives open orders and order intake
Posting dateWhen the invoice was postedDrives realised revenue and the rate date for invoices
Target periodThe month a phased target belongs toPerformance is measured against the version in force that month
Reporting periodThe period a report answers forCalendar month; year to date is derived, never stored
As-of dateWhen the figure was computedShown beside every published value
Publication versionWhich run and definitions produced itPast decisions stay reproducible

A target is data plus governance

The target model is part of this model, not a separate one: one grain, one owner, a declared phasing and a lifecycle in which every change is a version.

Target lifecycle: state, who moves it there and the rule that applies
StateWhoRule
ProposedSales leadershipTop-down and account sums compared; gaps explained
SignedSales leadership with financeThe commercial commitment—kept apart from the finance budget
PublishedCommercial planningVersioned, phased and effective-dated; read everywhere, edited nowhere
FrozenAutomatic, per periodClosed months cannot change
RevisedSales leadership, approved by financeA new version from an effective date; past months keep theirs

The original amount is a fact; the reporting amount is a view

Currency is one semantic dimension of the model. Each converted amount carries what is needed to reproduce it.

Currency policy: the question and the decision taken in this model
QuestionDecision
Transaction or reporting currency?Both: the original amount and currency are the fact; the reporting amount is derived
Which rate date?Invoices at posting date, orders at order date
Which rate type?Monthly average for actuals; the planning rate when comparing with targets
Where is it converted?Once, in the data platform—never in the CRM or a report
What if a rate is corrected?A new rate version recomputes forward; published reports keep the version they used

07Candidate architectures

Credible options, judged against these premises.

Rejected

Copy transactions into the CRM

Few transactions, one company, one currency

Cost: CRM volume, no point-in-time history, every team recomputes
Rejected

Report straight from each source

One source per question

Cost: Different grains, times and rates give different answers to the same question
Selected

A governed semantic model in the data platform, metrics computed once

Several sources, companies and currencies; shared definitions

Cost: Needs fact contracts, a target lifecycle, a rate policy and a freshness promise

Design decision

Conform operational facts in the analytical platform at a declared business grain, preserve original facts and versions, govern time and currency semantics centrally, and compute commercial measures from that model once—rather than rebuilding them independently in CRM reports or dashboards.

08Resulting architecture

Where the data comes from, where it goes—and how.

Every hand-over is named with its mode. The mode follows the business tolerance for waiting, inconsistency and loss.

Systems and partiesERPPlanningCRMData platform

  • 01 · ERPERP orders

    Order lines per company, every version kept.

    Feeds by Batch
  • 02 · ERPERP invoices

    Posted invoice lines, including credit notes, in original currency.

    Feeds by Batch
  • 03 · PlanningTargets

    Published target versions with their phasing.

    Feeds by Batch
  • 04 · Rate serviceExchange rates

    Rates by type and date, versioned.

    Feeds by Batch
  • 05 · CRMCRM accounts

    Commercial accounts and their governed identity links.

    Feeds by Event
  1. 06 · Data platformConformed model

    Facts at the declared grain, with time, version and currency semantics.

    Hands over by Batch
  2. 07 · Data platformMetric computation

    Performance to target, coverage and period status—computed once.

    Hands over by Batch
  3. 08 · Analytics · CRMConsumers

    Decision views and read-only CRM signals, each stamped with an as-of date.

09Failure & quality

Design the failure path before the happy path.

  • Invoices arrive a day after ordersCompleteness per company and dayMark the month provisional; recompute when they arriveData platform team
  • A credit note corrects a closed monthPosting date after the period closeBook it in the open period; the closed report keeps its versionFinance
  • An ERP customer maps to no accountUnmapped-customer check on every loadPark under “unmapped” and route to master data—never drop the revenueMaster data
  • A target is republished mid-yearNew target versionRecompute from its effective month; earlier months keep their versionCommercial planning
  • No rate exists yet for a monthMissing rate for pair and monthHold the amounts as provisional; never convert at a guessed rateTreasury

Data quality is a property of the contract

Data quality as a property of the contract: dimension, owner, how it is detected and what happens when it fails
DimensionOwnerDetected byWhen it fails
CompletenessData platform teamExpected loads per company and dayPeriod stays provisional
IdentityMaster dataUnmapped or merged customers on each loadParked, never dropped
ConsistencyFinanceInvoiced totals against the ERP ledgerPublication held
FreshnessData platform teamAs-of date against the promiseValues shown as stale
Semantic validitySales operationsDefinition and version checks per metricMetric not published

10Governance

Who keeps the meaning stable over time.

Governance of the performance model: what is governed, by whom, how often and on what evidence
WhatOwnerCadenceEvidence
Metric definitionsSales operationsVersioned on every changeMetrics published per definition version
Target publication & freezeCommercial planningMonthly freeze; revisions quarterlyVersions in force per month
Rate policyFinance & treasuryAnnual; corrections versionedRate versions used by published reports
Grain & identity contractsData platform ownerWith every new sourceUnmapped facts and contract changes

11The second layer

Questions that change the design.

Meaning & grain

  1. Does “actual” mean ordered or invoiced—and who decided?
  2. What is the grain of each fact, and where do they meet without double counting?
  3. Is a missing target a zero, or no target at all?

Time & version

  1. Which target version was in force for that month?
  2. When is a period final?
  3. Can last year’s report be reproduced exactly?

Currency & corrections

  1. Which currency is the fact, and which is a view?
  2. Which rate compares targets with actuals?
  3. How are credit notes and late invoices represented?

12Decisions & outputs

What the work produces.

  1. 01Fact contracts
  2. 02Declared grain
  3. 03Time & version semantics
  4. 04Target lifecycle & phasing rules
  5. 05Currency policy & amount model
  6. 06Metric contracts
  7. 07Data-quality rules