Calculated Dimensions

Calculated dimensions are row-level computed fields that produce grouping values. Unlike calculated metrics (which aggregate numbers into KPIs), dimensions transform each row before any grouping happens. The result appears in the ROWS or COLUMNS area of a card.

Use them to bucket orders by size, truncate dates to months or quarters, combine text fields, or apply any row-level logic that your raw data doesn't already have.

Creating in the Explorer

The Explorer lets you create calculated dimensions by writing a SQL expression with field references.

Steps

1. Open the Explorer and click + Add Calculated Field
2. Select Calculated Dimension
3. Enter a name (e.g., "Order Size Bucket")
4. Write your SQL expression using {FieldName} tokens to reference fields
5. Click Validate Expression to check for errors
6. Select the output type (String, Number, or Date)
7. Click Create Dimension

Expression Syntax

Use {FieldName} tokens as placeholders for field references. The tokens are replaced with fully-qualified column names at query time.

CASE WHEN bucketing:

CASE
  WHEN {Amount} > 1000 THEN 'Large'
  WHEN {Amount} > 100 THEN 'Medium'
  ELSE 'Small'
END

Date truncation:

DATE_TRUNC('month', {Order Date})

Combining text fields:

CONCAT({First Name}, ' ', {Last Name})

Handling nulls:

COALESCE({Region}, 'Unknown')

Example: Order Size Bucket

• Expression: CASE WHEN {Amount} > 1000 THEN 'Large' WHEN {Amount} > 100 THEN 'Medium' ELSE 'Small' END
• Output Type: String
• Result: Each row is categorized as Large, Medium, or Small based on its amount

Defining in a Template

For version-controlled definitions, bulk configuration, or CI/CD workflows, define calculated dimensions in the calculatedDimensions section of your domain template.

Expression Syntax

Expressions use {input_name} tokens as placeholders for field references. The tokens are replaced with fully-qualified SQL at query time.

calculatedDimensions:
  order_month:
    label: "Order Month"
    expression: "DATE_TRUNC('month', {order_date})"
    inputs:
      order_date: { dataset: orders, field: order_date }
    dataType: date

Two Input Types

Column reference -- points to a physical column in a dataset:

inputs:
  order_date:
    dataset: orders     # Dataset name
    field: order_date   # Column name

Dimension reference -- points to another calculated dimension (enables nesting):

inputs:
  order_month:
    dimension: order_month   # Name of another calculated dimension

Common Patterns

Date truncation:

order_quarter:
  label: "Order Quarter"
  expression: "DATE_TRUNC('quarter', {order_date})"
  inputs:
    order_date: { dataset: orders, field: order_date }
  dataType: date

CASE WHEN bucketing:

order_size:
  label: "Order Size"
  expression: |
    CASE
      WHEN {amount} > 1000 THEN 'Large'
      WHEN {amount} > 100 THEN 'Medium'
      ELSE 'Small'
    END
  inputs:
    amount: { dataset: orders, field: amount }
  dataType: string

Combining text fields:

full_name:
  label: "Full Name"
  expression: "CONCAT({first_name}, ' ', {last_name})"
  inputs:
    first_name: { dataset: customers, field: first_name }
    last_name: { dataset: customers, field: last_name }
  dataType: string

Handling nulls:

region_clean:
  label: "Region"
  expression: "COALESCE({region}, 'Unknown')"
  inputs:
    region: { dataset: customers, field: region }
  dataType: string

Grain Mappings

Grain mappings tell Semaphor how a calculated dimension maps to columns in other datasets. They are needed when a calculated metric references fields from multiple datasets and you group by a calculated dimension.

The problem: If you define order_month as DATE_TRUNC('month', orders.order_date), Semaphor needs to know what column in the returns dataset represents the same time grain.

grainMappings:
  - sourceDimension: order_month        # The calculated dimension
    targetDataset: returns              # The other dataset
    targetColumn: return_date           # The column to DATE_TRUNC in that dataset

When are grain mappings needed?

Only when all three conditions are true:

1. You have a calculated metric that spans multiple datasets (cross-dataset metric)
2. You're grouping by a calculated dimension
3. The dimension's time grain needs to be applied to columns in other datasets

Example: Net Revenue (orders.amount - returns.amount) grouped by Order Month requires a grain mapping so Semaphor knows to use DATE_TRUNC('month', returns.return_date) for the returns dataset CTE.

Without the grain mapping, Semaphor wouldn't know how to align the returns data to the same monthly grain as orders.

Field Reference