Concepts

Cube borrows a lot of terminology from data science and OLAP theory (opens in a new tab), and this document is intended for both newcomers and regular users to refresh their understanding.

We'll use a sample e-commerce database with two tables, orders and line_items to illustrate the concepts throughout this page:

orders

line_items

Cubes

A cube represents a dataset in Cube, and is conceptually similar to a view in SQL (opens in a new tab). Cubes are typically declared in separate files with one cube per file. Within each cube are definitions of dimensions and measures. Typically, a cube points to a single table in your database using the sql_table property:

cubes:
  - name: orders
    sql_table: orders

You can also use the sql property to accommodate more complex SQL queries:

cubes:
  - name: orders
    sql: >
      SELECT * FROM orders, line_items WHERE orders.id = line_items.order_id

Note that cubes support extension, plymorphism, and data blending. Also, cubes may not only be defined statically; you can actually build dynamic data models.

Views

Views sit on top of the data graph of cubes and create a facade of your whole data model with which data consumers can interact. They are useful for defining metrics, managing governance and data access, and controlling ambiguous join paths.

Views can not have their own members. Instead, use the cubes or includes parameters to include measures and dimensions from other cubes into the view. In the example below, we create a new view active_users which is made up of properties from the users cube:

views:
  - name: orders
 
    cubes:
      - join_path: base_orders
        includes:
          - status
          - created_date
          - total_amount
          - total_amount_shipped
          - count
          - average_order_value
 
      - join_path: base_orders.line_items.products
        includes:
          - name: name
            alias: product
 
      - join_path: base_orders.users
        prefix: true
        includes: "*"
        excludes:
          - company

View may not only be defined statically; you can actually build dynamic data models.

Dimensions

Dimensions represent the properties of a single data point in the cube. The orders table contains only dimensions, so representing them in the orders cube is straightforward:

cubes:
  - name: orders
    # ...
 
    dimensions:
      - name: id
        sql: id
        type: number
        # Here we explicitly let Cube know this field is the primary key
        # This is required for de-duplicating results when using joins
        primary_key: true
 
      - name: status
        sql: status
        type: string

The line_items table also has a couple of dimensions which can be represented as follows:

cubes:
  - name: line_items
    # ...
 
    dimensions:
      - name: id
        sql: id
        type: number
        # Again, we explicitly let Cube know this field is the primary key
        # This is required for de-duplicating results when using joins
        primary_key: true
 
      - name: order_id
        sql: order_id
        type: number

Dimensions can have different types. Also, subquery dimensions can be used to join cubes implicitly.

Time-based properties should be modeled as dimensions of the time time. Time dimensions allow grouping the result set by a unit of time (e.g. hours, days, weeks, etc.). In analytics, this is also known as "granularity".

We can add the necessary time dimensions to both data models as follows:

cubes:
  - name: orders
    # ...
 
    dimensions:
      - name: created_at
        sql: created_at
        type: time
 
      - name: completed_at
        sql: completed_at
        type: time

cubes:
  - name: line_items
    # ...
 
    dimensions:
      - name: created_at
        sql: created_at
        type: time

Time dimensions are essential to enabling performance boosts such as partitioned pre-aggregations and incremental refreshes.

Measures

Measures represent the properties of a set of data points in the cube. To add a measure called count to our orders cube, for example, we can do the following:

cubes:
  - name: orders
    # ...
 
    measures:
      - name: count
        type: count

In our LineItems cube, we can also create a measure to sum up the total value of line items sold:

cubes:
  - name: line_items
    # ...
 
    measures:
      - name: total
        sql: price
        type: sum

Measures can be of different types, and you can find them all here.

Joins

Joins define the relationships between cubes, which then allows accessing and comparing properties from two or more cubes at the same time. In Cube, all joins are LEFT JOINs.

In the following example, we are left-joining the line_items cube onto our orders cube:

cubes:
  - name: orders
    # ...
 
    joins:
      - name: line_items
        # Here we use the `CUBE` global to refer to the current cube,
        # so the following is equivalent to `orders.id = line_items.order_id`
        sql: "{CUBE}.id = {line_items.order_id}"
        relationship: many_to_one

There are three types of join relationships (one_to_one, one_to_many, and many_to_one) and a few other concepts.

Segments

Segments are filters that are predefined in the data model instead of a Cube query. They allow simplifying Cube queries and make it easy to re-use common filters across a variety of queries.

To add a segment which limits results to completed orders, we can do the following:

cubes:
  - name: orders
    # ...
 
    segments:
      - name: only_completed
        sql: "{CUBE}.status = 'completed'"

Pre-aggregations

Pre-aggregations are a powerful way of caching frequently-used, expensive queries and keeping the cache up-to-date on a periodic basis. Within a data schema, they are defined under the pre_aggregations property:

cubes:
  - name: orders
    # ...
 
    pre_aggregations:
      - name: main
        measures:
          - count
        dimensions:
          - status
        time_dimension: created_at
        granularity: day

A more thorough introduction can be found in Getting Started with Pre-Aggregations.