Currency conversion (declarative FX via <CurrencyConversion>)

Mondrian-4 supports a <CurrencyConversion> schema element that produces a converted measure as SUM(base_measure × exchange_rate), automatically picking the correct rate as of the fact row’s own date via a rate table with non-overlapping validity intervals. You declare what you want; the engine compiles the interval band join and aggregation for you.

Why declarative currency conversion?

Without <CurrencyConversion>, converting a measure to a different currency requires a hand-written calculated member that must re-implement the effective-date lookup every time the rate table structure changes:

<CalculatedMember name="Revenue (USD)" dimension="Measures">
  <Formula>
    Sum(
      Existing [Calendar].[Month].Members,
      [Measures].[Revenue] * LookupCube("[fx_rate]", ...)
    )
  </Formula>
</CalculatedMember>

With <CurrencyConversion> the same metric is:

XML

<CurrencyConversion name="Revenue (USD)" measure="Revenue"
    rateTable="fx_rate" rateColumn="rate"
    rateType="ECB" rateTypeColumn="rate_type"
    factCurrencyColumn="currency_id" rateCurrencyColumn="currency_id"
    factDateColumn="month_key"
    rateValidFromColumn="valid_from" rateValidToColumn="valid_to"
    formatString="#,##0.00"/>

YAML

currency_conversions:
- name: "Revenue (USD)"
  measure: "Revenue"
  rate_table: "fx_rate"
  rate_column: "rate"
  rate_type: "ECB"
  rate_type_column: "rate_type"
  fact_currency_column: "currency_id"
  rate_currency_column: "currency_id"
  fact_date_column: "month_key"
  rate_valid_from_column: "valid_from"
  rate_valid_to_column: "valid_to"
  format_string: "#,##0.00"

The loader validates that the named base measure and all referenced columns exist, generates the band join at load time, and throws a clear error rather than producing a silently wrong result.

Schema placement

<CurrencyConversion> elements are wrapped in a <CurrencyConversions> block inside a <MeasureGroup>, as a sibling to <Measures> and <DimensionLinks>:

XML

<Cube name="Monthly Revenue">

  <MeasureGroups>
    <MeasureGroup name="Revenue" table="mm_monthly">

      <Measures>
        <Measure name="Revenue" column="revenue" aggregator="sum"/>
      </Measures>

      <DimensionLinks>
        <ForeignKeyLink dimension="Calendar" foreignKeyColumn="month_key"/>
      </DimensionLinks>

      <CurrencyConversions>
        <CurrencyConversion name="Revenue (USD)" measure="Revenue"
            rateTable="fx_rate" rateColumn="rate"
            rateType="ECB" rateTypeColumn="rate_type"
            factCurrencyColumn="currency_id" rateCurrencyColumn="currency_id"
            factDateColumn="month_key"
            rateValidFromColumn="valid_from" rateValidToColumn="valid_to"
            formatString="#,##0.00"/>
      </CurrencyConversions>

    </MeasureGroup>
  </MeasureGroups>

</Cube>

YAML

measure_groups:
- name: "Revenue"
  table: "mm_monthly"
  measures:
  - name: "Revenue"
    column: "revenue"
    aggregator: "sum"
  dimension_links:
  - type: "foreign_key"
    dimension: "Calendar"
    foreign_key_column: "month_key"
  currency_conversions:
  - name: "Revenue (USD)"
    measure: "Revenue"
    rate_table: "fx_rate"
    rate_column: "rate"
    rate_type: "ECB"
    rate_type_column: "rate_type"
    fact_currency_column: "currency_id"
    rate_currency_column: "currency_id"
    fact_date_column: "month_key"
    rate_valid_from_column: "valid_from"
    rate_valid_to_column: "valid_to"
    format_string: "#,##0.00"

Attribute reference

