YAML schemas

XML schemas are precise but they are a lot of angle brackets. YAML schemas give you the same model in a format that is easier to read at a glance, easier to review in a pull request, and easy to annotate with # comments. The Saiku engine treats them identically — the converter builds the same typed object graph either way.

Why YAML?

Readable. Fewer characters for the same information — a typical dimension that takes 60 XML lines fits in 20 YAML lines.
Diffable. Structural changes (adding a level, renaming a measure) produce clean, readable diffs instead of attribute-soup diffs.
Commentable. You can annotate any section with # comments; XML comments are legal but rarely survive round-trips through graphical tools.
Round-trippable. XML → YAML → XML produces byte-equivalent query results. You can convert your existing schemas at any time with the CLI.

Top-level structure

A complete M4 YAML schema uses these top-level keys (only schema is required):

schema:                   # required — schema header
  name: "FoodMart"
  metamodel_version: "4.0"

annotations:              # optional — schema-level metadata
  caption.de_DE: "Verkaufen"

physical_schema:          # optional — tables, calculated columns, links
  tables: [...]
  links: [...]

shared_dimensions:        # optional — named dimensions reused across cubes
  Store: { ... }
  Time:  { ... }

cubes:                    # optional — one entry per cube
  Sales: { ... }

roles:                    # optional — access-control roles
  - name: "California manager"
    schema_grant: { ... }

`schema` (header)

Maps to <Schema name="..." metamodelVersion="...">.

Key	Required	Notes
`name`	yes	Schema display name
`metamodel_version`	no	Typically `"4.0"`

Short form (name only):

schema: FoodMart

Long form:

schema:
  name: "FoodMart"
  metamodel_version: "4.0"

`annotations`

A flat map of name: text pairs. Supported at schema, cube, dimension, hierarchy, level, attribute, measure, calculated member, and role level. Mondrian uses dot-qualified names as a convention for locale-specific metadata:

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

`physical_schema`

Declares the physical tables and the foreign-key relationships between them.

`tables`

A list of table definitions. Each entry maps to a <Table> element.

Key	Required	Notes
`name`	yes	Table name in the database
`alias`	no	Alternative name used elsewhere in the schema (e.g. for self-joins)
`schema`	no	Database schema qualifier (e.g. `dbo`)
`key_column`	no	Single-column primary key shorthand
`key`	no	Multi-column primary key — list of column name strings
`calculated_columns`	no	Derived columns defined as SQL expressions (see below)

key_column and key are mutually exclusive. Use key_column for a single column; use key for composite keys. Fact tables typically have no key declared.

physical_schema:
  tables:
    - name: "customer"
      key:
        - "customer_id"
    - name: "product"
      key_column: "product_id"
    - name: "salary"
      alias: "salary2"        # second alias for a self-join
    - name: "sales_fact_1997" # fact table — no key declared

`calculated_columns`

Virtual columns computed from SQL expressions. Maps to <ColumnDefs><CalculatedColumnDef>.

Key	Required	Notes
`name`	yes	Column name used elsewhere in the schema
`type`	no	Mondrian type: `String`, `Numeric`, `Integer`
`expression`	yes	Map of SQL dialect name → SQL body

Inline column references inside SQL bodies use {col:column_name} or {col:table.column_name} tokens, which the converter parses back to <Column> elements:

- name: "customer"
  key:
    - "customer_id"
  calculated_columns:
    - name: "full_name"
      type: "String"
      expression:
        oracle:  "{col:fname} || ' ' || {col:lname}"
        mysql:   "CONCAT({col:fname}, ' ', {col:lname})"
        mssql:   "{col:fname} + ' ' + {col:lname}"
        generic: "{col:fullname}"

Supported dialect keys include generic, mysql, oracle, postgres, mssql, access, derby, db2, luciddb.

`links`

Foreign-key relationships between tables. Each entry maps to a <Link source="..." target="..."> element.

Key	Required	Notes
`source`	yes	Child (many-side) table
`target`	yes	Parent (one-side) table
`foreign_key_column`	no*	Single FK column name (shorthand)
`foreign_key`	no*	List of FK column names (compound FK)

*One of foreign_key_column or foreign_key is required.

links:
  - source: "product_class"
    target: "product"
    foreign_key:
      - "product_class_id"
  - source: "store"
    target: "employee"
    foreign_key:
      - "store_id"

`shared_dimensions`

A map of dimension_name: dimension_body. The map key becomes the name attribute on the resulting <Dimension> element. Shared dimensions live outside any cube and can be referenced by multiple cubes.

Key	Required	Notes
`table`	no	Default table for attributes in this dimension
`key`	no	Name of the key attribute (must match an attribute `name`)
`type`	no	`"TIME"` for time dimensions; omit for standard
`attributes`	no	List of attribute definitions
`hierarchies`	no	List of hierarchy definitions
`annotations`	no	Map of annotation name → text

