Schema structure

A complete example

Here is a small but complete Mondrian-4 schema — one cube, one dimension, two measures. Saiku accepts schemas in either YAML (the default) or XML; pick a format below and every example on the site switches to match.

YAML

schema:
  name: "Sales Example"
  metamodel_version: "4.0"
physical_schema:
  tables:
  - name: "sales_fact"
    schema: "demo"
    key:
    - "sale_id"
  - name: "dim_customer"
    schema: "demo"
shared_dimensions:
  Customer:
    table: "dim_customer"
    key: "Customer"
    attributes:
    - name: "Customer"
      key:
      - "customer_id"
      name_column: "customer_name"
    - name: "Gender"
      key:
      - "gender"
      has_hierarchy: false
    hierarchies:
    - name: "Customers"
      levels:
      - "Gender"
      - "Customer"
cubes:
  Sales:
    dimensions:
    - source: "Customer"
    measure_groups:
    - name: "Sales"
      table: "sales_fact"
      measures:
      - name: "Unit Sales"
        column: "units"
        aggregator: "sum"
        format_string: "#,###"
      - name: "Revenue"
        column: "revenue"
        aggregator: "sum"
        format_string: "#,##0.00"
      dimension_links:
      - type: "foreign_key"
        dimension: "Customer"
        foreign_key_column: "customer_id"

XML

<Schema name="Sales Example" metamodelVersion="4.0">

  <PhysicalSchema>
    <Table schema="demo" name="sales_fact">
      <Key><Column name="sale_id"/></Key>
    </Table>
    <Table schema="demo" name="dim_customer"/>
  </PhysicalSchema>

  <Dimension name="Customer" table="dim_customer" key="Customer">
    <Attributes>
      <Attribute name="Customer">
        <Key><Column name="customer_id"/></Key>
        <Name><Column name="customer_name"/></Name>
      </Attribute>
      <Attribute name="Gender" hasHierarchy="false">
        <Key><Column name="gender"/></Key>
      </Attribute>
    </Attributes>
    <Hierarchies>
      <Hierarchy name="Customers">
        <Level attribute="Gender"/>
        <Level attribute="Customer"/>
      </Hierarchy>
    </Hierarchies>
  </Dimension>

  <Cube name="Sales">
    <Dimensions>
      <Dimension source="Customer"/>
    </Dimensions>
    <MeasureGroups>
      <MeasureGroup name="Sales" table="sales_fact">
        <Measures>
          <Measure name="Unit Sales" column="units" aggregator="sum" formatString="#,###"/>
          <Measure name="Revenue" column="revenue" aggregator="sum" formatString="#,##0.00"/>
        </Measures>
        <DimensionLinks>
          <ForeignKeyLink dimension="Customer" foreignKeyColumn="customer_id"/>
        </DimensionLinks>
      </MeasureGroup>
    </MeasureGroups>
  </Cube>
</Schema>

Tip

The YAML and XML above are equivalent — the engine converts YAML to XML at load time, so they produce identical cubes. (The YAML here is generated from the XML by the converter and round-trip-verified, so the two never drift.)

Structure of a schema

The overall structure of a Mondrian-4 schema XML document looks like this:

<Schema>
  <PhysicalSchema>
    <Table>
      <Key>
        <Column/>
      </Key>
    </Table>
    <Query>
      <SQL/>
    </Query>
    <InlineTable>
      <ColumnDefs>
        <ColumnDef/>
      </ColumnDefs>
      <Key/> <!-- same structure as Table/Key -->
      <Rows>
        <Row>
          <Value/>
        </Row>
      </Rows>
    </InlineTable>
    <Link/>
  </PhysicalSchema>

  <Dimension/> <!-- shared; same structure as Dimension within a Cube -->

  <Cube>
    <Dimensions>
      <Dimension>
        <Attributes>
          <Attribute>
            <Key>
              <Column/>
            </Key>
            <Name/>     <!-- same structure as Key -->
            <Caption/>  <!-- same structure as Key -->
            <OrderBy/>  <!-- same structure as Key -->
            <Closure/>
            <MemberFormatter>
              <Script/>
            </MemberFormatter>
            <Property>
              <PropertyFormatter>
                <Script/>
              </PropertyFormatter>
            </Property>
          </Attribute>
        </Attributes>
        <Hierarchies>
          <Hierarchy>
            <Levels>
              <Level/>
            </Levels>
          </Hierarchy>
        </Hierarchies>
      </Dimension>
    </Dimensions>

    <MeasureGroups>
      <MeasureGroup>
        <Measures>
          <Measure/>
          <MeasureRef/>
        </Measures>
        <DimensionLinks>
          <ForeignKeyLink/>
          <FactLink/>
          <ReferenceLink/>
          <CopyLink/>
          <NoLink/>
        </DimensionLinks>
      </MeasureGroup>
    </MeasureGroups>

    <CalculatedMembers>
      <CalculatedMember>
        <Formula/>
        <CalculatedMemberProperty/>
        <CellFormatter>
          <Script/>
        </CellFormatter>
      </CalculatedMember>
    </CalculatedMembers>

    <NamedSets>
      <NamedSet>
        <Formula/>
      </NamedSet>
    </NamedSets>
  </Cube>

  <Role>
    <SchemaGrant>
      <CubeGrant>
        <DimensionGrant/>
        <HierarchyGrant>
          <MemberGrant/>
        </HierarchyGrant>
      </CubeGrant>
    </SchemaGrant>
    <Union>
      <RoleUsage/>
    </Union>
  </Role>

  <UserDefinedFunction>
    <Script/>
  </UserDefinedFunction>

  <Parameter/>
