Skip to content
Saiku Cloud

Advanced: virtual cubes, parent-child, calculated members

This page covers five advanced modelling patterns that go beyond the standard star-schema cube: combining multiple fact tables in a single cube, parent-child hierarchies, calculated members defined in the schema, reusable named sets, and bridge (many-to-many) dimensions.

Multi-fact cubes (the Mondrian 4 answer to “virtual cubes”)

Mondrian 3 shipped a <VirtualCube> element that stitched together two or more regular cubes. Mondrian 4 has no <VirtualCube> element. The feature was absorbed into the regular <Cube> model: a single cube can hold multiple <MeasureGroup> elements, each pointing at a different fact table. This achieves everything a virtual cube did, with less ceremony and better query planning.

You use a multi-measure-group cube when you have:

  • Fact tables at different granularities — say one at the day level and another at the month level.
  • Fact tables with different dimensionalities — say one covering Product, Time and Customer, another covering Product, Time and Warehouse.
  • An aggregate table that you want to register alongside the base fact (a common pattern — aggregate measure groups use type="aggregate").

Example: Sales and Warehouse in one cube

Warehouse and Sales:
  dimensions:
  - source: "Time"
  - source: "Product"
  - source: "Store"
  - source: "Customer"
  - source: "Warehouse"
  measure_groups:
  - name: "Sales"
    table: "sales_fact_1997"
    measures:
    - name: "Unit Sales"
      column: "unit_sales"
      aggregator: "sum"
      format_string: "Standard"
    - name: "Store Sales"
      column: "store_sales"
      aggregator: "sum"
      format_string: "#,###.00"
    - name: "Store Cost"
      column: "store_cost"
      aggregator: "sum"
      format_string: "#,###.00"
    dimension_links:
    - type: "foreign_key"
      dimension: "Time"
      foreign_key_column: "time_id"
    - type: "foreign_key"
      dimension: "Product"
      foreign_key_column: "product_id"
    - type: "foreign_key"
      dimension: "Store"
      foreign_key_column: "store_id"
    - type: "foreign_key"
      dimension: "Customer"
      foreign_key_column: "customer_id"
    - type: "no_link"
      dimension: "Warehouse"
  - name: "Warehouse"
    table: "inventory_fact_1997"
    measures:
    - name: "Units Ordered"
      column: "units_ordered"
      aggregator: "sum"
    - name: "Warehouse Sales"
      column: "warehouse_sales"
      aggregator: "sum"
    - name: "Warehouse Cost"
      column: "warehouse_cost"
      aggregator: "sum"
    dimension_links:
    - type: "foreign_key"
      dimension: "Time"
      foreign_key_column: "time_id"
    - type: "foreign_key"
      dimension: "Product"
      foreign_key_column: "product_id"
    - type: "foreign_key"
      dimension: "Store"
      foreign_key_column: "store_id"
    - type: "foreign_key"
      dimension: "Warehouse"
      foreign_key_column: "warehouse_id"
    - type: "no_link"
      dimension: "Customer"
  calculated_members:
  - name: "Profit Per Unit Shipped"
    dimension: "Measures"
    formula: "([Measures].[Store Sales] - [Measures].[Store Cost]) / [Measures].[Units\
      \ Ordered]"

How Mondrian handles non-conforming dimensions

Dimensions shared by both measure groups — Time and Product in the example above — are conforming dimensions. Mondrian automatically synchronises context across measure groups for these. If the current context is [Time].[1997].[Q2] and [Product].[Beer], measures from both groups resolve correctly.

Dimensions that belong to only one measure group are non-conforming. When the current context includes [Customer].[Jane Smith], a measure from the Warehouse group (which has <NoLink dimension="Customer"/>) returns NULL rather than an incorrect aggregate. This is the correct and expected behaviour.

Aggregate measure groups

When you register an aggregate table alongside its base fact table, the aggregate measure group uses type="aggregate" and <CopyLink> instead of <ForeignKeyLink> for rolled-up dimensions. See the Physical schema — CopyLink section for full details.

Parent-child hierarchies

