Dimensions, attributes and hierarchies

A dimension is a grouping of related attributes — the axes you slice by in a query. The [Customer] dimension might contain [Gender], [City], and [Country]; the [Time] dimension contains [Year], [Quarter], [Month], and [Day]. This page covers every aspect of authoring dimensions in Mondrian 4, from the simplest one-table dimension to snowflake joins, time functions, member properties, and SQL optimization hints.

Dimensions and attributes

A dimension is declared with a <Dimension> element. It always has:

• a name — the MDX name used in queries
• a table — the database table (or alias) the dimension is drawn from
• a key — the name of the attribute that uniquely identifies each row

YAML

Customer:
  table: "customer"
  key: "Id"
  attributes:
  - name: "Gender"
  - name: "Id"

XML

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

Each <Attribute> inside <Attributes> becomes independently queryable in MDX. Mondrian automatically generates a single-level attribute hierarchy for every attribute (see Attribute hierarchies below), so you can start using attributes in queries without explicitly defining any <Hierarchy> elements.

Dimension key

Every dimension must have a key attribute — the attribute whose values uniquely identify each row in the dimension table. You declare it with the key attribute on <Dimension>, which must match the name of one of the attributes in <Attributes>.

For example, in the [Customer] dimension above, key="Id" points to the <Attribute name="Id" column="customer_id"/>. The key attribute is used when linking the dimension to a fact table via <ForeignKeyLink>.

Attribute key and name

An attribute’s key is the column (or columns) that uniquely identify a member. By default, the attribute’s name — the string displayed to users — is also the key. You can override this:

YAML

attributes:
- name: "Month"
  key:
  - "the_year"
  - "month"
  name_column: "month_name"
  order_by_column: "month"

XML

<Attribute name="Month" nameColumn="month_name" orderByColumn="month">
  <Key>
    <Column name="the_year"/>
    <Column name="month"/>
  </Key>
</Attribute>

Key concepts:

• column (or a nested <Key>/<Column> block) — the column(s) that identify the member. A composite key ensures members in two different years that happen to share the same name (e.g. Q1) are treated as separate members.
• nameColumn — the column to display. If omitted, the key (last column in a composite key) is used.
• orderByColumn — the column controlling sort order. If omitted, members are sorted by name.
• captionColumn — the column for the caption, if different from the name.

Attribute order

By default, attributes are sorted by their name. This is not always what you want. Consider the [Month] attribute — if month names are stored as strings, alphabetical order gives April, August, December… rather than January, February, March…

Fix this by setting orderByColumn to the numeric month column:

YAML

attributes:
- name: "Month"
  key:
  - "the_year"
  - "month"
  name_column: "month_name"
  order_by_column: "month"

XML

<Attribute name="Month" nameColumn="month_name" orderByColumn="month">
  <Key>
    <Column name="the_year"/>
    <Column name="month"/>
  </Key>
</Attribute>

With orderByColumn="month" pointing at the numeric 1..12 column, Mondrian sorts by the number but displays the human-readable name.

Hierarchies and levels

Some attributes are naturally used together. A business user viewing a state often wants to expand it to see cities. Viewing a month, they might want to roll up into quarter or year. For such combinations, you define a hierarchy.

A hierarchy is an ordered list of attributes — coarser at the top, finer at the bottom. Each entry in the hierarchy is a level.

YAML

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: "Week"
    key:
    - "the_year"
    - "week_of_year"
  - name: "Day"
  hierarchies:
  - name: "Yearly"
    has_all: false
    levels:
    - "Year"
    - "Quarter"
    - "Month"
    - "Day"
  - name: "Weekly"
    has_all: false
    levels:
    - "Year"
    - "Week"
    - "Day"

XML

