Skip to content
Saiku Cloud

Physical schema

The physical schema is where you tell Mondrian about the database objects that sit beneath your cubes: which tables exist, how their columns are typed, how tables relate to one another, and which joins are safe to traverse automatically. Everything in <PhysicalSchema> is pure structural description — no analytical semantics yet, just the plumbing.

Table

A table is a named use of a database table. You declare it with a <Table> element inside <PhysicalSchema>. The only required attribute is name; if the table lives in a database schema other than the default, add the schema attribute:

- name: "sales_fact_1997"
  schema: "Foodmart"

Primary keys

Mondrian needs to know the primary key of every dimension table so it can join correctly. You can declare it inline on the element itself using keyColumn (single column) or with a nested <Key> element (composite key):

# single-column shorthand
- name: "product"
  key_column: "product_id"


# composite key
- name: "time_by_day"
  key:
  - "the_year"
  - "quarter"

Fact tables do not need a <Key> declaration.

Columns and calculated columns

Inside a <Table> you can optionally define a <ColumnDefs> section. If you omit it, Mondrian reads column definitions from JDBC — which is adequate for most situations. When you need precise typing or computed columns, declare them explicitly:

<Table name="customer">
  <ColumnDefs>
    <ColumnDef name="customer_id" type="Integer" internalType="int"/>
    <ColumnDef name="fname"/>
    <ColumnDef name="lname"/>
    <CalculatedColumnDef name="full_name" type="String">
      <ExpressionView>
        <SQL dialect="mysql">
          CONCAT(<Column name="fname"/>, ' ', <Column name="lname"/>)
        </SQL>
        <SQL dialect="generic">
          <Column name="fullname"/>
        </SQL>
      </ExpressionView>
    </CalculatedColumnDef>
  </ColumnDefs>
</Table>

(This example is shown in XML only: the YAML schema format carries calculated columns but not bare <ColumnDef> typing metadata, and it encodes calculated-column SQL as a per-dialect string rather than the nested <Column> element form shown above. A calculated column whose body is plain SQL — see Calculated columns in measures — round-trips through YAML faithfully.)

<ColumnDef> declares that a physical column exists and how to interpret it:

  • type — the Mondrian data type (String, Integer, Numeric, Boolean, Date, Time, Timestamp). This controls sort order and the MDX type of expressions built from the column.
  • internalType — the Java type Mondrian uses to store values in memory (e.g. int causes ResultSet.getInt() calls and int storage). Use sparingly; the default is usually correct.

<CalculatedColumnDef> defines a virtual column as a SQL expression. You can supply per-dialect SQL bodies — Mondrian picks the right one at query time, which is invaluable when shipping a schema that must run on multiple database backends. Inside each SQL body, reference columns with <Column name="..."/> (or <Column table="..." name="..."/> for cross-table references); Mondrian qualifies and quotes them appropriately for the dialect.

Inline table

<InlineTable> lets you embed a small lookup dataset directly in the schema file — no database table required. You declare the column names and types, then list the rows. Mondrian materialises this as if it were a real table:

<Dimension name="Severity">
  <Hierarchy hasAll="true" primaryKey="severity_id">
    <InlineTable alias="severity">
      <ColumnDefs>
        <ColumnDef name="id" type="Numeric"/>
        <ColumnDef name="desc" type="String"/>
      </ColumnDefs>
      <Rows>
        <Row>
          <Value column="id">1</Value>
          <Value column="desc">High</Value>
        </Row>
        <Row>
          <Value column="id">2</Value>
          <Value column="desc">Medium</Value>
        </Row>
        <Row>
          <Value column="id">3</Value>
          <Value column="desc">Low</Value>
        </Row>
      </Rows>
    </InlineTable>
    <Level name="Severity" column="id" nameColumn="desc" uniqueMembers="true"/>
  </Hierarchy>
</Dimension>

This behaves identically to having a severity table in your database with three rows. To represent a NULL value for a cell, simply omit the <Value> element for that column.

(Shown in XML only — <InlineTable> is not part of the YAML schema format. Author inline lookup datasets in XML, or provide the rows as a real database table.)

Query

A <Query> element defines a virtual table by wrapping a SQL statement — like an inline view. You give it a name so other schema elements can reference it, and you can supply per-dialect SQL:

<Query name="american_customers">
  <ExpressionView>
    <SQL dialect="generic">
      SELECT * FROM customer WHERE country = 'USA'
    </SQL>
  </ExpressionView>
</Query>

The query can then be used wherever a <Table> is accepted. This is useful for filtering out rows, pre-computing columns, or unioning multiple tables before Mondrian sees them.

