Measures

For Data Analysts

Measures are numeric aggregations that form the quantitative backbone of your semantic layer. They define how values are calculated when querying your cubes.

What is a Measure?

A measure is a numeric value that is aggregated across rows. Examples include:

Total revenue (SUM)
Order count (COUNT)
Average order value (AVG)
Maximum discount (MAX)
Unique customers (COUNT_DISTINCT)

┌─────────────────────────────────────────────────────────────┐
│                   Measure Aggregation                        │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│   Raw Data                    Aggregated Result              │
│   ┌──────────────────┐       ┌──────────────────┐           │
│   │ order_id │ amount │       │ total_revenue    │           │
│   │    1     │  100   │       │                  │           │
│   │    2     │  250   │  ───► │      450         │           │
│   │    3     │  100   │       │   SUM(amount)    │           │
│   └──────────────────┘       └──────────────────┘           │
│                                                              │
└─────────────────────────────────────────────────────────────┘

Measure Types

Olytix Core supports the following measure types:

Type	Description	SQL Generated	Use Case
`count`	Count of rows	`COUNT(*)`	Total orders, events
`count_distinct`	Count of unique values	`COUNT(DISTINCT col)`	Unique customers, products
`count_distinct_approx`	Approximate unique count	`APPROX_COUNT_DISTINCT(col)`	Large-scale unique counts
`sum`	Sum of values	`SUM(col)`	Revenue, quantities
`avg`	Average of values	`AVG(col)`	Average order value
`min`	Minimum value	`MIN(col)`	First date, lowest price
`max`	Maximum value	`MAX(col)`	Last date, highest price
`number`	Custom SQL expression	As specified	Complex calculations
`running_total`	Cumulative sum	`SUM() OVER (...)`	Running totals
`boolean`	Boolean aggregation	`BOOL_OR(col)`	Any true, all true

Basic Measure Definition

cubes:
  - name: orders
    sql: "SELECT * FROM {{ ref('fct_orders') }}"

    measures:
      - name: count
        type: count
        description: "Total number of orders"

Measure Configuration Options

Each measure supports these properties:

measures:
  - name: total_revenue
    type: sum
    sql: total_amount               # Column or expression to aggregate
    title: "Total Revenue"          # Display name
    description: "Sum of all order amounts"
    format: currency                # Formatting hint
    shown: true                     # Whether to expose in API
    filters:                        # Measure-level filters
      - sql: "status = 'completed'"
    rolling_window:                 # Rolling window calculation
      trailing: "7 days"
    drill_members:                  # Columns to show in drill-down
      - order_id
      - order_date
      - customer_id
    meta:                           # Custom metadata
      owner: finance-team
      certified: true

COUNT Measures

Count the number of rows:

measures:
  # Simple row count
  - name: count
    type: count
    description: "Total number of orders"

  # Filtered count
  - name: completed_count
    type: count
    filters:
      - sql: "status = 'completed'"
    description: "Number of completed orders"

COUNT_DISTINCT Measures

Count unique values in a column:

measures:
  # Unique customers
  - name: unique_customers
    type: count_distinct
    sql: customer_id
    description: "Number of unique customers"

  # Unique products ordered
  - name: unique_products
    type: count_distinct
    sql: product_id
    description: "Number of distinct products ordered"

  # Approximate count for large datasets
  - name: approx_unique_visitors
    type: count_distinct_approx
    sql: visitor_id
    description: "Approximate unique visitors (faster on large datasets)"

SUM Measures

Sum numeric values:

measures:
  # Simple sum
  - name: total_revenue
    type: sum
    sql: total_amount
    format: currency
    description: "Total revenue from all orders"

  # Sum with expression
  - name: total_discount
    type: sum
    sql: "unit_price * quantity * discount_rate"
    format: currency
    description: "Total discount amount"

  # Sum with filter
  - name: refunded_amount
    type: sum
    sql: total_amount
    filters:
      - sql: "status = 'refunded'"
    format: currency
    description: "Total refunded amount"

AVG Measures

Calculate average values:

measures:
  # Simple average
  - name: avg_order_value
    type: avg
    sql: total_amount
    format: currency
    description: "Average order amount"

  # Average with expression
  - name: avg_items_per_order
    type: avg
    sql: "CAST(quantity AS FLOAT)"
    format: number
    description: "Average number of items per order"

  # Weighted average (using number type)
  - name: weighted_avg_price
    type: number
    sql: "SUM(unit_price * quantity) / NULLIF(SUM(quantity), 0)"
    format: currency
    description: "Quantity-weighted average price"

MIN and MAX Measures

Find minimum and maximum values:

measures:
  # Minimum value
  - name: min_order_amount
    type: min
    sql: total_amount
    format: currency
    description: "Smallest order amount"

  # Maximum value
  - name: max_order_amount
    type: max
    sql: total_amount
    format: currency
    description: "Largest order amount"

  # First and last dates
  - name: first_order_date
    type: min
    sql: order_date
    description: "Date of first order"

  - name: last_order_date
    type: max
    sql: order_date
    description: "Date of most recent order"

NUMBER Measures (Custom Calculations)

Use number type for custom SQL expressions:

measures:
  # Ratio calculation
  - name: conversion_rate
    type: number
    sql: "CAST(COUNT(CASE WHEN status = 'completed' THEN 1 END) AS FLOAT) / NULLIF(COUNT(*), 0)"
    format: percent
    description: "Order completion rate"

  # Complex expression
  - name: gross_margin
    type: number
    sql: "(SUM(revenue) - SUM(cost)) / NULLIF(SUM(revenue), 0)"
    format: percent
    description: "Gross margin percentage"

  # Conditional aggregation
  - name: net_revenue
    type: number
    sql: "SUM(CASE WHEN status != 'refunded' THEN total_amount ELSE 0 END)"
    format: currency
    description: "Revenue after refunds"