Attribute	XML key	YAML key	Required	Description
name	name	name	yes	The name of the generated converted measure. Appears in [Measures] like any other measure.
measure	measure	measure	yes	The name of an existing <Measure> in the same measure group (the base value to multiply).
rateTable	rateTable	rate_table	yes	Physical table name that holds exchange rates. Must exist in the physical schema.
rateColumn	rateColumn	rate_column	yes	Column in rateTable that holds the numeric exchange rate.
rateType	rateType	rate_type	yes	Literal value used to filter the rate table to one rate source (e.g. "ECB", "BLOOMBERG").
rateTypeColumn	rateTypeColumn	rate_type_column	yes	Column in rateTable that holds the rate-type discriminator.
factCurrencyColumn	factCurrencyColumn	fact_currency_column	yes	Column in the fact table that identifies the source currency of each row.
rateCurrencyColumn	rateCurrencyColumn	rate_currency_column	yes	Column in rateTable that identifies the currency the rate converts from.
factDateColumn	factDateColumn	fact_date_column	yes	Column in the fact table used to determine which rate interval applies.
rateValidFromColumn	rateValidFromColumn	rate_valid_from_column	yes	Column in rateTable for the interval start (inclusive).
rateValidToColumn	rateValidToColumn	rate_valid_to_column	yes	Column in rateTable for the interval end (exclusive).
formatString	formatString	format_string	no	MDX format string for the converted measure, e.g. "#,##0.00" or "$#,##0".

Effective-date interval data contract

The rate table holds one row per (currency, rate_type) combination per validity period. The engine performs a band join that selects exactly the rate whose interval contains the fact row’s date:

fact.factDateColumn >= rate.rateValidFromColumn
AND fact.factDateColumn <  rate.rateValidToColumn
AND fact.factCurrencyColumn = rate.rateCurrencyColumn
AND rate.rateTypeColumn = '<rateType literal>'

The join is inherently 1-to-1 — one fact row matches at most one rate row — provided the rate table’s intervals are non-overlapping. This means the converted measure’s aggregation is simply SUM(fact.measure × rate.rate), and any unconverted measure queried alongside it is completely unaffected.

Caution

Intervals must be non-overlapping. If two rate rows for the same (currency, rate_type) have overlapping validity ranges, a single fact row will match both, and the join will fan out — inflating the converted measure. The engine does not enforce non-overlap at load time; it is the data owner’s responsibility.

Note

Missing-interval behaviour: the band join is an inner join. A fact row whose date falls in a gap between two intervals — or before the earliest valid_from or after the last valid_to — produces no match and is silently excluded from the converted measure. The base (unconverted) measure is unaffected because it is aggregated from the fact table directly.

Recommended rate-table structure

CREATE TABLE fx_rate (
  currency_id      VARCHAR(3)  NOT NULL,  -- e.g. 'EUR'
  rate_type        VARCHAR(16) NOT NULL,  -- e.g. 'ECB'
  rate             DECIMAL(18,6) NOT NULL,
  valid_from       DATE NOT NULL,         -- inclusive
  valid_to         DATE NOT NULL,         -- exclusive
  PRIMARY KEY (currency_id, rate_type, valid_from)
);

Use valid_to = '9999-12-31' (or the next period’s valid_from) as a sentinel for the current open interval so that the < valid_to predicate always works cleanly.

Backend requirement and fail-closed validation

Caution

<CurrencyConversion> requires the Calcite query backend (mondrian.backend=calcite), which is the Saiku default. If the legacy SQL generator is active the conversion is refused at load time — the schema will not load. Do not disable Calcite if you use this feature.

Validation is fail-closed: the schema load is aborted with a clear error message for any of the following conditions.

Condition	Error
measure does not name an existing measure in the measure group	CurrencyConversion "X": measure "Y" not found in measure group
rateTable does not exist in the physical schema	CurrencyConversion "X": rateTable "Y" not found in physical schema
Any of the named columns does not exist in the referenced table	CurrencyConversion "X": column "Y" not found in table "Z"
Calcite backend is not active	CurrencyConversion "X": requires Calcite backend

There is no silent wrong result — every misconfiguration is caught before the first query runs.