A conventional hierarchy has a fixed number of levels and every member at a given level has its parent at the level above. A parent-child hierarchy has only one real level (plus the optional All member), but members can parent other members within the same level. This is the right model for organisation charts, product trees, general ledger accounts, geographic regions with variable depth, and similar recursive structures.

Defining a parent-child hierarchy in Mondrian 4

In Mondrian 4 you express parent-child structure on an <Attribute> inside a <Dimension>. Add the parentAttribute attribute pointing to the attribute that holds each member’s parent key. In the FoodMart Employees dimension the self-referencing column is supervisor_id:

<Dimension name="Employee" table="employee" key="Employee Id">
  <Attributes>
    <Attribute name="Employee Id"  keyColumn="employee_id"  nameColumn="full_name"/>
    <Attribute name="Manager Id"   keyColumn="supervisor_id"/>
    <!-- additional descriptive attributes -->
    <Attribute name="Position Title" keyColumn="position_title" hasHierarchy="false"/>
    <Attribute name="Gender"         keyColumn="gender"         hasHierarchy="false"/>
    <Attribute name="Education Level" keyColumn="education_level" hasHierarchy="false"/>
  </Attributes>
  <Hierarchies>
    <Hierarchy name="Employees" allMemberName="All Employees">
      <Level attribute="Employee Id" parentAttribute="Manager Id" nullParentValue="0"/>
    </Hierarchy>
  </Hierarchies>
</Dimension>

Key attributes on <Level>:

  • parentAttribute — the name of the attribute that supplies each member’s parent key. This single attribute is the signal to Mondrian that the hierarchy is parent-child.
  • nullParentValue — the value that indicates “no parent” (i.e. a root member). Defaults to null, but many schemas use 0 or -1 instead because some databases do not index null values.

Tuning parent-child hierarchies

The naive implementation of parent-child roll-up is expensive: Mondrian must issue one SQL statement per node to sum up all descendants. For shallow hierarchies with a few dozen members this is acceptable. For deeper or wider trees — hundreds or thousands of members — you will notice significant performance degradation. There is also a second constraint: you cannot define a distinct-count measure in any cube that contains an un-optimised parent-child hierarchy, because Mondrian cannot express the required de-duplication in standard SQL.

The solution is a closure table.

Closure tables

A closure table is a flat SQL table that pre-computes every ancestor-descendant pair at every depth. For the employee table it looks like this:

supervisor_idemployee_iddistance
110
121
132
141
153
162
220
231
252
261
330
351
440
550
660

Each row says “employee X is a descendant of supervisor Y at depth D”. Crucially every employee appears as their own descendant at distance 0 (the reflexive closure). With this table available, Mondrian can compute any subtree aggregate with a single SQL join — no iteration required.

You declare the closure table on the <Level> element using the <Closure> child:

<Hierarchy name="Employees" allMemberName="All Employees">
  <Level attribute="Employee Id" parentAttribute="Manager Id" nullParentValue="0">
    <Closure table="employee_closure"
             parentColumn="supervisor_id"
             childColumn="employee_id"/>
  </Level>
</Hierarchy>

The physical schema also needs a <Link> so Mondrian can join from employee to employee_closure:

<Table name="employee"         keyColumn="employee_id"/>
<Table name="employee_closure"/>
<Link source="employee_closure" target="employee" foreignKeyColumn="employee_id"/>

For best performance, add the following indexes:

CREATE UNIQUE INDEX employee_closure_pk
  ON employee_closure (supervisor_id, employee_id);


CREATE INDEX employee_closure_emp
  ON employee_closure (employee_id);

Declare both supervisor_id and employee_id as NOT NULL — some database optimisers handle non-null indexed columns significantly better.

Populating closure tables

Mondrian does not populate the closure table — that is the ETL layer’s job. The table must be refreshed whenever the hierarchy changes. If you use Pentaho Data Integration (Kettle), there is a built-in Closure Generator step that handles this automatically as part of your load pipeline.

If you are not using Kettle, you can populate the table with a stored procedure. Here is a MySQL example that seeds self-pairs then iterates outward one depth level at a time:

DELIMITER //


