Calculated Dimensions

Calculated dimensions are row-level computed fields that produce grouping values. Unlike 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. Choose the Per row type
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 your domain template.

On a Dataset

Most calculated dimensions belong on their dataset, as a dimension with an expression:

datasets:
  - name: orders
    fields:
      dimensions:
        - name: order_month
          label: "Order Month"
          dataType: date
          expression: "DATE_TRUNC('month', order_date)"
          granularity: month
          format:
            type: temporal_bucket
            presentation:
              mode: pattern
              pattern: MMM YYYY

The expression uses the dataset's identifiers and dimensions by name, plus the column in the dimension's own sourceField. See Inline Expressions.

Composed Dimensions

A composed dimension lives in the top-level composedDimensions list. Its expression uses {input_name} tokens, and each input names its dataset:

composedDimensions:
  - name: order_month
    label: "Order Month"
    expression: "DATE_TRUNC('month', {order_date})"
    inputs:
      order_date: { dataset: orders, field: order_date }
    dataType: date
    granularity: month
    format:
      type: temporal_bucket
      presentation:
        mode: pattern
        pattern: MMM YYYY

Two Input Types

Field reference points to a field of a dataset:

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

Dimension reference points to another composed dimension (enables nesting):

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

Common Patterns

These patterns work as dataset dimension expressions. Each example shows the dimension entry under fields.dimensions.

Date truncation:

- name: order_quarter
  label: "Order Quarter"
  dataType: date
  expression: "DATE_TRUNC('quarter', order_date)"
  granularity: quarter
  format:
    type: temporal_bucket
    presentation:
      mode: preset
      preset: quarter_short

CASE WHEN bucketing (sourceField lets the expression read the amount column):

- name: order_size
  label: "Order Size"
  dataType: string
  sourceField: amount
  expression: |
    CASE
      WHEN amount > 1000 THEN 'Large'
      WHEN amount > 100 THEN 'Medium'
      ELSE 'Small'
    END

Combining text fields:

- name: full_name
  label: "Full Name"
  dataType: string
  expression: "CONCAT(first_name, ' ', last_name)"

Handling nulls:

- name: region_clean
  label: "Region"
  dataType: string
  expression: "COALESCE(region, 'Unknown')"

Grain Mappings

Grain mappings tell Semaphor how a composed dimension maps to columns in other datasets. They are needed when a composed metric uses inputs from multiple datasets and you group by a composed 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 composed 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 composed metric that spans multiple datasets (cross-dataset metric)
2. You're grouping by a composed 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

Composed dimension properties:

For dataset dimension properties, see Datasets & Fields.

Temporal validation

granularity describes the analytical bucket; format controls only its display. Semaphor rejects these configurations:

# Invalid: a format cannot exist without a grain
dataType: date
format:
  type: temporal_bucket
  presentation: { mode: pattern, pattern: MMM YYYY }

# Invalid: date-only fields cannot use sub-day grains
dataType: date
granularity: hour

# Invalid: temporal presentation cannot be attached to a string result
dataType: string
granularity: month
format:
  type: temporal_bucket
  presentation: { mode: auto }

Presentation never changes grouping identity. Query results retain the canonical bucket value and metadata; card or semantic presentation controls the label, and the viewer's Display formats preferences apply when neither layer supplies an explicit format.