`attributes`

Key	Required	Notes
`name`	yes	Attribute display name
`table`	no	Override the dimension’s default table
`key_column`	no*	Single-column key
`key`	no*	Multi-column key — list of `"table.column"` or `"column"` strings
`name_column`	no	Column used for the member display name
`name_columns`	no	Multi-column name — list of column strings
`order_by_column`	no	Column used for member ordering
`caption_column`	no	Column used for the member caption
`level_type`	no	Time grain: `TimeYears`, `TimeQuarters`, `TimeMonths`, `TimeWeeks`, `TimeDays`
`datatype`	no	`Boolean`, `Numeric`, `Integer`, `String` (default `String` is omitted)
`has_hierarchy`	no	`false` suppresses the auto-generated single-attribute hierarchy (default `true`; omit when true)
`hierarchy_all_member_name`	no	All-member label for the auto-generated hierarchy
`hierarchy_all_member_caption`	no	All-member caption for the auto-generated hierarchy
`hierarchy_default_member`	no	Default member MDX unique name for the auto-generated hierarchy
`hierarchy_has_all`	no	`false` suppresses the All level in the auto-generated hierarchy
`properties`	no	List of sibling attribute names that are properties of this attribute
`annotations`	no	Map of annotation name → text

*key_column and key are mutually exclusive. For cross-table references inside key or name_columns, qualify with table.column:

attributes:
  - name: "Brand Name"
    table: "product"
    key:
      - "product_class.product_family"      # qualified cross-table ref
      - "product_class.product_department"
      - "brand_name"                         # unqualified — belongs to default table
    name_column: "brand_name"
    has_hierarchy: false

`hierarchies`

Key	Required	Notes
`name`	yes	Hierarchy name
`all_member_name`	no	Label for the All member
`default_member`	no	MDX unique name of the default member
`has_all`	no	`false` suppresses the All level
`levels`	yes	Ordered list of levels
`annotations`	no	Map of annotation name → text

Each entry in levels is either a bare string (when the level name equals the attribute name) or a map {name, attribute} (when they differ):

hierarchies:
  - name: "Stores"
    all_member_name: "All Stores"
    levels:
      - "Store Country"   # bare string
      - "Store State"
      - "Store City"
      - "Store Name"

  - name: "Education Level"
    levels:
      - name: "Education Level"   # map form — level name differs from attribute
        attribute: "Education"

Full example — the Store shared dimension from FoodMart:

shared_dimensions:
  Store:
    table: "store"
    key: "Store Id"
    attributes:
      - name: "Store Country"
        key_column: "store_country"
        has_hierarchy: false
      - name: "Store State"
        key_column: "store_state"
        has_hierarchy: false
      - name: "Store City"
        key:
          - "store_state"
          - "store_city"
        name_column: "store_city"
        has_hierarchy: false
      - name: "Store Id"
        key_column: "store_id"
        has_hierarchy: false
      - name: "Store Name"
        key_column: "store_name"
        has_hierarchy: false
        properties:
          - "Store Type"
          - "Store Manager"
      - name: "Store Type"
        key_column: "store_type"
        hierarchy_all_member_name: "All Store Types"
    hierarchies:
      - name: "Stores"
        all_member_name: "All Stores"
        levels:
          - "Store Country"
          - "Store State"
          - "Store City"
          - "Store Name"

`cubes`

A map of cube_name: cube_body. The map key becomes the cube’s name.

Key	Required	Notes
`default_measure`	no	Name of the default measure
`annotations`	no	Map of annotation name → text
`dimensions`	no	List of dimension usages and local dimension definitions
`measure_groups`	no	List of measure group definitions
`calculated_members`	no	List of calculated member definitions
`named_sets`	no	List of named set definitions

`dimensions` (cube-level)

Each entry is either a usage (a reference to a shared dimension) or a local definition (an inline dimension defined only for this cube).

Usage — a map with only source:

dimensions:
  - source: "Store"
  - source: "Time"
  - source: "Product"

Local definition — a map with name plus the full dimension body (same keys as shared_dimensions):

dimensions:
  - name: "Customer"
    table: "customer"
    key: "Name"
    attributes:
      - name: "Country"
        key_column: "country"
        has_hierarchy: false
      - name: "Name"
        key_column: "customer_id"
        name_column: "full_name"
        order_by_column: "full_name"
        has_hierarchy: false
    hierarchies:
      - name: "Customers"
        all_member_name: "All Customers"
        levels:
          - "Country"
          - "State Province"
          - "City"
          - "Name"

`measure_groups`