</Schema>

Element order is not significant. For example, a <UserDefinedFunction> element may appear before one <Cube> and after another — Mondrian-4 parses the document positionally but treats element order as advisory. This is a significant change from Mondrian 3.x, where element order was strictly required.

The content of each element is described in the following pages of this section and in the YAML schema reference.

The Schema element

<Schema> is the root element of every Mondrian schema. A minimal example:

YAML

schema:
  name: "Rock Sales"
  metamodel_version: "4.0"

XML

<Schema name="Rock Sales" metamodelVersion="4.0">
</Schema>

Required and common attributes

Attribute	Required	Default	Description
name	yes	—	Display name for this schema
metamodelVersion	no	auto-detected	Schema format version. Use "4.0" for all new schemas
caption	no	—	Override the display name seen by client tools
description	no	—	Human-readable description
measuresCaption	no	—	Caption for the virtual [Measures] dimension
defaultRole	no	—	Role applied when no role is specified by the connection
quoteSql	no	true	Whether Mondrian quotes SQL identifiers
missingLink	no	warning	Behaviour when a dimension link is missing — warning, error, or ignore
locales	no	—	Comma-separated list of locale codes for localised captions

The metamodelVersion attribute tells Mondrian which version the schema was written for. If you omit it, Mondrian infers the version from the schema contents. For all new schemas set it to "4.0".

Tip

The Schema designer in the Saiku dashboard generates valid Mondrian-4 XML with metamodelVersion="4.0" automatically. See the schema designer guide for the no-code path.

Annotations

The major element types — schema, cube, shared dimension, dimension, attribute, hierarchy, level, measure group, measure, calculated member — all support annotations. An annotation lets you attach arbitrary key/value metadata to any schema element, which is particularly useful for tools that need to store information without modifying the official Mondrian schema definition.

Add an <Annotations> element as a child of the element you want to annotate, then include one or more <Annotation> elements inside it. Annotation name values must be unique within their parent element. If you are creating annotations for a specific tool, choose names carefully to avoid clashes with other tools.

YAML

schema:
  name: "Rock Sales"
  metamodel_version: "4.0"
annotations:
  Author: "Fred Flintstone"
  Date: "10,000 BC"
cubes:
  Sales:
    ...

XML

<Schema name="Rock Sales" metamodelVersion="4.0">
  <Annotations>
    <Annotation name="Author">Fred Flintstone</Annotation>
    <Annotation name="Date">10,000 BC</Annotation>
  </Annotations>
  <Cube name="Sales">
    ...
  </Cube>
</Schema>

Conventional annotation names

Some annotation names are used by convention across tools:

Annotation	Element(s)	Description
AnalyzerBusinessGroup	Level	Creates folders in the UI
AnalyzerBusinessGroupDescription	Level	Description for those folders
AnalyzerDateFormat	Level	Used for relative date filters
AnalyzerHideInUI	Measure, CalculatedMember	Hides the field from the UI
AnalyzerDisableDrillLinks	Cube	Disables drillthrough links on the cube

Locale-aware tools use dot-qualified names by convention — for example caption.de_DE for a German caption, description.fr_FR for a French description. The FoodMart demo schema shows this pattern on the Sales cube:

YAML

annotations:
  caption.de_DE: "Verkaufen"
  caption.fr_FR: "Ventes"
  description.fr_FR: "Cube des ventes"

XML

<Annotations>
  <Annotation name="caption.de_DE">Verkaufen</Annotation>
  <Annotation name="caption.fr_FR">Ventes</Annotation>
  <Annotation name="description.fr_FR">Cube des ventes</Annotation>
