A dimension is a value per row that people group or filter by, such as Region or Amount Band. Unlike a measure, Kater does not work a dimension out again for each grouping. It reads the value from each row. This page shows how to declare a dimension and when a change keeps a field. It also shows windows in a shared model.

How it works

A dimension holds one value for each row of its model, such as the region of each order. When a query groups by a dimension, the dimension splits the rows of each measure. Each region’s row of Orders counts only the orders of that region.

1
SELECT
2
  shop.region,
3
  shop.count_distinct__order_id
4
FROM workspace.shop AS shop
5
GROUP BY
6
  shop.region
7
ORDER BY
8
  2 DESC

Each order has one region, so the five rows add up to the 3,000 orders of the sample shop. The 64 orders with no region group under null.

You declare dimensions in every kind of model:

Syntax

1
-- @description What one value means.
2
DIMENSION('name', expression) AS "Label"

• Declarations are outermost. A DIMENSION sits in the final SELECT, never inside a CTE, a subquery or a union branch.
• A new field needs a new name. It cannot take the name of a field in scope, from any model the SELECT reads, in any letter case.
• The formula decides the kind of field. A DIMENSION over a window in a query is a custom calculation, and the compiler warns you. See Custom calculations.

Pass through or declare

How you write a field from a source decides what you get. Some changes keep the source’s field, under its own name. Every other change makes a new value, which needs a new name.

A format step keeps the field

The Region Codes query writes each region in upper case.

1
SELECT
2
  UPPER(shop.region) AS region_code,
3
  shop.count_distinct__order_id
4
FROM workspace.shop AS shop
5
GROUP BY
6
  UPPER(shop.region)

UPPER is one format step, so the column is still the region field. The alias region_code is only its label. A query that reads Region Codes selects the column by its field name:

1
SELECT
2
  q.region,
3
  q.count_distinct__order_id
4
FROM query.region_codes AS q

The rows are the same five, still labelled region_code.

Any other change makes a new value

The Region Labels query replaces a missing region with “Unknown”, and declares nothing.

1
SELECT
2
  COALESCE(shop.region, 'Unknown') AS region_label,
3
  shop.count_distinct__order_id
4
FROM workspace.shop AS shop
5
GROUP BY
6
  COALESCE(shop.region, 'Unknown')

The query runs, and the 64 orders with no region show as Unknown. The compiler adds a hint: “region_label has no field name, so other models can’t read it.” A query that reads q.region_label from it is refused: “Column ‘region_label’ isn’t in ‘query.region_labels’ (as q).”

COALESCE gives a different value, so it is a new field and needs a name. The hint’s fix, Name this field as a dimension, declares it under its alias. A label for missing values shows the declared form.

Examples

Sizes from a CASE

Which orders are large? The orders dataset declares Order Size by hand, because no operation writes a CASE.

1
  DIMENSION('order_size', CASE WHEN orders.amount >= 100 THEN 'large' ELSE 'small' END) AS "Order Size",

1
SELECT
2
  shop.order_size,
3
  shop.count_distinct__order_id,
4
  shop.sum__quantity
5
FROM workspace.shop AS shop
6
GROUP BY
7
  shop.order_size
8
ORDER BY
9
  1

An order of exactly 100 is large, because the CASE tests >= 100. The 31 orders with no amount are small: a CASE sends a missing value to its ELSE. A bucket keeps them null. See Groups, buckets and flags.

A value read from JSON

How many orders came through each channel? The orders table keeps the channel and the campaign in a JSON column, attributes. The orders dataset reads them out as dimensions:

1
  DIMENSION('channel', orders.attributes ->> '$.channel') AS "Channel",
2
  DIMENSION('campaign', (
3
    orders.attributes -> '$.campaign'
4
  ) ->> '$.name') AS "Campaign",

1
SELECT
2
  shop.channel,
3
  shop.count_distinct__order_id
4
FROM workspace.shop AS shop
5
GROUP BY
6
  shop.channel
7
ORDER BY
8
  2 DESC

On DuckDB, ->> reads a JSON value as text, and -> reads it as JSON. Campaign reads the name inside a nested object. Write the same thing on another warehouse with that warehouse’s JSON functions.

A label for missing values

How do you show the orders with no region as “No region”? Declare the COALESCE under its own name.

1
SELECT
2
  DIMENSION('region_label', COALESCE(shop.region, 'No region')) AS "Region Label",
3
  shop.count_distinct__order_id
4
FROM workspace.shop AS shop
5
GROUP BY
6
  1