<Dimension name="Time" table="time_by_day" key="Day">
  <Attributes>
    <Attribute name="Year" column="the_year"/>
    <Attribute name="Quarter">
      <Key>
        <Column name="the_year"/>
        <Column name="quarter"/>
      </Key>
    </Attribute>
    <Attribute name="Month">
      <Key>
        <Column name="the_year"/>
        <Column name="month_of_year"/>
      </Key>
    </Attribute>
    <Attribute name="Week">
      <Key>
        <Column name="the_year"/>
        <Column name="week_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"/>
      <Level attribute="Day"/>
    </Hierarchy>
    <Hierarchy name="Weekly" hasAll="false">
      <Level attribute="Year"/>
      <Level attribute="Week"/>
      <Level attribute="Day"/>
    </Hierarchy>
  </Hierarchies>
</Dimension>

Each <Level attribute="..."/> references an attribute by name. The attribute definitions do most of the work; the hierarchy is just a declaration of which attributes you want and in what order.

Tip

When you are first designing a schema, define your attributes and let users explore the cube before you create any hierarchies. Once you see which drill paths are used regularly, add hierarchies for those paths.

Designing attributes for use in hierarchies

The key rule for hierarchies: each attribute must be functionally dependent on the attribute of the level below it. That means there must be exactly one Quarter for any given Month, and exactly one Year for any given Quarter.

A Year → Month → Week → Day hierarchy would violate this rule, because some days in Week 5 belong to January and some to February.

The practical consequence is that most attributes in a hierarchy need composite keys to capture the parent–child relationship. If your Quarter attribute has only 4 members (because you forgot to include the_year in its key), the level sequence will be 10, 4, 120, 3652 — a non-increasing sequence that signals a modelling mistake.

Order and display of levels

The orderByColumn attribute on <Attribute> controls how members within a level are sorted. The nameColumn controls the display string. These two attributes are independent: you can sort by a numeric column and display a human-readable name column.

Ordinal columns may be of any data type that can be used in an ORDER BY clause. Ordering is scoped per-parent — a day_in_month column cycles from 1 to 28–31 within each month.

The type attribute on <Attribute> (values: String, Integer, Numeric, Boolean, Date, Time, Timestamp) tells Mondrian how to generate SQL for that attribute’s key. The default is Numeric. If the key is a string, Mondrian needs to know so it can wrap values in single quotes:

WHERE productSku = '123-455-AA'

‘All’ and default members

By default, every hierarchy contains a top level called (All), which holds a single member called (All {hierarchyName}). This member is the parent of all other members and represents a grand total. It is also the default member — the member used when the hierarchy is absent from the query axes.

You can customise this behaviour with attributes on <Hierarchy>:

Attribute	Description
hasAll	Whether the (All) level exists. Defaults to true.
allMemberName	Name of the all member. Defaults to "All {hierarchyName}".
allLevelName	Name of the all level. Defaults to "(All)".
defaultMember	Fully-qualified MDX name of the default member.

<Hierarchy name="Yearly" hasAll="false" defaultMember="[Time].[1997].[Q1].[1]">
  ...
</Hierarchy>

When hasAll="false", the default member becomes the first member of the first level — for a Time hierarchy, the first year in the data. This can cause unexpected results when that hierarchy is not on an axis, so prefer hasAll="true" unless you have a specific reason.

When defaultMember is set, it can even be a calculated member.

Attribute hierarchies

MDX does not know about attributes — it only knows about dimensions, hierarchies, levels, and members. Mondrian bridges the gap by automatically generating a single-level hierarchy for each attribute, called an attribute hierarchy.

Attribute hierarchies work exactly like manually declared hierarchies. They let you expose a dozen attributes and start querying them straight away, without writing any <Hierarchy> elements.

To control an attribute hierarchy, use the following attributes on <Attribute>:

<Hierarchy> attribute	<Attribute> attribute	Description
N/A	hasHierarchy	Whether an attribute hierarchy is generated. Default: true.
name	N/A	Always equals the attribute name.
hasAll	hierarchyHasAll	Whether the hierarchy has an (All) level. Default: true.
allMemberName	hierarchyAllMemberName	Name of the all member.
allMemberCaption	hierarchyAllMemberCaption	Caption of the all member.
allLevelName	hierarchyAllLevelName	Name of the all level.
defaultMember	hierarchyDefaultMember	Fully-qualified MDX name of the default member.

