Reference transfer options

Reference("name") works much like a lookup in a classic spreadsheet: a formula in one cube reads a value from another cube. The difference is that a cube cell is not addressed by a row and a column but by one item per dimension. So when you register a reference, XCubes must know, for every dimension of the source cube, which item to read. Each source dimension gets its own transfer rule, and you can mix rules freely within one reference.

The examples below use two source cubes: Product Prices (Product × Price Measure × Month) and Salary Scale (Grade × Pay Measure).

Path lookups: skip the setup

For the common case — pin one or two items, let everything else auto-match — you don't need to register a reference at all. Write the lookup inline as a path:

UNITS * "Product Prices"!"PRICE"

Each pin (!"CODE") fixes one item on the source cube; any dimension shared with the consuming cube still aligns automatically, exactly as in the "shared dimension" rule below. XCubes creates and maintains the underlying reference for you — it shows up read-only under "Inline lookups (auto-managed)" in the References panel, so you can see what it resolved to without having to manage it.

Chain multiple pins to fix more than one dimension: "Product Prices"!"PRICE"!"BUDGET". If a pin's code exists in more than one dimension of the source cube, disambiguate with a DimName: qualifier: "Product Prices"!"Price Measure:PRICE". Either "..." or '...' quoting works for any segment.

Rename the source cube, a pinned item, or a qualified dimension, and every formula referencing it is rewritten automatically — path lookups don't go stale on a rename.

Reach for a named reference (the rest of this page) when you need anything beyond fixed pins and auto-match: reading a shifted period (linked, with an offset), pairing differently-coded items (mapped), a lookup key that lives in a cell (cell-driven), or a key that's a property of the item itself (schema attribute).

1. Shared dimension — automatic

If the source cube and the consuming cube use the same dimension, nothing needs to be configured: the reference reads the source at the same item the consuming cell is on.

Here, the Sales cube shares the Product and Month dimensions with Product Prices. The formula on its Revenue row,

UNITS * Reference("Price")

multiplies each Units cell by the price of the same product in the same month — Alpha sold 12 units in February at a price of 102, giving revenue 1,224. No mapping was declared for Product or Month; alignment is automatic.

2. Linked (by position), with optional offset

Linked matches items by position rather than by identity, and can add a positive or negative offset. Its main use is "same list, shifted": reading the previous or next period.

The Sales cube registers a second reference, PriorPrice, identical to Price except that the Month dimension is linked with offset −1. The row Unit price of prior month is simply Reference("PriorPrice"): in February it returns January's price. If the offset walks off the end of the list — January has no prior month — the reference returns nothing (the cell shows 0/blank).

Note. Linked mode is positional. If the source dimension contains subtotal rows (quarters, totals), an offset of −1 can land on a subtotal rather than the previous month. Keep offset-linked dimensions free of rollup items, or use the time-series functions (Prior, Lag) which understand data periods.

3. Fixed item

Fixed pins a source dimension to one specific item, for every cell of the consuming cube. This is the standard answer for a source dimension that does not exist in the consuming cube at all.

The Sales cube has no Price Measure dimension, so its Price reference fixes Price Measure to the item Unit price, and its Cost reference fixes the same dimension to Unit cost. Two references, same source cube, different fixed items — that is how one source feeds several lines. (A path lookup's pins are exactly this rule, applied inline.)

4. Mapped (item-by-item)

Mapped is for two dimensions that describe the same world at different granularity or with different codes: you pick a dimension of the consuming cube and pair its items with source items, one by one. Several consuming items may map to the same source item.

The SKU Catalog cube lists sellable SKUs, while prices are kept per Product. Its ProductPrice reference maps the source's Product dimension to the local SKU dimension:

Caveat. An item with no pair in the mapping table does not return blank — it falls back to the first item of the source dimension (Prototype X above shows Alpha's price). Map every item you intend to use, and give deliberately-unpriced items an explicit source item, so nothing rides on the fallback.

5. Cell-driven

Cell-driven turns the lookup key into data: the source item is chosen per cell, from the value stored in another cell of the same row. The driving cell holds the item UID of the source-dimension item to read (each dimension item's numeric UID is visible in the dimension editor).

In Staff Cost — Cell-driven, each employee's Grade cell stores the UID of a Grade item, and the Salary row is Reference("PayScale"), whose Grade dimension is cell-driven by that Grade column:

Change Bob's Grade cell to 102 and his salary recomputes to 7,500. This is the closest analogue to an Excel VLOOKUP whose key sits in a helper column. A cell whose value is 0, negative, or not a valid UID does not resolve to blank — it falls back to the first item of the source dimension, same as an unmapped item in mapped mode.

6. Schema attribute

A schema attribute moves the "which item?" answer out of cube data and into the model itself. In a schema you declare an attribute on a dimension — for example Grade, defined on Employee and pointing at the Grade dimension — and give each employee a value. Attributes may be time-varying, with effective-from/to dates per value.

Staff Cost — Schema computes its Salary row as Reference("PayScaleByGrade"), whose Grade dimension is resolved through the Grade attribute. Because the attribute is time-varying and Bob Chen is promoted from Senior to Principal effective April:

The consuming cube needs a time-scale dimension so each cell knows its period; the attribute's aggregation rule (latest / earliest / most common) decides what happens when a period spans an attribute change (relevant for quarters and year totals). Compared with cell-driven, schema attributes are maintained in one place, apply to every cube that uses them, and carry their own history. If the attribute has no value for the item's period, the reference does not return blank — like cell-driven, it falls back to the first item of the source dimension.

What happens when a rule can't resolve

References never error a cell — but they don't all fail the same way, and only two situations genuinely return blank. In several others the lookup silently reads the first item of the source dimension instead of showing blank; know these:

Choosing a transfer option

• Fixed pins on shared-otherwise cubes → path lookup: just write "Cube"!"ITEM" in the formula, no reference setup needed.
• Same dimension on both sides → do nothing, it aligns automatically.
• Source dimension absent from the consumer → fixed item (or a path-lookup pin).
• "Same list, previous/next entry" → linked with an offset.
• Two codings of the same concept (SKU→Product, local→group chart of accounts) → mapped.
• The key is data the user types per row → cell-driven.
• The key is a property of the item, possibly changing over time → schema attribute.