(Shown in XML only. In Mondrian 4 a <Query> is identified by an alias attribute — <Query alias="american_customers"> — rather than name. Written with alias, the query round-trips through the YAML schema format as a queries: entry carrying the per-dialect expression.)

A <Link> element in the physical schema declares a directed join relationship between two tables — the physical equivalent of a foreign key. Mondrian uses declared links to automatically resolve join paths when you build snowflake dimensions.

Here is how to define the link between an emp table and a dept table:

tables:
- name: "emp"
  key_column: "empno"
- name: "dept"
  key_column: "deptno"
links:
- source: "dept"
  target: "emp"
  foreign_key_column: "deptno"

The source table holds the foreign key; target is the table being joined to. You can also use <ForeignKey><Column .../></ForeignKey> children for composite foreign keys instead of the foreignKeyColumn shorthand.

Important: physical <Link> elements are used for snowflake paths inside dimension tables. Connecting a measure group’s fact table to its dimension tables requires an explicit dimension link element inside <DimensionLinks> — see Dimension links in measure groups below.

Every <MeasureGroup> declares how it relates to each dimension in the cube through a <DimensionLinks> block. Mondrian 4 supports five standard link types, plus a Saiku-specific <BridgeLink> for many-to-many relationships — six in total, each with a specific purpose:

The standard join: the fact table has a foreign key column pointing to the dimension’s key attribute. This covers the vast majority of star-schema cube designs.

- type: "foreign_key"
  dimension: "Store"
  foreign_key_column: "store_id"

Attributes:

AttributeRequiredDescription
dimensionyesName of the dimension to link
foreignKeyColumnone of theseSingle FK column in the fact table
<ForeignKey><Column/></ForeignKey>or thisChild element for composite foreign keys
attributenoTarget attribute name when the FK does not point to the dimension key

Example with a compound FK and an explicit target attribute:

- type: "foreign_key"
  dimension: "Time"
  foreign_key:
  - "time_id"
  attribute: "Date"

Used only in aggregate measure groups (type="aggregate"). The aggregate table already contains pre-rolled dimension key columns — there is no join needed because the dimension data has been copied into the aggregate table. You declare which dimension columns correspond to which aggregate table columns:

<CopyLink dimension="Time" attribute="Month">
  <Column table="time_by_day" name="the_year"    aggColumn="time_year"/>
  <Column table="time_by_day" name="quarter"     aggColumn="quarter"/>
  <Column table="time_by_day" name="month_of_year" aggColumn="month_of_year"/>
</CopyLink>

Each <Column> child maps a dimension column (table + name) to its counterpart column in the aggregate table (aggColumn). The attribute attribute on <CopyLink> is a no-op in Mondrian and does not affect query behaviour.

(Shown in XML only. A copy link’s column mappings do round-trip through YAML as a column_refs: list, but the no-op attribute is dropped by the YAML converter — so to avoid showing a YAML twin that silently lost an attribute the source XML still carries, this block is left as XML.)

Declares explicitly that this measure group does not relate to the named dimension. Mondrian returns NULL for measures in this group when a query filters by the unlinked dimension. Using <NoLink> is recommended (instead of omitting the entry) when the schema attribute missingLink is set to warning — which is the default.

- type: "no_link"
  dimension: "Warehouse"
AttributeRequiredDescription
dimensionyesName of the dimension that has no link

Declares that the dimension table and the fact table are the same physical table — what is sometimes called a degenerate dimension. No join is generated; the dimension columns are read directly from the fact table rows.

- type: "fact"
  dimension: "Store Type"
AttributeRequiredDescription
dimensionyesName of the degenerate dimension

This is common for dimensions like “Has coffee bar” or “Payment method” that are stored as columns on the fact table rather than in a separate lookup.

Declares that the dimension is reached indirectly through another dimension’s attribute — a bridge or snowflake path that does not touch the fact table directly. The FK joins to the key of a specified attribute of a specified intermediate dimension, not to the fact table.

- type: "reference"
  dimension: "Store"
  via_dimension: "Employee"
  via_attribute: "Store Id"
AttributeRequiredDescription
dimensionyesThe dimension being linked indirectly
viaDimensionnoThe intermediate dimension whose attribute acts as the bridge
viaAttributenoThe attribute on viaDimension that holds the join key

This appears in the FoodMart HR cube, where the Store dimension is reached via the Employee dimension’s Store Id attribute rather than through a direct FK on the salary fact table.