7
ORDER BY
8
  2 DESC

The 64 orders that grouped under null now have a label. Region Label is a new field, so another query can read it as q.region_label.

Two fields in one

Which pairs of region and channel bring the most orders? Join the two values into one segment.

1
SELECT
2
  DIMENSION('segment', shop.region || ' ' || shop.channel) AS "Segment",
3
  shop.count_distinct__order_id
4
FROM workspace.shop AS shop
5
GROUP BY
6
  1
7
ORDER BY
8
  2 DESC

The first 5 of 17 rows. Four regions and four channels make 16 segments. The 17th row is null: when the region is missing, || gives null, so those 64 orders have no segment. Wrap the region in COALESCE to give them one.

A dimension from a joined model

How many orders and customers does each country have? Country belongs to the customers dataset. semantic.shop joins each order to its customer, and passes Country through with this line:

1
  c.country,

The join that brings in c sits below the semantic model’s SELECT:

1
FROM kater_dataset.orders AS o
2
-- @label Customer
3
LEFT JOIN RELATIONSHIP('customer', kater_dataset.customers AS c ON o.customer_id = c.customer_id, 'many_to_one')

A query of the workspace groups by it like any other dimension:

1
SELECT
2
  shop.country,
3
  shop.count_distinct__order_id,
4
  shop.count_distinct__customer_id
5
FROM workspace.shop AS shop
6
GROUP BY
7
  shop.country
8
ORDER BY
9
  2 DESC

Each customer has one country, so Customers adds up to the shop’s 240. By region, Customers adds up to 976. Region belongs to the order, and most customers ordered in more than one region. See Relationships and joins.

A measure’s value as a dimension

How many days had one order, two orders, and so on? Evaluate Orders for each day, then group the days by that number.

1
WITH daily AS (
2
  SELECT
3
    shop.order_date,
4
    EVAL(shop.count_distinct__order_id) AS orders_that_day
5
  FROM workspace.shop AS shop
6
  GROUP BY
7
    shop.order_date
8
)
9
SELECT
10
  daily.orders_that_day,
11
  MEASURE('days', KATER__COUNT(daily.order_date)) AS "Days"
12
FROM daily
13
GROUP BY
14
  daily.orders_that_day
15
ORDER BY
16
  1

The first 8 of 12 rows. The busiest day had 14 orders. EVAL fixes Orders at the CTE’s grouping, one value for each day. That value is now a dimension, so the outer SELECT groups by it like any other. The 12 rows count 633 days with at least one order.

Classification

-- @categorical and -- @continuous say what kind of values a dimension holds. The editor calls this Values.

A number can be either. A numeric ID is categorical, and a count can be continuous. The classification changes what Kater offers for the field:

• Filter values. A categorical field’s filter offers a list of its values. A continuous number or date offers a range, from its smallest to its largest value.
• The Derive menu. A continuous number offers Bucket, Is Greater Than, Is Less Than and Is Between. A categorical number, such as a code, offers Is Equal To, Is One Of and Map in their place. See Groups, buckets and flags.
• Explore. When Explore draws a query’s result, a continuous number is a value on the chart. A categorical number, such as an ID, is a category.

Where a classification comes from:

• Sync writes it for each column of a database dataset. Sync reads a sample of up to 100,000 rows. Dates, times, floats and numbers with decimal places are continuous. A whole number is continuous when more than half of its values are distinct. Text, true or false, JSON and other whole numbers are categorical. So the shop’s Quantity is categorical.
• A reference keeps it. A pass-through, an alias or a cast keeps the classification of the field it reads.
• Kater works out many formulas. A comparison or a text function is categorical. A date part is a categorical number, and a date grain is continuous. When Kater cannot tell, the field has none until you set it. Channel, read from JSON, has none.
• You set it. Select Values in the field panel, the field menu or the Fields sheet: Automatic, Categorical or Continuous. In a query, the field menu has Classification under Edit. Each writes the directive through the field.classify operation.

Windows in a shared model

In a Kater dataset or a semantic model, a window over the model’s own rows is a dimension. Such a window runs over dimensions only. A window over a measure needs a query’s grouping to mean anything. Write it in a query, where it is a custom calculation. The Windows menu in a shared model offers windows on dimensions only, and writes them as DIMENSION.

Where the window can sit

Kater works out a measure again from its model’s rows for each reader. It cannot work out a window again from those rows. So a model with no GROUP BY cannot hold a window beside a measure that readers group or filter by it. The compiler refuses it with unsupported_measure_context.