</Annotations>

The logical model

The most important components of a schema are cubes, measures, attributes, and dimensions:

• A cube is a data set describing one or more business processes over a particular time period.
• A fact is the data representing one occurrence of a process — for example, a line item describing the sale of a product to a customer, or a pay period for an employee.
• A measure is a quantity you want to aggregate within a cube — for example, unit sales of a product, or an employee’s salary.
• An attribute is a value, possessed by every fact, by which you can divide facts into subsets. You might slice product sales by colour, customer gender, and the store where the product was sold; colour, gender, and store are all attributes.
• A dimension is a grouping of related attributes. For example, name, gender, zip code, and eye colour are attributes of a Customer dimension; colour, weight, and manufacturer are attributes of a Product dimension.

Here is a complete, working example of a simple schema that ties these concepts together:

YAML

schema:
  name: "Sales"
  metamodel_version: "4.0"
physical_schema:
  tables:
  - name: "sales_fact_1997"
  - name: "customer"
  - name: "time_by_day"
cubes:
  Sales:
    dimensions:
    - name: "Customer"
      table: "customer"
      key: "Id"
      attributes:
      - name: "Gender"
      - name: "Id"
    - name: "Time"
      table: "time_by_day"
      key: "Day"
      attributes:
      - name: "Year"
      - name: "Quarter"
        key:
        - "the_year"
        - "quarter"
      - name: "Month"
        key:
        - "the_year"
        - "month_of_year"
      - name: "Day"
      hierarchies:
      - name: "Yearly"
        has_all: false
        levels:
        - "Year"
        - "Quarter"
        - "Month"
    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"
    calculated_members:
    - name: "Profit"
      dimension: "Measures"
      formula: "[Measures].[Store Sales] - [Measures].[Store Cost]"
      properties:
      - name: "FORMAT_STRING"
        value: "$#,##0.00"

XML

<Schema name="Sales" metamodelVersion="4.0">
  <PhysicalSchema>
    <Table name="sales_fact_1997"/>
    <Table name="customer"/>
    <Table name="time_by_day"/>
  </PhysicalSchema>

  <Cube name="Sales">
    <Dimensions>
      <Dimension name="Customer" table="customer" key="Id">
        <Attributes>
          <Attribute name="Gender" column="gender"/>
          <Attribute name="Id" column="customer_id"/>
        </Attributes>
      </Dimension>

      <Dimension name="Time" table="time_by_day" key="Day">
        <Attributes>
          <Attribute name="Year" column="the_year"/>
          <Attribute name="Quarter" column="quarter">
            <Key>
              <Column name="the_year"/>
              <Column name="quarter"/>
            </Key>
          </Attribute>
          <Attribute name="Month" column="month_of_year">
            <Key>
              <Column name="the_year"/>
              <Column name="month_of_year"/>
            </Key>
          </Attribute>
          <Attribute name="Day" column="time_id"/>
        </Attributes>
        <Hierarchies>
          <Hierarchy name="Yearly" hasAll="false">
            <Level attribute="Year"/>
            <Level attribute="Quarter"/>
            <Level attribute="Month"/>
          </Hierarchy>
        </Hierarchies>
      </Dimension>
    </Dimensions>

    <MeasureGroups>
      <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>
    </MeasureGroups>

    <CalculatedMembers>
      <CalculatedMember
        name="Profit"
        dimension="Measures"
        formula="[Measures].[Store Sales] - [Measures].[Store Cost]">
        <CalculatedMemberProperty name="FORMAT_STRING" value="$#,##0.00"/>
      </CalculatedMember>
    </CalculatedMembers>
  </Cube>
</Schema>

This schema contains a single cube called “Sales” with two dimensions (“Customer” and “Time”), three base measures, and one calculated member (“Profit”). You can write an MDX query against it immediately:

SELECT {[Measures].[Unit Sales], [Measures].[Store Sales]} ON COLUMNS,
       {Descendants([Time].[Yearly].[1997].[Q1])} ON ROWS
FROM [Sales]
WHERE [Customer].[Gender].[F]

Which produces results like:

[Time]	[Measures].[Unit Sales]	[Measures].[Store Sales]
[1997].[Q1]	32,910	$69,798.23
[1997].[Q1].[Jan]	10,932	$23,309.04
[1997].[Q1].[Feb]	10,266	$21,773.93
[1997].[Q1].[Mar]	11,712	$24,715.26

The sections that follow explain each part of this schema in detail.

---

Content adapted from the Mondrian project schema guide, available under the Eclipse Public License v1.0 (http://www.eclipse.org/legal/epl-v10.html).