Cubes and measures

The Cube element

A cube (<Cube>) is a named collection of dimensions and measures.

Dimensions live inside a <Dimensions> holder element. By convention they are declared first, followed by measures organised into measure groups under a <MeasureGroups> holder element. A measure group is a collection of measures that share the same fact table. Simple cubes have exactly one measure group; more advanced cubes can have several — for example when you want to combine a transaction fact table with a pre-aggregated rollup table.

Cube attributes

Attribute	Required	Default	Description
name	yes	—	Display name used in MDX queries
defaultMeasure	no	—	Measure selected when none is specified in the query
caption	no	—	Override display name for client tools
description	no	—	Human-readable description
visible	no	true	Whether the cube appears to client tools
cache	no	true	Whether Mondrian caches aggregates for this cube
enabled	no	true	Disabling a cube hides it without removing it
enableScenarios	no	false	Enables write-back / what-if scenarios

How fact tables and dimensions connect

The [Sales] cube in the schema structure example has its measure group based on the table "sales_fact_1997". Every table referenced in the logical schema must also appear in the <PhysicalSchema> block.

The fact table contains the columns from which measures are calculated, plus foreign key columns that link to dimension tables. Mondrian needs to know about all of them:

• Each measure column appears inside a <Measure> definition.
• Each foreign key column appears inside a <ForeignKeyLink> element, connecting the measure group to the appropriate dimension.

YAML

measure_groups:
- name: "Sales"
  table: "sales_fact_1997"
  measures:
  - name: "Unit Sales"
    column: "unit_sales"
    aggregator: "sum"
    format_string: "#,###"
  - name: "Store Sales"
    column: "store_sales"
    aggregator: "sum"
    format_string: "#,###.##"
  - name: "Store Cost"
    column: "store_cost"
    aggregator: "sum"
    format_string: "#,###.00"
  dimension_links:
  - type: "foreign_key"
    dimension: "Customer"
    foreign_key_column: "customer_id"
  - type: "foreign_key"
    dimension: "Time"
    foreign_key_column: "time_id"

XML

<MeasureGroup name="Sales" table="sales_fact_1997">
  <Measures>
    <Measure name="Unit Sales" column="unit_sales" aggregator="sum" formatString="#,###"/>
    <Measure name="Store Sales" column="store_sales" aggregator="sum" formatString="#,###.##"/>
    <Measure name="Store Cost" column="store_cost" aggregator="sum" formatString="#,###.00"/>
  </Measures>
  <DimensionLinks>
    <ForeignKeyLink dimension="Customer" foreignKeyColumn="customer_id"/>
    <ForeignKeyLink dimension="Time" foreignKeyColumn="time_id"/>
  </DimensionLinks>
</MeasureGroup>

Measures

Measure attributes

Each <Measure> element has the following attributes:

Attribute	Required	Default	Description
name	yes	—	Display name used in MDX queries
column	yes*	—	Column in the fact table. Use a calculated column name if the value is derived
aggregator	yes	—	Aggregation function — see below
formatString	no	—	How the value is formatted for display
datatype	no	Numeric	How values are stored in Mondrian’s cache and returned via XML for Analysis
caption	no	—	Override display name for client tools
description	no	—	Human-readable description
visible	no	true	Whether the measure appears to client tools
formatter	no	—	Fully-qualified class name of a custom cell formatter
table	no	—	Override the measure group’s table for this measure (advanced)

* column is required unless you point to a calculated column defined in the physical schema.

Aggregator types

The aggregator attribute supports the following values:

Value	Meaning
sum	Sum of all values
count	Row count
min	Minimum value
max	Maximum value
avg	Arithmetic mean
distinct-count	Count of distinct values
median	Median (50th percentile) — non-additive
percentile	A percentile (set percentile="0..100", default 50) — non-additive

distinct-count has limitations when the cube contains a parent-child hierarchy.

Non-additive aggregators: median and percentile

median and percentile are non-additive leaf aggregators: there is no median-of-medians, so they cannot be combined from sub-aggregates. Saiku pushes them down to SQL as PERCENTILE_CONT(fraction) WITHIN GROUP (ORDER BY column) and computes them at the exact grain you queried — they are deliberately excluded from aggregate-table substitution and segment rollup-from-cache (so you never get a wrong “median of medians”).

