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, etc.).
primary_key (optional): Set this to true if this dimension makes up part of (or the whole) primary key for this model.

Examples

Note that what you put in the sql field much match exactly to the syntax for your database (e.g. postgres syntax may differ slightly from snowflake syntax).

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

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