Worked example: Bank demo monthly revenue

The Bank demo ships a Monthly Revenue cube whose mm_monthly fact rows are denominated in EUR. An fx_rate table holds two yearly ECB rates:

currency_id	rate_type	rate	valid_from	valid_to
EUR	ECB	1.10	2024-01-01	2025-01-01
EUR	ECB	1.20	2025-01-01	2026-01-01

The raw fact data:

Year	Revenue (EUR)
2024	600
2025	750
Total	1350

Schema declaration

XML

<MeasureGroup name="Revenue" table="mm_monthly">

  <Measures>
    <Measure name="Revenue" column="revenue" aggregator="sum"/>
  </Measures>

  <DimensionLinks>
    <ForeignKeyLink dimension="Calendar" foreignKeyColumn="month_key"/>
  </DimensionLinks>

  <CurrencyConversions>
    <CurrencyConversion name="Revenue (USD)" measure="Revenue"
        rateTable="fx_rate" rateColumn="rate"
        rateType="ECB" rateTypeColumn="rate_type"
        factCurrencyColumn="currency_id" rateCurrencyColumn="currency_id"
        factDateColumn="month_key"
        rateValidFromColumn="valid_from" rateValidToColumn="valid_to"
        formatString="#,##0.00"/>
  </CurrencyConversions>

</MeasureGroup>

YAML

- name: "Revenue"
  table: "mm_monthly"
  measures:
  - name: "Revenue"
    column: "revenue"
    aggregator: "sum"
  dimension_links:
  - type: "foreign_key"
    dimension: "Calendar"
    foreign_key_column: "month_key"
  currency_conversions:
  - name: "Revenue (USD)"
    measure: "Revenue"
    rate_table: "fx_rate"
    rate_column: "rate"
    rate_type: "ECB"
    rate_type_column: "rate_type"
    fact_currency_column: "currency_id"
    rate_currency_column: "currency_id"
    fact_date_column: "month_key"
    rate_valid_from_column: "valid_from"
    rate_valid_to_column: "valid_to"
    format_string: "#,##0.00"

Golden results

Row	Revenue (EUR)	Revenue (USD)	Calculation
2024	600	660	600 × 1.10 (ECB 2024 rate)
2025	750	900	750 × 1.20 (ECB 2025 rate)
Grand total	1350	1560	660 + 900

The 2024 and 2025 rows use different rates because the band join picks the rate whose [valid_from, valid_to) interval contains each fact row’s month_key. The base Revenue (EUR) is unaffected and remains 1350.

Tip

The changing rate across years (1.10 → 1.20) is the clearest proof that the as-of band join is working: if all rows used the same rate the converted grand total would be either 1350 × 1.10 = 1485 or 1350 × 1.20 = 1620. The correct answer of 1560 is only achievable when each year’s rows are multiplied by that year’s rate before summing.

Sample MDX query

SELECT
  { [Measures].[Revenue],
    [Measures].[Revenue (USD)] } ON COLUMNS,
  [Calendar].[Year].Members ON ROWS
FROM [Monthly Revenue]

Result:

Year	Revenue (EUR)	Revenue (USD)
2024	600	660.00
2025	750	900.00
All Time	1,350	1,560.00

Relationship to <CalculatedMembers>

<CurrencyConversion> compiles to a native SQL aggregation (SUM(fact.revenue × rate.rate)) rather than to an MDX <CalculatedMember>. This means:

• The conversion is evaluated in the database, not in the MDX engine — it is as fast as any ordinary aggregate measure.
• The converted measure appears in XMLA member enumerations alongside regular measures.
• It can be referenced by <CalculatedMember> formulas (e.g. to compute a margin in the target currency).

If you need a formula that <CurrencyConversion> cannot express — for example, converting only a slice of a measure, or blending rates from multiple sources — use a plain <CalculatedMember> together with a separate scripted lookup. The two approaches can coexist in the same cube.

See Advanced — Calculated members for the full <CalculatedMember> reference.