Evolving commercial analytics from single-currency to multi-currency
A fictional architecture evolution case: moving a single-currency analytical model to multi-currency without breaking history, targets or segments.
Independently created. Contains no employer or client implementation detail, internal names or figures.
01The outcome
Every amount carries its original value and currency, the rate and rate date used, and a reporting amount computed once in the data platform—reproducible for any past version.
The first release reported in one currency. Subsidiaries that sell in others now threaten every total, threshold and historical report.
Which amount is the fact, which is a view—and can last year’s report still be reproduced?
Store original amount, currency, rate, rate type and rate date with every converted amount, and convert once in the data platform—never in the CRM or a report.
Commercial analytics launched for companies that invoice in euros only. Amounts were stored as a single number. New subsidiaries invoice in other currencies, targets are planned centrally, and performance segments drive actions—so an exchange-rate move must not change a segment on its own.
02The reality · current state
What the data does today.
- Amounts are stored without a currency
- Converting at report time gives different totals in different reports
- Targets and actuals would be converted at different rates
- A corrected exchange rate would silently rewrite last year’s figures
- Thresholds for segments assume one currency
03Target flow
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.
- 01 · ERPERP amountsFeeds by Batch
Invoice and order amounts in transaction currency.
- 02 · Rate serviceExchange ratesFeeds by Batch
Monthly average and planning rates, versioned.
- 03 · Data platformConversionHands over by Batch
One conversion per amount, rate stored beside it.
- 04 · Data platformReporting amountsHands over by Batch
Reporting currency, reproducible by rate version.
- 05 · CRMCRM signals
Performance in reporting currency, with the rate policy named.
04Contracts
What one row or message means.
Meaning, grain, key, freshness and owner—written down before anything is mapped.
Converted amount
One amount in its original and reporting currency, with the rate that links them.
- Grain
- Source line × rate version
- Key
Source line key + rate version- Freshness
- Daily
- Owner
- Finance (policy) · data team (computation)
Exchange rate
A rate of a declared type for a date or month.
- Grain
- Currency pair × rate type × date
- Key
Pair + type + date + version- Freshness
- Monthly; corrections versioned
- Owner
- Treasury
05Key dimension · Evolution: V1 → V2 amount model
V1 → V2: from a number to an amount model
V1 stored a number. V2 stores a fact and a view of it, with everything needed to reproduce the view later.
- AmountA number, implicitly euros
- CurrencyNot stored: assumed
- Original amountThe fact, as invoiced
- Original currencyNewAs invoiced
- FX rateNewThe rate used
- Rate type & dateNewMonthly average, planning, or spot—and for which date
- Rate versionNewSo a correction never rewrites history
- Reporting amountNewDerived, in the reporting currency
- Reporting currencyNewDeclared, not assumed
- Transaction or reporting currency?
Both: the original is the fact; the reporting amount is a derived view.
- Which rate date?
Invoices at posting date, orders at order date.
- Daily, monthly or planning rate?
Monthly average for actuals; the corporate planning rate when comparing with targets.
- Where does conversion happen?
In the data platform, once—never in the CRM or a report.
- What happens when a rate is corrected?
A new rate version recomputes forward; published reports keep the version they used.
- Which currency drives thresholds and segments?
Reporting currency at the planning rate, so an exchange-rate move alone cannot change a segment.
V1 history migrates as original amounts in euros with an identity rate—no restatement, and the same reports reproduce exactly.
07Failure & recovery
Design the failure path before the happy path.
- No rate for a month yetMissing rate for pair and monthHold the amounts as provisional; never convert at a guessed rateTreasury
- A rate is corrected after closeNew rate versionRecompute from the new version; closed reports keep theirsFinance
- Target and actual in different currenciesCurrency check at comparisonCompare only in reporting currency at the planning rateSales operations
- Last year’s report is re-runReport parameter: rate versionReproduce with the stored rate versionData platform team
08Candidate architectures
Credible options, judged against these premises.
Convert at entry in the source or the CRM
One reporting currency forever
Cost: Original amounts lost; every policy change is a restatementStore only reporting amounts
Stable single rate policy
Cost: Cannot re-convert, audit or change policyStore original + rate + reporting; convert once, versioned
Several currencies, targets and thresholds
Cost: More columns and a rate-version discipline09The second layer
Questions that change the design.
Amounts
- Transaction currency or reporting currency?
- Should the original amount be preserved?
- Where does conversion happen?
Rates
- Which exchange-rate date?
- Daily, monthly or corporate planning rate?
- What happens when rates are corrected?
Comparisons
- How are targets converted?
- How are historical reports reproduced?
- Which currency is used for thresholds and segmentation?
10Decisions & outputs
What the work produces.
- 01V2 amount model
- 02Rate policy
- 03Conversion placement
- 04Rate versioning & reproducibility rule
- 05Target conversion rule
- 06V1 migration without restatement