Mapping source data to a reporting template
The commonest way to produce a return that validates cleanly and still says the wrong thing is to map to the template instead of to the model. A reported figure is not identified by its position in a grid. It is a metric plus a set of dimensions — counterparty sector, country, currency, maturity band, accounting portfolio — and the template is a human-readable presentation of those combinations, not their definition. Mapping to the visual layout is how two different data points end up sharing one source query.
1. Work in this order
| Step | Question | Output |
|---|---|---|
| 1 | Which data points are actually in scope for us? | An inventory — not the whole framework |
| 2 | What are the dimensions of each? | The qualifier list per data point |
| 3 | Can the source produce each dimension? | A gap list |
| 4 | What is the metric, and from where? | Source field plus transformation |
| 5 | Which rules touch this data point? | The validation the mapping must satisfy |
2. Dimensions are where the work is
Source systems store balances and transaction amounts happily. What they frequently cannot produce is the qualifying detail a supervisory framework needs: counterparty sector on the required classification, counterparty residence at the required level, the instrument category, the accounting portfolio.
Three responses exist for a missing dimension, and they are not equivalent:
- Capture it at source. Slowest, correct, and the only one that scales. It is a data-collection change, usually with a customer-facing or onboarding component.
- Derive it, and document the derivation. Legitimate where the derivation is deterministic and recorded — for example deriving a category from an attribute the source does hold.
- Infer it. The one to avoid. Inferring residence from an IBAN prefix, or sector from a name, produces figures nobody can defend when queried.
The distinction between deriving and inferring is whether the rule is deterministic and written down. If a second person would produce the same answer from the same inputs, it is a derivation. If not, it is a guess with a spreadsheet around it.
3. The same data point, twice
Because data points are defined by metric plus dimensions rather than by position, the same data point can appear in more than one template — and where it does, the validation rules will compare them. Two independent source queries producing the same conceptual figure is therefore not redundancy; it is a guaranteed future cross-template validation failure.
The mapping should record, explicitly, where one data point serves several templates, so that a change to its derivation propagates everywhere it is used. That relationship is invisible in the templates and visible only in the model.
4. A worked mapping
Facts: a return requires exposures broken down by counterparty sector and residence, and the firm’s ledger holds counterparty name, account identifier and amount.
What the mapping needs: the amount is available. Sector is not held at all. Residence is not held, and cannot be taken from the account identifier — an account held at an institution in one country tells you nothing reliable about where its holder is resident.
What the practitioner does: records two gaps rather than one workaround. Sector becomes a data-collection change at onboarding, with a remediation pass for the existing book. Residence becomes a maintained customer attribute, sourced from the identification data already captured for due-diligence purposes — which is usually the cheapest fix available, because the information exists in the compliance record and simply never reached the ledger.
Both are logged in the mapping with an owner and a target date, and until they close, the derivation actually used is documented alongside. That log is what turns “we could not produce this” into a managed gap rather than a finding.
5. Keeping it alive
A mapping is valid for a framework version. When a release lands, the question is not “does the file still build” but “which mapped data points changed definition” — and that can only be answered against a mapping that records the version it was written for.
Three habits keep it current: version the mapping alongside the framework; review the gap list on a fixed cycle rather than when someone remembers; and require any new product launch to answer the reporting question before go-live, since a product that creates a new instrument type or counterparty class changes the population before it changes any number.
FAQ
Why not map straight from the templates?
Because the template shows presentation, not definition. Visually similar cells in different templates can differ in a dimension, and mapping them to one query is a silent error.
Is deriving a missing dimension acceptable?
Where the derivation is deterministic and documented, yes. Inferring it from something unreliable — residence from an IBAN prefix, sector from a name — is not defensible when queried.
What triggers a mapping review?
A framework release, a product launch, and a fixed periodic review of the open gap list. The first two change definitions and populations; the third stops gaps becoming permanent.
Related: The data point model · Building a reporting pipeline · Validation rules