A Saiku extension to Mondrian 4. Links a many-to-many dimension through a separate bridge table, so a single fact row can belong to several dimension members at once (a joint account owned by two customers, a ticket with several tags). Saiku resolves the fan-out join safely — fullCount de-duplicates the grand total via a symmetric aggregate, weighted splits each value by an allocation column.

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"
AttributeRequiredDescription
dimensionyesThe many-to-many dimension being linked
bridgeTableyesPhysical table mapping fact rows to dimension members
factForeignKeyColumnyesFact-grain key column on the fact table
bridgeFactKeyColumnyesBridge column matching factForeignKeyColumn
bridgeDimensionKeyColumnyesBridge column matching the dimension key
aggregationnofullCount (default) or weighted
weightColumnnoAllocation-weight column; required for weighted

The fact table must declare its grain as a <Key>, and bridge queries require the Calcite backend. See Bridge (many-to-many) dimensions for the full treatment, allocation semantics, and a worked example.

Table hints

Mondrian supports a small set of database-specific optimizer hints on <Table> elements. These are passed through to generated SQL queries:

DatabaseHint typePermitted valuesEffect
MySQLforce_indexName of an index on the tableForces the named index when selecting level values
<Table name="automotive_dim">
  <Hint type="force_index">my_index</Hint>
</Table>

(Shown in XML only — <Hint> is not part of the YAML schema format. Express optimizer hints in XML when you need them.)

Hints are optional and non-portable. Only use them when profiling shows a specific plan problem that the hint fixes.

Star and snowflake schemas

The simplest cube layout — one fact table joined to several dimension tables — is called a star schema. Each dimension table joins directly to the fact table, and you connect them with <ForeignKeyLink> entries in the measure group.

A snowflake schema extends this by allowing a dimension to span multiple tables. Instead of a single dimension table, you have a chain: the fact table joins to the first dimension table, which joins to a second, and so on. In Mondrian 4 you model this by declaring physical <Link> elements between the dimension tables in <PhysicalSchema>, and Mondrian resolves the join path automatically. Snowflake dimension tables are referenced from <Attribute> elements using their table attribute.

For example, a Product dimension spanning product and product_class tables requires:

physical_schema:
  tables:
  - name: "product"
    key_column: "product_id"
  - name: "product_class"
    key_column: "product_class_id"
  links:
  - source: "product"
    target: "product_class"
    foreign_key_column: "product_class_id"
shared_dimensions:
  Product:
    table: "product"
    key: "Product Id"
    attributes:
    - name: "Product Id"
      table: "product"
      key_column: "product_id"
      has_hierarchy: false
    - name: "Product Name"
      table: "product"
      key_column: "product_id"
    - name: "Product Category"
      table: "product_class"
      key_column: "product_class_id"

Mondrian walks the physical <Link> graph to build the correct SQL JOIN chain. You do not need a <Join> element inside the dimension definition — that was a Mondrian 3 pattern. See Dimensions for the full dimension model.

Shared dimensions

A shared dimension is declared at the schema level (outside any cube) and reused by multiple cubes. Because it has no fixed foreign key, the link is established per cube inside each measure group’s <DimensionLinks>.

In Mondrian 4, a shared dimension appears in a cube’s <Dimensions> block with a source attribute:

shared_dimensions:
  Store:
    table: "store"
    key: "Store Id"
    attributes:
    - name: "Store Id"
      key_column: "store_id"
      has_hierarchy: false
    - name: "Store Country"
      key_column: "store_country"
      has_hierarchy: false
    - name: "Store State"
      key_column: "store_state"
      has_hierarchy: false
    - name: "Store City"
      key_column: "store_city"
      has_hierarchy: false
    - name: "Store Name"
      key_column: "store_name"
cubes:
  Sales:
    dimensions:
    - source: "Store"
    measure_groups:
    - name: "Sales"
      table: "sales_fact_1997"
      dimension_links:
      - type: "foreign_key"
        dimension: "Store"
        foreign_key_column: "store_id"
  Warehouse:
    dimensions:
    - source: "Store"
    measure_groups:
    - name: "Warehouse"
      table: "inventory_fact_1997"
      dimension_links:
      - type: "foreign_key"
        dimension: "Store"
        foreign_key_column: "warehouse_store_id"

The source="Store" reference pulls in the shared dimension’s full attribute and hierarchy definitions. The FK column that connects it to the fact table is declared on the <ForeignKeyLink>, not on the dimension itself.

Mondrian 3 note: M3 used <DimensionUsage source="..." foreignKey="..."/> to reference shared dimensions. In Mondrian 4 this is replaced by <Dimension source="..."/> in the cube’s <Dimensions> block and a <ForeignKeyLink> in the measure group. See Dimensions for the full attribute-based dimension model.

Adapted from the Mondrian project schema guide (EPL v1.0).