YAML

measures:
- name: "Median Order Value"
  column: "order_total"
  aggregator: "median"
- name: "P90 Latency"
  column: "latency_ms"
  aggregator: "percentile"
  percentile: "90"

XML

<Measure name="Median Order Value" column="order_total" aggregator="median"/>
<Measure name="P90 Latency" column="latency_ms" aggregator="percentile" percentile="90"/>

They require the Calcite backend (the Saiku default) on a database that supports PERCENTILE_CONT — PostgreSQL, Oracle, SQL Server, Snowflake, BigQuery, H2, DuckDB, and similar. On a backend without it the query is refused with a clear error rather than returning a wrong number. The trade-off is intentional: you lose pre-aggregation/cache-rollup for these measures, in exchange for a correct value at the queried grain.

Data types

The datatype attribute controls how cell values are stored in Mondrian’s cache and returned over XML for Analysis. Accepted values are String, Integer, Numeric, Boolean, Date, Time, and Timestamp. The default is Numeric, except for count and distinct-count measures, which default to Integer.

Format strings

The optional formatString attribute controls how a value is printed. The , and . symbols are locale-sensitive — if you are running in Italian, #,###.00 might produce 48.123,45. A few common patterns:

Pattern	Example output
#,###	32,910
#,###.##	69,798.23
#,###.00	69,798.23
$#,##0.00	$69,798.23
Standard	locale default

For advanced date patterns and conditional formats see the MDX format strings reference.

Caption

A measure can have a caption attribute that is returned instead of its name by client APIs. This is useful when you want to localise a measure name or display special characters:

YAML

measures:
- name: "Sum X"
  column: "sum_x"
  aggregator: "sum"
  caption: "Σ X"

XML

<Measure name="Sum X" column="sum_x" aggregator="sum" caption="&#931; X"/>

Calculated columns in measures

Rather than pointing a measure at a raw column, you can derive the value from a SQL expression. Define a calculated column in the <PhysicalSchema> declaration of the fact table and then reference it by name in the <Measure>:

YAML

calculated_columns:
- name: "promotion_sales"
  expression:
    generic: "(case when {col:promotion_id} = 0 then 0 else {col:store_sales}\
      \ end)"

XML

<Table name="sales_fact_1997">
  <ColumnDefs>
    <CalculatedColumnDef name="promotion_sales">
      <ExpressionView>
        <SQL dialect="generic">(case when <Column name="promotion_id"/> = 0 then 0 else <Column name="store_sales"/> end)</SQL>
      </ExpressionView>
    </CalculatedColumnDef>
  </ColumnDefs>
</Table>

Then reference that column just like any other:

YAML

measures:
- name: "Promotion Sales"
  column: "promotion_sales"
  aggregator: "sum"
  format_string: "#,###.00"

XML

<Measure name="Promotion Sales" aggregator="sum"
         column="promotion_sales" formatString="#,###.00"/>

The <PhysicalSchema> gathers all implementation details in one place. The <Measure> definition does not need to know — or care — that promotion_sales is calculated. Every time Mondrian needs to access it, the engine substitutes the SQL expression instead. Arbitrary SQL expressions are supported, including subqueries, as long as the underlying database can evaluate them in an aggregate context.

Composite keys and dimension links

When a dimension’s key spans more than one column, you express that with a multi-column <Key> block on the <Attribute>:

YAML

attributes:
- name: "Quarter"
  key:
  - "the_year"
  - "quarter"

XML

<Attribute name="Quarter">
  <Key>
    <Column name="the_year"/>
    <Column name="quarter"/>
  </Key>
</Attribute>

If there is only one key column, <Key> and the keyColumn shorthand attribute are equivalent — use whichever is clearer. You do not need to specify nameColumn separately when it defaults to the last column in the composite key.

When a dimension table has a composite primary key, the <ForeignKeyLink> in the fact table’s <MeasureGroup> must supply one foreign-key column per column in the composite key. See Physical schema for the full link reference.

Note

Mondrian-4 does not have <DimensionUsage>, <Join>, or <VirtualCube> elements. Shared dimensions are declared at the schema level and referenced inside cubes with <Dimension source="..."/>. Multi-fact cubes are modelled with multiple <MeasureGroup> elements inside one <Cube>.