Key	Required	Notes
`name`	no	Measure group name; optional for the primary group
`table`	yes	Fact or aggregate table name
`type`	no	`"aggregate"` for aggregate measure groups; omit for `"fact"`
`approx_row_count`	no	Hint for the approximate fact table row count (string, e.g. `"86837"`)
`ignore_unrelated_dimensions`	no	`true` treats unrelated dimensions as `[All]` rather than returning null
`measures`	no	List of measure or measure-ref definitions
`dimension_links`	no	List of links from this measure group to its dimensions

`measures`

Each entry is either a measure definition (has name) or a measure reference (has ref, used in aggregate measure groups).

Measure definition:

Key	Required	Notes
`name`	yes	Measure display name
`column`	no	Source column
`aggregator`	yes	`sum`, `count`, `distinct-count`, `min`, `max`, `avg`
`format_string`	no	MDX format string (e.g. `"#,###.00"`, `"Standard"`, `"Currency"`)
`datatype`	no	`Numeric`, `Integer`, `String`
`properties`	no	List of `{name, value}` or `{name, expression}` maps
`annotations`	no	Map of annotation name → text

Measure reference (aggregate measure groups only):

Key	Required	Notes
`ref`	yes	Name of the referenced measure from the primary group
`agg_column`	no	Column in the aggregate table holding the pre-aggregated value

measures:
  - name: "Unit Sales"
    column: "unit_sales"
    aggregator: "sum"
    format_string: "Standard"

  - name: "Customer Count"
    column: "customer_id"
    aggregator: "distinct-count"
    format_string: "#,###"

  # Inside an aggregate measure group:
  - ref: "Unit Sales"
    agg_column: "unit_sales_sum"

  - ref: "Fact Count"
    agg_column: "fact_count"

`dimension_links`

Each link has a type field that determines the join kind.

foreign_key — the standard join from fact table to dimension via a FK column:

Key	Required	Notes
`type`	yes	`"foreign_key"`
`dimension`	yes	Dimension name
`foreign_key_column`	no*	Single FK column name
`foreign_key`	no*	List of FK column names (compound FK)
`attribute`	no	Attribute to join to when the FK does not point to the dimension key

copy — the aggregate table inherits dimension data from another measure group:

Key	Required	Notes
`type`	yes	`"copy"`
`dimension`	yes	Dimension name
`column_refs`	no	List of `{table, name, agg_column}` maps

no_link — this measure group does not link to this dimension:

Key	Required	Notes
`type`	yes	`"no_link"`
`dimension`	yes	Dimension name

fact — the dimension’s data comes directly from the fact table:

Key	Required	Notes
`type`	yes	`"fact"`
`dimension`	yes	Dimension name

reference — the dimension is reached indirectly via another dimension’s attribute:

Key	Required	Notes
`type`	yes	`"reference"`
`dimension`	yes	Dimension name
`via_dimension`	no	Intermediate dimension name
`via_attribute`	no	Attribute on the intermediate dimension used to join

Full measure group example:

measure_groups:
  - name: "Sales"
    table: "sales_fact_1997"
    measures:
      - name: "Unit Sales"
        column: "unit_sales"
        aggregator: "sum"
        format_string: "Standard"
      - name: "Store Cost"
        column: "store_cost"
        aggregator: "sum"
        format_string: "#,###.00"
      - name: "Customer Count"
        column: "customer_id"
        aggregator: "distinct-count"
        format_string: "#,###"
    dimension_links:
      - type: "foreign_key"
        dimension: "Store"
        foreign_key_column: "store_id"
      - type: "foreign_key"
        dimension: "Time"
        foreign_key_column: "time_id"
      - type: "foreign_key"
        dimension: "Product"
        foreign_key_column: "product_id"

  - table: "agg_c_special_sales_fact_1997"
    type: "aggregate"
    measures:
      - ref: "Fact Count"
        agg_column: "fact_count"
      - ref: "Unit Sales"
        agg_column: "unit_sales_sum"
    dimension_links:
      - type: "foreign_key"
        dimension: "Store"
        foreign_key_column: "store_id"
      - type: "copy"
        dimension: "Time"

`calculated_members`

Key	Required	Notes
`name`	yes	Member name
`dimension`	no	Target dimension (e.g. `"Measures"`)
`hierarchy`	no	Target hierarchy MDX unique name
`parent`	no	Parent member MDX unique name
`formula`	no	MDX formula
`format_string`	no	MDX format string
`caption`	no	Display caption shown in client tools
`description`	no	Human-readable description
`visible`	no	`false` hides the member from client tools (default `true`; omit when true)
`cell_formatter`	no	Custom cell formatter — `{class_name}` or `{script: {language, body}}`
`properties`	no	List of `{name, value}` or `{name, expression}` maps
`annotations`	no	Map of annotation name → text

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

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

