Dimensions and Measures

Dimensions and Measures define how your data is segmented and summarized. For example:

dimensions:
  - name: country
    title: 'Country of origin'
    sql: country
    type: string 
 
measures:
  - name: count
    type: count
    title: "# of Customers"

With a single country dimension and a count measure, the following queries can be generated:

Total customers (count of all rows).
Customers by country (grouped by the country column).
Filtered counts (e.g. customers in a specific country).

Dimensions

Dimensions are the fields you want to filter or group by.

Dimensions must come from columns in the base table you define using sql_table or sql.
You can define a dimension for every column if you want all columns exposed, but you don't need to.

  dimensions:
    - name: email
      title: "Email Address"
      sql: email
      type: string
 
    - name: country
      title: "Country"
      sql: country
      type: string
 
    - name: created_at
      title: "Created at"
      sql: created_at
      type: time

Basic Structure

Each dimension has:

name: A unique identifier (no spaces or special characters).
title (optional): A user-friendly name for display in the UI.
sql: The column or SQL expression from your base table/query.
type: The data type (string, number, time, geo).
primary_key (optional): Set this to true if it uniquely identifies rows.

Examples

Combining first and last names
Instead of defining separate dimensions for first_name and last_name, create a full_name dimension using an SQL expression:
```
dimensions:
  - name: full_name
    title: "Full Name"
    type: string
    sql: CONCAT(first_name, ' ', last_name)
```

Converting Unix timestamps
If your table stores timestamps in Unix time, convert them to a date/time:

dimensions:
  - name: created_at
    title: "Signed Up At"
    type: time
    sql: TO_TIMESTAMP(created_at)

Applying numeric conversions
For currency or other numeric transformations, use a calculated expression:

dimensions:
  - name: price
    title: "Price in Dollars"
    type: number
    sql: price_in_cents / 100.0

Anonymizing or Transforming Data
Use SQL expressions to mask or obfuscate sensitive columns before exposing them in the UI. For example, if you only want to show part of a user’s email:
```
dimensions:
  - name: email_prefix
    title: "Email Prefix"
    type: string
    sql: SUBSTRING(email, 1, POSITION('@' IN email) - 1)
```

Measures

Measures are the values you aggregate (such as a count of users or total revenue).

  measures:
    - name: revenue
      type: sum
      sql: price_usd

Basic Structure

Each measure has:

name: A unique internal identifier.
title (optional): A user-friendly name for display in the UI.
type: The measure type (e.g., count, sum, avg).
sql: Points to the column or expression you want to aggregate.

filters (optional): A condition for a metric's calculation.

  measures:
      - name: us_revenue
        type: sum
        sql: price_usd
        filters: 
          - sql: "{CUBE}.country = 'United States'"

meta (optional): a parameter to store additional information for a measure. E.g. use meta to pass a $ symbol for a currency column or any other custom info you want to access in your charting components.
```
  measures:
      - name: price
        title: price in USD
        type: sum
        sql: price_usd
        meta:
          pretext: $
```
⚡️
Embeddable's Starter Components support pretext out of the box.

Supported Measure Types

Embeddable (via Cube) supports the following measure types:

Examples

Basic Count

measures:
  - name: total_customers
    title: "Total Customers"
    type: count
    sql: id

Summing a Numeric Column

measures:
  - name: total_sales
    title: "Total Sales"
    type: sum
    sql: amount

Average Order Value

measures:
  - name: average_order_value
    title: "Average Order Value"
    type: avg
    sql: total_price

Count Distinct (e.g., unique emails)

measures:
  - name: unique_customers
    title: "Unique Customers"
    type: count_distinct
    sql: email

Calculated measures

This example demonstrates how to reference measures within other measure definitions.

measures:
  - name: count
    sql: id
    type: count
 
  - name: paid_orders
    title: "Paid Orders"
    sql: id
    type: count
    filters:
      - sql: "{CUBE}.status = 'paid'"
  
  - name: percentage_paid
    sql: "100.0 * {paid_orders} / {count}"
    type: number
    format: percent

Rolling Count
This measure counts orders over a rolling window, allowing you to track an ongoing total:

measures:
    - name: rolling_count
      type: count
      title: "Rolling Orders"
      description: "Cumulative count"
      rolling_window:
        trailing: unbounded

For more details, see the Cube docs on rolling windows (opens in a new tab).

Defining data models Joins