CREATE PROCEDURE populate_employee_closure()
BEGIN
  DECLARE distance INT;
  TRUNCATE TABLE employee_closure;
  SET distance = 0;


  -- seed with self-pairs (distance 0)
  INSERT INTO employee_closure (supervisor_id, employee_id, distance)
    SELECT employee_id, employee_id, distance
      FROM employee;


  -- for each (root, leaf) in the closure add (root, leaf->child)
  REPEAT
    SET distance = distance + 1;
    INSERT INTO employee_closure (supervisor_id, employee_id, distance)
      SELECT ec.supervisor_id, e.employee_id, distance
        FROM employee_closure ec
        JOIN employee e ON ec.employee_id = e.supervisor_id
       WHERE ec.distance = distance - 1;
  UNTIL (ROW_COUNT() = 0)
  END REPEAT;
END //


DELIMITER ;

Run this procedure after every load that modifies the employee table.

Calculated members

A calculated member is a cube member whose value comes from an MDX formula rather than from a column of the fact table. You can define calculated members on the Measures dimension (creating computed measures) or on any other dimension in the cube.

Rather than repeating the formula in every MDX query with a WITH MEMBER clause, you define it once in the schema and it is automatically available in all queries against that cube:

calculated_members:
- name: "Profit"
  dimension: "Measures"
  formula: "[Measures].[Store Sales] - [Measures].[Store Cost]"
  properties:
  - name: "FORMAT_STRING"
    value: "$#,##0.00"

You can also write the formula as an XML attribute if you prefer brevity:

calculated_members:
- name: "Profit"
  dimension: "Measures"
  formula: "[Measures].[Store Sales] - [Measures].[Store Cost]"
  properties:
  - name: "FORMAT_STRING"
    value: "$#,##0.00"

Both forms produce identical results — and because the converter folds the <Formula> child and the formula attribute into the same YAML formula: key, the two XML spellings yield the identical YAML shown above.

CalculatedMemberProperty

<CalculatedMemberProperty> sets MDX solve-order properties, format strings, and type information on the calculated member. The most commonly used properties are:

PropertyPurposeExample value
FORMAT_STRINGControls how the value is rendered in clients"$#,##0.00"
DATATYPETells XMLA clients the return type"Numeric", "Integer", "String"
SOLVE_ORDERPriority when multiple calculated members are in scope"2000"

FORMAT_STRING can hold a conditional expression instead of a literal:

calculated_members:
- name: "Conditional Profit"
  dimension: "Measures"
  formula: "[Measures].[Store Sales] - [Measures].[Store Cost]"
  properties:
  - name: "FORMAT_STRING"
    expression: "Iif(Value < 0, '|($#,##0.00)|style=red', '|$#,##0.00|style=green')"

When Mondrian renders a cell it first evaluates the expression to get a format string, then applies that format string to the cell value.

Visibility

Set visible="false" on a <CalculatedMember> (or on a <Measure>) to hide it from client tool member browsers. This is useful when you build a result through intermediate steps that should not be exposed directly:

<Measure name="Store Cost" column="store_cost" aggregator="sum"
         formatString="#,###.00" visible="false"/>


<CalculatedMember name="Margin" dimension="Measures" visible="false">
  <Formula>([Measures].[Store Sales] - [Measures].[Store Cost]) / [Measures].[Store Cost]</Formula>
</CalculatedMember>


<CalculatedMember name="Store Sqft" dimension="Measures" visible="false">
  <Formula>[Store].Properties("Sqft")</Formula>
</CalculatedMember>


<CalculatedMember name="Margin per Sqft" dimension="Measures" visible="true">
  <Formula>[Measures].[Margin] / [Measures].[Store Cost]</Formula>
  <CalculatedMemberProperty name="FORMAT_STRING" value="$#,##0.00"/>
</CalculatedMember>

Only “Margin per Sqft” appears in the client; the others are helpers.

Calculated members in multi-fact cubes

<CalculatedMembers> sits at the <Cube> level and can reference measures from any of the cube’s measure groups. This is the natural home for cross-fact-table KPIs:

calculated_members:
- name: "Profit Growth"
  dimension: "Measures"
  visible: true
  formula: "([Measures].[Profit] - [Measures].[Profit last Period]) / [Measures].[Profit\
    \ last Period]"
  properties:
  - name: "FORMAT_STRING"
    value: "0.0%"

Named sets

A named set is a reusable MDX set expression defined in the schema. It is the schema analogue of a WITH SET clause in an MDX query. Once defined, the set is implicitly available in every query against the cube (or schema) where it is declared.

Cube-level named sets

Declare a named set inside a <Cube> to make it available for all queries against that cube:

named_sets:
- name: "Top Sellers"
  formula: "TopCount([Warehouse].[Warehouse Name].MEMBERS, 5, [Measures].[Warehouse\
    \ Sales])"

You can then use [Top Sellers] directly in MDX:

SELECT
  {[Measures].[Warehouse Sales]} ON COLUMNS,
  {[Top Sellers]}                ON ROWS
FROM [Warehouse]
WHERE [Time].[Year].[1997]

Which might return:

WarehouseWarehouse Sales
Treehouse Distribution31,116.37
Jorge Garcia, Inc.30,743.77
Artesia Warehousing, Inc.29,207.96
Jorgensen Service Storage22,869.79
Destination, Inc.22,187.42

Schema-level named sets

You can also declare named sets at the schema level, outside any cube. Schema-level named sets are available across all cubes in the schema, but they are only valid in cubes that contain the dimensions the formula references:

<Schema name="FoodMart">
  <Cube name="Sales" .../>
  <Cube name="Warehouse" .../>


  <NamedSets>
    <NamedSet name="CA Cities"
              formula="{[Store].[USA].[CA].Children}"/>
    <NamedSet name="Top CA Cities">
      <Formula>TopCount([CA Cities], 2, [Measures].[Unit Sales])</Formula>
    </NamedSet>
  </NamedSets>
</Schema>

[CA Cities] is valid in any cube that has a [Store] dimension. Using it in a cube without that dimension raises an error at query time, not at schema load time.

The formula attribute and the <Formula> child element are equivalent. Use the attribute form for short expressions, the child element for readability on longer ones.

Bridge (many-to-many) dimensions

A normal dimension has a one-to-many relationship with the fact: each fact row belongs to exactly one customer, one product, one day. A many-to-many (or bridge) relationship is different — a single fact row can belong to several dimension members at once.

The classic example is a joint bank account. One account has one balance, but it can be co-owned by two or more customers:

ACCOUNTS (fact)            OWNERSHIP (bridge)
acct  year  balance        acct  customer  weight
 1    2024   1000           1     Alice      0.50
 2    2024    500           1     Bob        0.50
 3    2025    300           2     Bob        1.00
                            3     Alice      0.25
total balance = 1800        3     Carol      0.75

Account 1 is owned by both Alice and Bob. There is no single customer_id column you can put on the fact table, so a plain <ForeignKeyLink> cannot model this. Instead the relationship lives in a separate bridge table (OWNERSHIP) that maps accounts to customers, optionally with an ownership weight.

The fan-out problem

The naive way to answer “balance by customer” is to join fact → bridge → customer and SUM(balance). But that join fans out: account 1’s single $1000 row becomes two rows (one for Alice, one for Bob). Sum naively across all customers and you get 3100, not the true 1800 — account 1 counted twice, account 3 counted twice. This double-counting is the core hazard of many-to-many modelling.

Saiku’s Mondrian handles the fan-out correctly with a <BridgeLink>.

A <BridgeLink> replaces the <ForeignKeyLink> for the many-to-many dimension inside the measure group’s <DimensionLinks>:

measure_groups:
- name: "Balances"
  table: "account_fact"
  measures:
  - name: "Balance"
    column: "balance"
    aggregator: "sum"
  dimension_links:
  - type: "foreign_key"
    dimension: "Date"
    foreign_key_column: "date_key"
  - type: "bridge"
    dimension: "Customer"
    bridge_table: "account_owner"
    fact_foreign_key_column: "account_id"
    bridge_fact_key_column: "account_id"
    bridge_dimension_key_column: "customer_id"
