Hierarchies

You can use the hierarchies parameter within cubes to define hierarchies. You can think about a hierarchy as a means to group dimensions together and organize them into levels of granularity, allowing users to drill down or roll up for analysis.

Hierarchies display is subject to support in visualization tools. Check APIs & Integrations for details. You can also preview hierarchies in Playground.

Any hierarchy should have the following parameters: name and levels.

Just like other members, hierarchies can be exposed to data consumers through views. List them in the includes parameter of a view’s cubes block alongside measures, dimensions, and segments.

Parameters

`name`

The name parameter serves as the identifier of a hierarchy. It must be unique among all members within a cube and follow the naming conventions.

cubes:
  - name: users
    sql_table: users

    # ...

    hierarchies:
      - name: location
        title: User Location
        levels:
          - state
          - city

cube(`users`, {
  sql_table: `users`,

  // ...

  hierarchies: {
    location: {
      title: `User Location`,
      levels: [
        state,
        city
      ]
    }
  }
})

`title`

You can use the title parameter to set the human-readable name of a hierarchy:

cubes:
  - name: users
    sql_table: users

    # ...

    hierarchies:
      - name: location
        title: User Location
        levels:
          - state
          - city

cube(`users`, {
  sql_table: `users`,

  // ...

  hierarchies: {
    location: {
      title: `User Location`,
      levels: [
        state,
        city
      ]
    }
  }
})

`levels`

The levels parameter is used to define the levels of the hierarchy. You can do so by listing the dimensions included in the hierarchy, from less granular to more granular ones:

cubes:
  - name: users
    sql_table: users

    dimensions:
      - name: state
        sql: state
        type: string

      - name: city
        sql: city
        type: string

    hierarchies:
      - name: location
        title: User Location
        levels:
          - state
          - city

cube(`users`, {
  sql_table: `users`,

  dimensions: {
    state: {
      sql: `state`,
      type: `string`
    },

    city: {
      sql: `city`,
      type: `string`
    }
  },

  hierarchies: {
    location: {
      title: `User Location`,
      levels: [
        state,
        city
      ]
    }
  }
})

You can include the same dimension in multiple hierarchies. It is also possible to include a dimension from a joined cube into a hierarchy:

cubes:
  - name: users
    sql_table: users

    joins:
      - name: orders
        sql: "{CUBE.id} = {orders.user_id}"
        relationship: one_to_many

    dimensions:
      - name: state
        sql: state
        type: string

      - name: city
        sql: city
        type: string

      - name: status
        sql: status
        type: string

    hierarchies:
      - name: details
        title: User Details
        levels:
          - status
          - state
          - city

      - name: statuses
        title: User & Order Statuses
        levels:
          - status
          - orders.status

cube(`users`, {
  sql_table: `users`,

  joins: {
    orders: {
      sql: `${CUBE.id} = ${orders.user_id}`,
      relationship: `one_to_many`
    }
  },

  dimensions: {
    state: {
      sql: `state`,
      type: `string`
    },

    city: {
      sql: `city`,
      type: `string`
    },

    status: {
      sql: `status`,
      type: `string`
    }
  },

  hierarchies: {
    location: {
      title: `User Location`,
      levels: [
        state,
        city
      ]
    },

    statuses: {
      title: `User & Order Statuses`,
      levels: [
        status,
        orders.status
      ]
    }
  }
})

`public`

The public parameter is used to manage the visibility of a hierarchy. Valid values for public are true and false. When set to false, this hierarchy cannot be queried through the API. Defaults to true.

cubes:
  - name: users
    sql_table: users

    # ...

    hierarchies:
      - name: location
        title: User Location
        levels:
          - state
          - city
        public: false

cube(`users`, {
  sql_table: `users`,

  // ...

  hierarchies: {
    location: {
      title: `User Location`,
      levels: [
        state,
        city
      ],
      public: false
    }
  }
})

Data Modeling

Configuration

Core Data APIs

Embed APIs

Other APIs

CLI

JavaScript SDK

Parameters

`name`

`title`

`levels`

`public`

​Parameters

​name

​title

​levels

​public

Parameters

`name`

`title`

`levels`

`public`