For example, this line in semantic.shop numbers each customer’s orders, oldest first:

1
  DIMENSION(
2
    'order_sequence',
3
    ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.ordered_at, o.order_id)
4
  ) AS "Order Sequence",

The semantic model compiles. But a query that groups Orders by Order Sequence is refused: “measure ‘count_distinct__order_id’ can’t be grouped or filtered by a value its source computes with a window over its own rows, because the measure’s rows can’t recompute that window. Select the value in a query first, then group or filter by it in a query that reads that one.”

Each customer’s first, second and later orders

How many first orders, second orders and later orders did the shop get? Put the window in a Kater dataset of its own, with no measures. A standard Kater dataset declares every column of its table first, so the window comes after them:

1
---
2
name: order_sequence
3
model_type: kater_dataset
4
label: Order Sequence
5
---
6

7
SELECT
8
  DIMENSION('order_id', orders.order_id) AS "Order ID",
9
  DIMENSION('customer_id', orders.customer_id) AS "Customer ID",
10
  DIMENSION('region', orders.region) AS "Region",
11
  DIMENSION('currency', orders.currency) AS "Currency",
12
  DIMENSION('order_date', orders.order_date) AS "Order Date",
13
  DIMENSION('ordered_at', orders.ordered_at) AS "Ordered At",
14
  DIMENSION('amount', orders.amount) AS "Amount",
15
  DIMENSION('quantity', orders.quantity) AS "Quantity",
16
  DIMENSION('discount', orders.discount) AS "Discount",
17
  DIMENSION('is_gift', orders.is_gift) AS "Is Gift",
18
  DIMENSION('notes', orders.notes) AS "Notes",
19
  DIMENSION('attributes', orders.attributes) AS "Attributes",
20
  DIMENSION('tags', orders.tags) AS "Tags",
21
  -- @description The order's position among its customer's orders, starting at 1.
22
  DIMENSION(
23
    'order_sequence',
24
    ROW_NUMBER() OVER (PARTITION BY orders.customer_id ORDER BY orders.ordered_at, orders.order_id)
25
  ) AS "Order Sequence"
26
FROM shop.sales.orders AS orders

Then semantic.shop joins it one to one on the order, and passes Order Sequence through:

1
  seq.order_sequence,

1
LEFT JOIN RELATIONSHIP('sequence', kater_dataset.order_sequence AS seq ON o.order_id = seq.order_id, 'one_to_one')

1
SELECT
2
  shop.order_sequence,
3
  shop.count_distinct__order_id
4
FROM workspace.shop AS shop
5
GROUP BY
6
  shop.order_sequence
7
ORDER BY
8
  1

The first 8 of 26 rows. Every one of the 240 customers has at least five orders, so the first five rows each count 240. One customer has 26 orders. The window runs in its own dataset, so Orders reads the finished numbers through the join. order_id breaks ties between two orders placed at the same time.

You can also select the window’s value in a query first, without a measure. A second query that reads that one can then group by it, with a measure of its own.

Steps in a session

The GA4 Event Steps model numbers each event in its session, and names the next event.

1
  -- @description Position of this event in its session, starting at 1.
2
  DIMENSION(
3
    'step_number',
4
    ROW_NUMBER() OVER (PARTITION BY events.session_key ORDER BY events.event_timestamp, events.event_key)
5
  ) AS "Step Number",
6
  -- @description The session's next event, or empty when this is the session's last event.
7
  DIMENSION(
8
    'next_event_name',
9
    LEAD(events.event_name) OVER (PARTITION BY events.session_key ORDER BY events.event_timestamp, events.event_key)
10
  ) AS "Next Event",

Rules

• A dimension is a value per row. A reader groups and filters by it. It does not total it again.
• One format or timeframe step keeps the field. Any other change is a new value, and needs a new name.
• A SELECT holds a field once. Two outputs that keep the same field, such as two grains of Order Date, are refused. Declare one of them under its own name. See Dates and times.
• Every output of a dataset needs a declaration. In a Kater dataset or a custom SQL dataset, even UPPER(orders.region) needs a DIMENSION.
• A standard Kater dataset keeps every column. It declares each column of its table first, unchanged. Add new dimensions after them.
• A label never names a field. SQL reads a field by its name, never by its label. A field has no @label directive.
• A workspace root cannot run a window. Its optional filters apply first, so the value would change for each reader. Put the window in a query, or in the shared model.

Common errors

Next

• Dates and times: group dates by month or week, and pull out parts such as the day of the week.
• Groups, buckets and flags: turn values into groups people can read.