AttributeRequiredDescription
dimensionyesThe many-to-many dimension this link resolves.
bridgeTableyesThe physical table holding the fact↔dimension mapping. Must be declared in <PhysicalSchema>.
factForeignKeyColumnyesColumn on the fact table that the bridge joins back to (the fact grain key).
bridgeFactKeyColumnyesColumn on the bridge that matches factForeignKeyColumn.
bridgeDimensionKeyColumnyesColumn on the bridge that matches the dimension’s key.
aggregationnofullCount (default) or weighted. See below.
weightColumnnoColumn on the bridge holding the allocation weight. Required when aggregation="weighted".

The bridge dimension also requires the fact grain to be declared, so Saiku can de-duplicate the fan-out. Declare it as the fact table’s <Key> in the physical schema:

tables:
- name: "account_fact"
  key:
  - "account_id"

Allocation: full-count vs weighted

There are two honest ways to attribute a shared fact value across its owners.

fullCount (the default) credits the whole value to every owner. Each customer sees the full balance of every account they are on:

Balance by Customer (fullCount):
  Alice  1300   (acct1 1000 + acct3 300)
  Bob    1500   (acct1 1000 + acct2 500)
  Carol   300   (acct3 300)

The per-customer numbers deliberately overlap — Alice and Bob both see account 1’s full $1000 — which is what you want for “how much balance does this customer have signing authority over?”. Whenever full-count totals are rolled up across owners — the (All Customers) grand total, or any intermediate level (see Multi-level bridge dimensions below) — Saiku applies a symmetric aggregate: it de-duplicates back to the fact grain before summing, so the grand total is the true 1800, not the fanned-out 3100.

weighted splits each value by the bridge’s weight column, so the parts sum to the whole:

dimension_links:
- type: "bridge"
  dimension: "Customer"
  bridge_table: "account_owner"
  fact_foreign_key_column: "account_id"
  bridge_fact_key_column: "account_id"
  bridge_dimension_key_column: "customer_id"
  aggregation: "weighted"
  weight_column: "weight"
 
Balance by Customer (weighted):
  Alice   575   (1000×0.50 + 300×0.25)
  Bob    1000   (1000×0.50 + 500×1.00)
  Carol   225   (300×0.75)
  total  1800   (reconciles exactly)

Use weighted for “what is this customer’s economic share?” and when weights sum to 1 per fact row, every level — including the grand total — reconciles to the fact total automatically.

Multi-level bridge dimensions

A bridge dimension is not limited to a single level — it can have a hierarchy, and full-count roll-ups stay correct at every level. Say each customer belongs to a segment (Alice and Bob are Premium, Carol is Standard) and you roll the bridge up to segment:

shared_dimensions:
  Customer:
    table: "dim_customer"
    key: "Customer"
    attributes:
    - name: "Segment"
      key_column: "segment"
    - name: "Customer"
      key_column: "customer_id"
      name_column: "customer_name"
    hierarchies:
    - name: "By Segment"
      all_member_name: "All Customers"
      levels:
      - "Segment"
      - "Customer"
 
Balance by Segment (fullCount):
  Premium   1800   (owns acct1, acct2, acct3 — de-duplicated)
  Standard   300   (owns acct3)

Account 1 is owned by Alice and Bob, both Premium — but it is counted once in the Premium total, not twice. This is the symmetric aggregate doing its job at an intermediate level: without it, Premium would read the fanned-out 2800. Account 3 appears in both segments (Alice is Premium, Carol is Standard) — that is the intended full-count overlap across segments, distinct from the double-count within a segment that the de-dup removes.

Weighted roll-ups need no de-duplication — each owner’s weighted share adds up cleanly, so Premium = 1575, Standard = 225, still reconciling to 1800.

What works

A bridge dimension behaves like any other dimension once declared. All of these work natively:

  • the bridge dimension on rows, columns, or in the slicer (WHERE);
  • crossed with normal foreign-key dimensions (e.g. Customer × Region), on different axes or as a crossjoin on the same axis;
  • multi-level bridge hierarchies — full-count totals de-duplicate correctly at every level, not just the leaf and the grand total;
  • multiple measures in one query, including calculated-column measures (a CASE, or arithmetic like revenue - cost), which de-duplicate just like plain measures;
  • NON EMPTY (customers with no accounts are suppressed);
  • explicit member sets and .Members.