`named_sets`

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

`roles`

A list of role definitions.

Key	Required	Notes
`name`	yes	Role name
`class_name`	no	Java class implementing custom role logic
`schema_grant`	no	Top-level grant

schema_grant has access ("all", "none", "all_dimensions", "custom") and an optional cubes list. Each cube grant can contain dimensions and hierarchies lists with their own grants. Hierarchy grants support top_level, bottom_level, rollup_policy ("full", "partial", "hidden"), and a members list.

roles:
  - name: "California manager"
    schema_grant:
      access: "none"
      cubes:
        - cube: "Sales"
          access: "all"
          dimensions:
            - dimension: "Gender"
              access: "none"
          hierarchies:
            - hierarchy: "[Store].[Stores]"
              access: "custom"
              top_level: "[Store].[Stores].[Store Country]"
              members:
                - member: "[Store].[Stores].[USA].[CA]"
                  access: "all"
                - member: "[Store].[Stores].[USA].[CA].[Los Angeles]"
                  access: "none"

XML vs YAML side-by-side

Here is the same Time dimension in both formats:

YAML
XML

shared_dimensions:
  Time:
    table: "time_by_day"
    key: "Time Id"
    type: "TIME"
    attributes:
      - name: "Year"
        key_column: "the_year"
        level_type: "TimeYears"
        has_hierarchy: false
      - name: "Quarter"
        key:
          - "the_year"
          - "quarter"
        name_column: "quarter"
        level_type: "TimeQuarters"
        has_hierarchy: false
      - name: "Month"
        key:
          - "the_year"
          - "month_of_year"
        name_column: "the_month"
        level_type: "TimeMonths"
        has_hierarchy: false
      - name: "Time Id"
        key_column: "time_id"
        has_hierarchy: false
    hierarchies:
      - name: "Time"
        has_all: false
        levels:
          - "Year"
          - "Quarter"
          - "Month"

<Dimension name='Time' table='time_by_day' key='Time Id' type='TIME'>
  <Attributes>
    <Attribute name='Year' keyColumn='the_year'
               levelType='TimeYears' hasHierarchy='false'/>
    <Attribute name='Quarter' levelType='TimeQuarters'
               nameColumn='quarter' hasHierarchy='false'>
      <Key>
        <Column name='the_year'/>
        <Column name='quarter'/>
      </Key>
    </Attribute>
    <Attribute name='Month' levelType='TimeMonths'
               nameColumn='the_month' hasHierarchy='false'>
      <Key>
        <Column name='the_year'/>
        <Column name='month_of_year'/>
      </Key>
    </Attribute>
    <Attribute name='Time Id' keyColumn='time_id'
               hasHierarchy='false'/>
  </Attributes>
  <Hierarchies>
    <Hierarchy name='Time' hasAll='false'>
      <Level attribute='Year'/>
      <Level attribute='Quarter'/>
      <Level attribute='Month'/>
    </Hierarchy>
  </Hierarchies>
</Dimension>

Column reference encoding

Two encoding conventions appear throughout the YAML format:

table.column qualified references — inside key and name_columns lists, a column belonging to a non-default table is written as "table_name.column_name". The converter splits on the first . to recover table and column:

key:
  - "product_class.product_family"
  - "product_class.product_department"
  - "brand_name"                       # unqualified — belongs to the default table

{col:...} tokens — inside SQL expression bodies for calculated columns, inline column references use {col:column_name} or {col:table.column_name}. The converter parses these back to <Column> elements:

expression:
  mysql:   "CONCAT({col:fname}, ' ', {col:lname})"
  generic: "{col:fullname}"

Known limitations

Identifiers containing a literal dot — the table.column encoding splits on the first . character. Table or column names that themselves contain a . (e.g. quoted identifiers) will not round-trip correctly. The same applies to {col:table.column} tokens.
Whitespace in calculated-column SQL — the YAML serializer may introduce extra leading whitespace on SQL bodies when a file has been machine-generated and re-read. SQL with significant leading whitespace may acquire extra indentation on a second round-trip.
$ref requires a file URL — $ref include resolution only works when the schema is loaded via a Catalog=file:///... connect-string property. Schemas loaded via CatalogContent have no base directory and $ref resolution is skipped.

CLI — convert between XML and YAML and lint schemas from the command line.
Schema designer — generate an XML schema from a natural-language description.
Schemas — manage and edit saved schemas in the Saiku dashboard.

YAML schemas

Why YAML?

Top-level structure

schema (header)

annotations

physical_schema

tables

calculated_columns

links

shared_dimensions

attributes

hierarchies

cubes

dimensions (cube-level)

measure_groups

measures

dimension_links

calculated_members

named_sets

roles