Mapping codes — Allocation tables

A Link matches a source column to a dimension by item code. That works when both sides agree on the coding — and they usually don't. Your accounting system exports account 620100; your P&L has a line called OPEX. Nothing in the Link can bridge that gap, so every row lands nowhere.

An Allocation table is the bridge: a saved list of rules, each one saying this source code becomes that destination item. You build it once in the Data section and point as many Links at it as you like.

Two source codes can map to the same destination — that is how a detailed chart of accounts collapses into a reporting line. Rows landing on the same cell sum, exactly as they would without a mapping.

The sign

Each rule also decides what happens to the value:

• = passes it through unchanged;
• − inverts it. This is the workhorse for accounting sources, which commonly deliver revenue and liabilities as credits — negative numbers you want positive in a report;
• abs takes the magnitude, for sources whose polarity is inconsistent.

How a code is matched

• Exact codes win. 620100 is looked up first, before any pattern.
• A trailing * is a prefix pattern. 70* matches 700000, 701200, and so on. The * only works at the end, and only on the source side — a pattern in the destination column matches nothing.
• Among patterns, the first listed wins. If 6* sits above 62*, the broader rule catches everything and 62* never fires. List your specific patterns before your catch-alls.
• Source codes ignore case; destination codes do not. 620100 matches whatever the source sends. But the destination must match your dimension's item code exactly — opex will not find OPEX. This one catches people, and it fails quietly.

When a code isn't matched

Nothing goes wrong loudly. A source value with no matching rule is skipped — its row simply doesn't arrive, and the cube shows a smaller number than the source. A destination code that doesn't exist behaves the same way.

So the habit that matters: Preview the Link before you run it. The preview reports how many source rows would be skipped, and a surprising skip count is how a missing or mistyped rule announces itself. Once the values are in the cube, the only clue is a total that looks slightly wrong.

Using one

Allocation tables live in the Data section, alongside your files and tables. Create one, set its source and destination dimensions (optional, but worth doing — XCubes then warns you about destination codes that don't exist), and fill in the rules. Auto-map can propose matches from the item codes and descriptions on both sides, which you then review.

In the Link editor, each source column can either match codes directly or go through an allocation table — you pick the table per column. One crosswalk can serve every Link that reads the same source system.

See Populating cubes — Links & integrations for the transfer itself, and Dimensions for where the destination item codes come from.