Requirements and limitations

  • The fact table must declare its grain as a <Key> so fullCount can de-duplicate. (This is what makes multi-level roll-ups correct.)
  • weighted requires a weightColumn; fullCount ignores any weight.
  • Both allocations cover plain real-column measures and calculated-column measures (a CASE, or arithmetic such as revenue - cost): fullCount de-duplicates the calc expression on the fact grain, and weighted scales it by the weight (SUM(expression × weight)).
  • The bridge join is single-column on each hop. Composite join keys are a general limitation of the Calcite join path (not specific to bridges) — if your bridge keys are multi-column, model a single surrogate grain key instead.

A complete, loadable worked example — schema, seed data, and sample MDX with expected numbers — ships in the cube library under many-to-many.

Measure-level distinct grain (distinctKeyColumn)

A bridge solves fan-out caused by a join. But fan-out can also come from the grain of the fact table itself: a fact row that should be counted once is physically stored as several rows (an order with several line items, an event logged per touch), and a plain SUM over the column double-counts. When the duplication is keyed by a column on the measure’s own fact table, you don’t need a bridge — you can pin the de-dup grain directly on the measure with distinctKeyColumn.

ORDER_LINES (fact)
order_id  line  region  amount
   1        a    North   100
   1        b    North   100      ← amount repeats per line of order 1
   2        a    South    50
   3        a    North   300
   3        b    North   300      ← amount repeats per line of order 3


SUM(amount)                       = 100+100+50+300+300 = 850   (WRONG — double-counts)
SUM(amount) DISTINCT over order_id = 100      + 50 + 300       = 450   (correct)

Declare the de-dup key on the measure. It must resolve to a column on the measure’s own fact table, and is allowed only for aggregator="sum" and aggregator="avg":

measures:
- name: "Order Amount"
  column: "amount"
  aggregator: "sum"
  distinct_key_column: "order_id"

Semantics

The measure aggregates over SELECT DISTINCT (group keys, distinctKeyColumn, operand) — each distinct key contributes its value once, even when the fact grain repeats the row. It reuses the same symmetric-aggregate machinery as the bridge fan-out (the de-dup is driven by the measure declaration rather than by join topology):

  • aggregator="sum" → SUM over the distinct keys (the LookML sum_distinct shape).
  • aggregator="avg" → AVG over the distinct keys (LookML average_distinct).

Roll-ups stay correct at every level: By Region over the example reads North = 400, South = 50 — the distinct values, never the fanned-out 850.

Composes with row security

The distinct grain is applied after row-security filtering, not before. <PredicateGrant> and bridge member grants filter the fact rows inside the DISTINCT subquery, so the de-dup always operates on exactly the rows the caller is allowed to see — there is no path that de-dups a value the role cannot see and then leaks the total. The distinct grain is a fixed schema property (not role-dependent), so it adds no new cache dimension, and the segment cache continues to isolate roles: a value warmed for one role’s grants is never served to another. (This is covered by the row-security composition tests, including a no-cross-role-cache-bleed test.)

Requirements and limitations

  • distinctKeyColumn must resolve to a real column on the measure’s own fact table. A cross-table or unresolvable key is rejected at load time (fail-closed) — never a silently-wrong de-dup.
  • Allowed only for aggregator="sum" and aggregator="avg".
  • A segment load that mixes a distinct-grain measure with a plain measure, or two measures with different distinct keys, is split into homogeneous per-measure segments automatically (the request-wide DISTINCT cannot serve two grains at once).
  • When the distinct key equals the fact table’s own grain key (e.g. its primary key), the de-dup is a no-op and the measure behaves as a plain sum/avg. This is the natural target for LookML sum_distinct/average_distinct — see Migrating from Looker.

Adapted from the Mondrian project schema guide (EPL v1.0). Bridge (many-to-many) dimensions and measure-level distinct grain are Saiku extensions to Mondrian 4.