Formatting

Use the format property to specify display formatting:

measures:
  - name: total_revenue
    type: sum
    sql: total_amount
    format: currency      # $1,234.56

  - name: conversion_rate
    type: number
    sql: "..."
    format: percent       # 45.2%

  - name: avg_quantity
    type: avg
    sql: quantity
    format: number        # 1,234.56

  - name: order_count
    type: count
    format: integer       # 1,234

Measure Filters

Apply filters that are specific to a measure:

measures:
  # Revenue from completed orders only
  - name: completed_revenue
    type: sum
    sql: total_amount
    filters:
      - sql: "status = 'completed'"
    description: "Revenue from completed orders"

  # Multiple filters
  - name: large_completed_orders
    type: count
    filters:
      - sql: "status = 'completed'"
      - sql: "total_amount >= 100"
    description: "Completed orders over $100"

  # Filter with dimension reference
  - name: domestic_revenue
    type: sum
    sql: total_amount
    filters:
      - sql: "region = 'US'"
    description: "Revenue from US orders"

Rolling Window Measures

Calculate measures over a rolling time window:

measures:
  # 7-day rolling average
  - name: rolling_7d_revenue
    type: sum
    sql: total_amount
    rolling_window:
      trailing: "7 days"
    description: "Revenue over trailing 7 days"

  # 30-day rolling count
  - name: rolling_30d_orders
    type: count
    rolling_window:
      trailing: "30 days"
    description: "Orders in last 30 days"

  # Rolling window with offset
  - name: previous_7d_revenue
    type: sum
    sql: total_amount
    rolling_window:
      trailing: "7 days"
      offset: start
    description: "Revenue from 7-14 days ago"

Drill Members

Specify which fields to show when drilling into a measure:

measures:
  - name: total_revenue
    type: sum
    sql: total_amount
    format: currency
    drill_members:
      - order_id
      - order_date
      - customer_id
      - status
      - total_amount
    description: "Total revenue with drill-down capability"

When a user drills into this measure, they see the underlying detail rows with these columns.

Complete Example

Here is a comprehensive orders cube with various measure types:

cubes:
  - name: orders
    sql: "SELECT * FROM {{ ref('fct_orders') }}"
    description: "E-commerce order transactions"

    measures:
      # Basic counts
      - name: count
        type: count
        description: "Total number of orders"
        drill_members: [order_id, order_date, status, total_amount]

      - name: unique_customers
        type: count_distinct
        sql: customer_id
        description: "Number of unique customers"

      # Revenue measures
      - name: total_revenue
        type: sum
        sql: total_amount
        format: currency
        description: "Total revenue from all orders"

      - name: gross_revenue
        type: sum
        sql: "total_amount - discount_amount"
        format: currency
        description: "Revenue after discounts"

      - name: completed_revenue
        type: sum
        sql: total_amount
        format: currency
        filters:
          - sql: "status = 'completed'"
        description: "Revenue from completed orders only"

      # Averages
      - name: avg_order_value
        type: avg
        sql: total_amount
        format: currency
        description: "Average order amount"

      - name: avg_items_per_order
        type: avg
        sql: quantity
        format: number
        description: "Average items per order"

      # Extremes
      - name: max_order_amount
        type: max
        sql: total_amount
        format: currency
        description: "Largest single order"

      - name: first_order_date
        type: min
        sql: order_date
        description: "Date of first order"

      # Calculated measures
      - name: order_completion_rate
        type: number
        sql: |
          CAST(COUNT(CASE WHEN status = 'completed' THEN 1 END) AS FLOAT) /
          NULLIF(COUNT(*), 0)
        format: percent
        description: "Percentage of orders completed"

      - name: revenue_per_customer
        type: number
        sql: "SUM(total_amount) / NULLIF(COUNT(DISTINCT customer_id), 0)"
        format: currency
        description: "Average revenue per unique customer"

    dimensions:
      - name: order_id
        type: number
        sql: order_id
        primary_key: true

      - name: order_date
        type: time
        sql: order_date
        granularities: [day, week, month, quarter, year]

      - name: status
        type: string
        sql: status

Best Practices

Use Descriptive Names

# Good
- name: total_revenue
- name: avg_order_value
- name: unique_customers

# Avoid
- name: revenue
- name: aov
- name: customers

Always Include Descriptions

measures:
  - name: net_revenue
    type: sum
    sql: "total_amount - refund_amount"
    description: |
      Net revenue after refunds.
      Calculated as total_amount minus refund_amount.
      Does not include shipping or tax.

Use Appropriate Formats

Match the format to the data type:

measures:
  - name: revenue
    format: currency      # For money values

  - name: rate
    format: percent       # For percentages

  - name: quantity
    format: integer       # For whole numbers

Handle NULL Values

Protect against division by zero and NULL:

measures:
  - name: avg_discount_rate
    type: number
    sql: "SUM(discount) / NULLIF(SUM(subtotal), 0)"

Next Steps

Dimensions - Create grouping and filtering attributes
Joins - Connect measures across cubes
Pre-aggregations - Optimize measure calculations

What is a Measure?​

Measure Types​

Basic Measure Definition​

Measure Configuration Options​

COUNT Measures​

COUNT_DISTINCT Measures​

SUM Measures​

AVG Measures​

MIN and MAX Measures​

NUMBER Measures (Custom Calculations)​

Formatting​

Measure Filters​

Rolling Window Measures​

Drill Members​

Complete Example​

Best Practices​

Use Descriptive Names​

Always Include Descriptions​

Use Appropriate Formats​

Handle NULL Values​

Next Steps​