Caution

Think twice before disabling the (All) member of an attribute hierarchy or changing its default member. Doing so creates hidden constraints that apply even when the attribute is not on a query axis.

Attributes versus hierarchies

In Mondrian 3, hierarchies were verbose to define and MDX syntax was awkward when a dimension had more than one hierarchy. As a result, most schemas exposed dimensions as a single hierarchy and treated individual levels as the unit of analysis.

Mondrian 4 encourages a different approach: design with many attributes, add hierarchies only where they are useful.

• Define attributes for every column you want to slice by.
• Let users explore the cube.
• When you notice certain combinations of attributes are always used together (e.g. Year → Quarter → Month), create a hierarchy for that drill path.
• Users will still use standalone attributes most of the time.

One nuance: some attributes have within-parent and without-parent variants. For example, [Time].[Month] (120 members over 10 years) is different from [Time].[Month of Year] (12 members). The first lets you compare December 2012 with December 2011; the second lets you compare December with April across all years. You need two separate attributes. A naming convention like "X of Parent" helps users understand which is which.

Schema shortcuts

XML can be verbose. Mondrian provides shortcuts to keep simple things concise.

Attribute as shorthand for a singleton nested element

When an attribute has a single-column key, you can write:

<Attribute name="A" column="c"/>

instead of the longer form:

<Attribute name="A">
  <Key>
    <Column name="c"/>
  </Key>
</Attribute>

When a composite key or cross-table column reference is needed, use the nested <Key> form.

The same shorthand pattern applies across the schema:

Parent element	Shorthand attribute	Equivalent nested element	Description
<Attribute>	keyColumn	<Key>	Column(s) that comprise the key of this attribute.
<Attribute>	nameColumn	<Name>	Column displayed as the member name. Defaults to the key.
<Attribute>	orderByColumn	<OrderBy>	Column(s) that define sort order. Defaults to the key.
<Attribute>	captionColumn	<Caption>	Column that forms the caption. Defaults to the name.
<Measure>	column	<Arguments>	Column(s) passed to the SQL aggregate function.
<Table>	keyColumn	<Key>	Column(s) forming the table’s primary key.
<Link>	foreignKeyColumn	<ForeignKey>	Column(s) forming the foreign key from a link’s referencing table.
<ForeignKeyLink>	foreignKeyColumn	<ForeignKey>	Column(s) linking a measure group’s fact table to a dimension table.

Inherited table attribute

The table attribute on <Dimension>, <Attribute>, and <Column> is inherited from the enclosing element when not explicitly set. This makes single-table dimensions concise — declare table once on <Dimension> and every <Attribute> inside inherits it automatically.

Shared dimensions

If several cubes in the same schema use dimensions with the same definition, define a shared dimension at the schema level and reference it from each cube.

The Measures dimension

Measures are treated as members of a special dimension called Measures. It has a single hierarchy and a single level. Because there is only one hierarchy, MDX lets you omit the hierarchy name:

[Measures].[Unit Sales]

is shorthand for:

[Measures].[Measures].[Unit Sales]

This design means you can change the measure context in a calculation just as easily as you change a time period or a sales region — it enables greater formula reuse and makes access control simpler (a grant on a cell is a three-dimensional coordinate: cube × dimension slice × measure).

Star and snowflake dimensions

Star dimensions

The dimensions seen so far draw all their columns from a single table. These are called star dimensions because they radiate from the fact table like points on a star.

Snowflake dimensions

A snowflake dimension spans two or more dimension tables joined together. Before you define one, ensure:

1. Every table in the snowflake is declared in the <PhysicalSchema>.
2. A <Link> element exists for each join between tables in the snowflake.

Here is the product and product_class pair used to build the [Product] dimension:

YAML

tables:
- name: "product"
  key_column: "product_id"
- name: "product_class"
  key_column: "product_class_id"
links:
- source: "product_class"
  target: "product"
  foreign_key_column: "product_class_id"

XML

<Table name="product" keyColumn="product_id"/>
<Table name="product_class" keyColumn="product_class_id"/>
<Link target="product" source="product_class" foreignKeyColumn="product_class_id"/>

Then define the dimension, specifying table overrides at the attribute level where needed:

YAML

Product:
  table: "product"
  key: "Product Id"
  attributes:
  - name: "Product Family"
    table: "product_class"
    key_column: "product_family"
  - name: "Product Department"
    table: "product_class"
    key:
    - "product_family"
    - "product_department"
  - name: "Brand Name"
    table: "product_class"
    key:
    - "product_family"
    - "product_department"
    - "product_class.brand_name"
  - name: "Product Name"
    table: "product"
    key_column: "product_id"
    name_column: "product_name"
  - name: "Product Id"
    table: "product"
    key_column: "product_id"

XML

<Dimension name="Product" table="product" key="Product Id">
  <Attributes>
    <Attribute name="Product Family" table="product_class" keyColumn="product_family"/>
    <Attribute name="Product Department" table="product_class">
      <Key>
        <Column name="product_family"/>
        <Column name="product_department"/>
      </Key>
    </Attribute>
    <Attribute name="Brand Name" table="product_class">
      <Key>
        <Column name="product_family"/>
        <Column name="product_department"/>
        <Column table="product_class" name="brand_name"/>
      </Key>
    </Attribute>
    <Attribute name="Product Name" table="product" keyColumn="product_id" nameColumn="product_name"/>
    <Attribute name="Product Id" table="product" keyColumn="product_id"/>
  </Attributes>
</Dimension>

The table attribute cascades: <Dimension table="product"> sets the default, <Attribute table="product_class"> overrides it, and <Column table="product_class"> overrides again. Mondrian will report an error if there is no path between the tables, or if there is more than one path.

Caution

Snowflake dimensions introduce an extra join. The join cost often outweighs the storage saving. The most reasonable case for a snowflake is when you are using aggregate tables at the grain of the outer dimension table — avoiding the need to repeat its columns in each aggregate.

Time dimensions

MDX includes time-aware functions — ParallelPeriod, PeriodsToDate, WTD, MTD, QTD, YTD, LastPeriod — that only work correctly when Mondrian knows which attributes represent time periods.

Declare a time dimension by adding type="TimeDimension" to <Dimension>. Then mark each attribute with a levelType value:

levelType value	Meaning
TimeYears	Year
TimeHalfYear	Half-year
TimeQuarters	Quarter
TimeMonths	Month
TimeWeeks	Week
TimeDays	Day
TimeHours	Hour
TimeMinutes	Minute
TimeSeconds	Second

A complete time dimension looks like this:

<Dimension name="Time" table="time_by_day" key="Day" type="TimeDimension">
  <Attributes>
    <Attribute name="Year" keyColumn="the_year" levelType="TimeYears"/>
    <Attribute name="Quarter" levelType="TimeQuarters">
      <Key>
        <Column name="the_year"/>
        <Column name="quarter"/>
      </Key>
    </Attribute>
    <Attribute name="Month" levelType="TimeMonths" nameColumn="month_name" orderByColumn="month_of_year">
      <Key>
        <Column name="the_year"/>
        <Column name="month_of_year"/>
      </Key>
    </Attribute>
    <Attribute name="Week" levelType="TimeWeeks">
      <Key>
        <Column name="the_year"/>
        <Column name="week_of_year"/>
      </Key>
    </Attribute>
    <Attribute name="Day" keyColumn="time_id" levelType="TimeDays"/>
  </Attributes>
  <Hierarchies>
    <Hierarchy name="Yearly" hasAll="true" allMemberName="All Periods">
      <Level attribute="Year"/>
      <Level attribute="Quarter"/>
      <Level attribute="Month"/>
      <Level attribute="Day"/>
    </Hierarchy>
  </Hierarchies>
</Dimension>

Tier and duration attributes

Two attribute shapes are computed from the underlying columns rather than read straight from a key column. Both are Saiku extensions to Mondrian 4 (issue #108): they desugar to a per-dialect SQL expression rendered through the Calcite backend, so you get the binned/derived members without modelling a helper table or a view.

Tier (binning)

A <Tier> turns a numeric column into a small set of ordered, named bins — the schema-native equivalent of LookML’s type: tier. Each bin except the last carries a numeric boundary (its exclusive upper bound); a row takes the label of the first bin whose boundary its value is strictly less than. The final bin omits boundary and captures everything at or above the last bound. Members sort by boundary order, not lexically.

YAML

attributes:
- name: "Size Tier"
  tier:
    column: "units"
    bins:
    - boundary: 10
      label: "Small"     # units < 10
    - boundary: 100
      label: "Medium"    # 10 ≤ units < 100
    - label: "Large"     # units ≥ 100 (open-ended)

XML

<Attribute name="Size Tier">
  <Tier column="units">
    <Bin boundary="10"  label="Small"/>   <!-- units < 10 -->
    <Bin boundary="100" label="Medium"/>  <!-- 10 ≤ units < 100 -->
    <Bin label="Large"/>                  <!-- units ≥ 100 (open-ended) -->
  </Tier>
</Attribute>

column is required; an optional table selects the source table when it is not the attribute’s (or dimension’s) own table.

Duration

A <Duration> computes a numeric interval between two date/timestamp columns in a fixed unit — the equivalent of LookML’s dimension_group: { type: duration }. The members are numbers and sort numerically.

YAML

attributes:
- name: "Lead Time (months)"
  duration:
    start_column: "order_date"
    end_column: "ship_date"
    unit: "MONTH"

XML

<Attribute name="Lead Time (months)">
  <Duration startColumn="order_date" endColumn="ship_date" unit="MONTH"/>
</Attribute>

startColumn and endColumn are required; unit is one of DAY (the default), WEEK, MONTH, QUARTER, YEAR, HOUR, MINUTE, SECOND. An optional table selects the source table.

Note

Tier and duration attributes are computed via the Calcite query backend (mondrian.backend=calcite, the Saiku default), which renders the binning CASE and the date-diff per dialect. They round-trip losslessly through the YAML converter, and the LookML importer maps type: tier / type: duration straight onto them.

Member properties

Member properties attach extra information to members of an attribute — data that is related to the attribute but not used as a key or for grouping. You declare them using <Property> inside <Attribute>:

<Attribute name="City" keyColumn="city_id">
  <Property attribute="Country"/>
  <Property attribute="State"/>
  <Property attribute="City Population" name="Population"/>
</Attribute>

Here, the [City] attribute gains three properties:

• Country and State inherit their name from the referenced attribute.
• City Population is referenced by attribute name but exposed as Population via the explicit name override.

Properties are defined in terms of other attributes in the same dimension. This means each property has a key, a name, a caption, and a sort order — just like any attribute. The referenced attribute must be functionally dependent on the attribute being annotated. A property based on [Zipcode] on [City] would be illegal — a city can have multiple zip codes. But each city has exactly one state, one country, and one population value.

You can access properties in MDX via:

member.Properties("propertyName")

For example:

SELECT {[Measures].[Store Sales]} ON COLUMNS,
  TopCount(
    Filter(
      [Customer].[City].Members,
      [Customer].[City].CurrentMember.Properties("Population") < 10000),
    10,
    [Measures].[Store Sales]) ON ROWS
FROM [Sales]

Mondrian infers the property type from the type attribute of the <Property> definition (String, Numeric, or Boolean) when the property name is a constant string. If you build the property name dynamically with an expression, Mondrian returns an untyped value.

Degenerate dimensions

A degenerate dimension is one so simple that it doesn’t warrant its own dimension table. Consider a payment_method column (values: Credit, Cash, ATM) sitting directly in the fact table. Creating a separate three-row lookup table just for these values adds a join with no benefit.

Instead, declare a dimension without specifying a table, and Mondrian reads the columns from the fact table directly. In M4 you can do this by omitting the table attribute on <Dimension> and ensuring the attribute’s column exists in the fact table:

YAML

Payment Method:
  key: "Payment Method"
  attributes:
  - name: "Payment Method"
    key_column: "payment_method"

XML

<Dimension name="Payment Method" key="Payment Method">
  <Attributes>
    <Attribute name="Payment Method" keyColumn="payment_method"/>
  </Attributes>
</Dimension>

Because there is no join, you do not need a <ForeignKeyLink> foreign-key column — the payment_method column is already in the fact table. No <Link> elements are needed either.

Approximate level cardinality

The approxRowCount attribute on <Attribute> (and on <Level> within explicit hierarchies) tells Mondrian approximately how many distinct members this attribute has. Providing this hint can significantly improve performance by reducing the need for Mondrian to execute COUNT(DISTINCT ...) queries to determine cardinality — particularly noticeable when connecting via XMLA.

<Attribute name="Product Name" keyColumn="product_id" approxRowCount="1560"/>

Default measure attribute

The defaultMeasure attribute on <Cube> lets you explicitly specify which measure is selected when a query does not reference the [Measures] dimension. Without it, Mondrian picks the first measure declared in the cube.

Setting defaultMeasure is especially useful when you want a calculated member to be the default, since calculated members are declared after base measures:

<Cube name="Sales" defaultMeasure="Unit Sales">
  ...
  <CalculatedMember name="Profit" dimension="Measures">
    <Formula>[Measures].[Store Sales] - [Measures].[Store Cost]</Formula>
    ...
  </CalculatedMember>
</Cube>

Functional-dependency optimizations

When Mondrian generates SQL to populate dimension members it uses GROUP BY to deduplicate rows. In some schemas you can declare that certain columns are functionally dependent on others — eliminating redundant GROUP BY columns and improving query performance.

Two attributes enable this:

dependsOnLevelValue on <Property>

Setting dependsOnLevelValue="true" on a property tells Mondrian that the property value is constant for any given level value. For example, a manufacturing plant exists in exactly one city and one state, so State and City properties are functionally dependent on the ManufacturingPlant level.

uniqueKeyLevelName on <Hierarchy>

Setting uniqueKeyLevelName="Vehicle Identification Number" on a hierarchy tells Mondrian that the named level — together with all levels above it — acts as a unique alternate key. For any unique combination of those level values, there is exactly one combination of values for all levels below.

Example:

<Dimension name="Automotive">
  <Attributes>
    <Attribute name="Make" keyColumn="make_id"/>
    <Attribute name="Model" keyColumn="model_id"/>
    <Attribute name="ManufacturingPlant" keyColumn="plant_id"/>
    <Attribute name="Vehicle Identification Number" keyColumn="vehicle_id"/>
    <Attribute name="LicensePlateNum" keyColumn="license_id"/>
  </Attributes>
  <Hierarchies>
    <Hierarchy name="Automotive" hasAll="true" uniqueKeyLevelName="Vehicle Identification Number">
      <Level attribute="Make"/>
      <Level attribute="Model"/>
      <Level attribute="ManufacturingPlant">
        <Property attribute="State"/>
        <Property attribute="City"/>
      </Level>
      <Level attribute="Vehicle Identification Number">
        <Property attribute="Color"/>
        <Property attribute="Trim"/>
      </Level>
      <Level attribute="LicensePlateNum">
        <Property attribute="License State"/>
      </Level>
    </Hierarchy>
  </Hierarchies>
</Dimension>

When Mondrian can confirm that:

1. The query includes the unique-key level, and
2. All properties in the query have dependsOnLevelValue="true"

…it can drop the GROUP BY clause entirely, which is a substantial performance win on large dimension tables.

On databases that permit non-grouped columns in SELECT (such as MySQL), Mondrian can apply a partial optimisation even without a uniqueKeyLevelName — leaving functionally dependent properties out of the GROUP BY while keeping the non-dependent columns in it.

Note

These optimisations are purely additive hints — omitting them never produces wrong results, only potentially slower queries. Add them when you have profiled query performance and confirmed the dimension is a bottleneck.

---

Adapted from the Mondrian project schema guide, available under the Eclipse Public License v1.0.