Dimensions, Measures & Filters¶
The difference between Dimensions and Measures is the most important thing to know about SemQL. Get it right and most queries work.
Dimensions¶
A Dimension is an attribute you slice, filter or group by: names, dates, categories, IDs, statuses.
-- Examples of Dimensions
name
region
created_at
product_category
customer_id
Dimensions map to plain columns in your database, or to expressions. For example, full_name might be defined as CONCAT(first_name, ' ', last_name); you don't need to know.
You use Dimensions in:
SELECT(to show the attribute)WHERE(to filter before aggregation)GROUP BY(to group rows)ORDER BY(to sort)
See Dimensions for how to define them.
Measures¶
A Measure is a predefined, already-aggregated metric: revenue, user counts, order totals, conversion rates.
-- Examples of Measures
total_revenue -- might be SUM(amount - refunds)
user_count -- might be COUNT(DISTINCT user_id)
average_order_value -- might be AVG(amount)
conversion_rate -- might be SUM(conversions) / COUNT(sessions)
Your data team defines Measures once in the semantic layer. They hold the business logic: which columns to use, how to handle nulls, what to exclude, how to aggregate.
You use Measures in:
SELECT(to show the metric)HAVING(to filter after aggregation)ORDER BY(to sort by the metric)
Using a Measure in WHERE or GROUP BY is rejected.
See Measures for how to define them.
Mixing Dimensions and Measures¶
When a query selects Dimensions and Measures together, the result is grouped by the Dimensions. You can leave GROUP BY out: SemQL groups by every non-aggregated expression in SELECT.
-- These two queries are equivalent
SELECT region, total_revenue FROM sales
SELECT region, total_revenue FROM sales GROUP BY region
If you write GROUP BY yourself, it must include every Dimension you select outside an aggregate. See GROUP BY.
The golden rule: never aggregate a Measure¶
This is the most common SemQL mistake. Measures are already aggregated: total_revenue already contains a SUM(). If you wrap it in another aggregate, SemQL rejects the query with the error Measure total_revenue cannot be wrapped in an aggregate function. Reference the Measure directly instead.
-- WRONG: total_revenue is already SUM(amount - refunds)
-- SemQL rejects this as double aggregation
SELECT region, SUM(total_revenue)
FROM sales
GROUP BY region
-- CORRECT: reference the Measure directly
SELECT region, total_revenue
FROM sales
GROUP BY region
The same rule applies to COUNT, AVG, MIN, MAX and every other aggregate:
-- WRONG
SELECT product, AVG(average_rating) FROM products GROUP BY product
-- CORRECT
SELECT product, average_rating FROM products GROUP BY product
Window aggregates over a Measure are allowed, for example a running total with SUM(total_revenue) OVER (ORDER BY order_date).
How to tell if something is a Measure: check the Model in Studio. Measures are listed on the Model's Measures tab, Dimensions on its Dimensions tab. When in doubt, a field that sounds like a business metric (revenue, count, rate, total, average) is probably a Measure.
WHERE vs. HAVING¶
Dimensions and Measures use different filter clauses:
| WHERE | HAVING | |
|---|---|---|
| What it filters | Dimensions (raw column values) | Measures (aggregated results) |
| When it runs | Before aggregation | After aggregation |
| Use for | status = 'active', date >= ... |
total_revenue > 10000, order_count >= 5 |
-- WHERE: filter by a Dimension (region is a Dimension)
SELECT region, total_revenue
FROM sales
WHERE region = 'Europe'
GROUP BY region
-- HAVING: filter by a Measure (total_revenue is a Measure)
SELECT region, total_revenue
FROM sales
GROUP BY region
HAVING total_revenue > 500000
-- Both together
SELECT region, total_revenue
FROM sales
WHERE created_at >= '2024-01-01' -- Dimension filter: rows to include
GROUP BY region
HAVING total_revenue > 100000 -- Measure filter: groups to keep
ORDER BY total_revenue DESC
A simple test: if you could put the condition in WHERE without a GROUP BY, it filters a Dimension. If it only makes sense after grouping (because it involves an aggregated value), it belongs in HAVING. Using a Measure in WHERE is rejected with Measures cannot be used in WHERE.
Predefined Filters¶
Predefined Filters are reusable row conditions defined on a Model. Instead of writing WHERE member_count > 0 everywhere, your data team defines it once as active_only. Anyone who uses organizations[active_only] gets exactly that condition.
Syntax¶
Filters go in the FROM or JOIN clause, in square brackets after the Model name:
-- Single Filter
FROM organizations[active_only]
-- Multiple Filters (all conditions applied together)
FROM sales[last_quarter, high_value]
-- Filter on a joined Model
FROM sales
JOIN customers[enterprise] ON sales.customer_id = customers.customer_id
If the Filter name doesn't exist on that Model, the query is rejected and the error suggests the closest Filter name.
Because the condition lives in one place, every query, AI Analyst and person uses the same criteria. If "enterprise customer" means annual_contract_value >= 50000 AND plan = 'enterprise', customers[enterprise] means that everywhere, instead of everyone writing it slightly differently.
What a Filter can contain¶
A Filter is a SemQL condition over the Model's own Dimensions. It can't use Measures, aggregate functions or raw database SQL. If you need database-specific logic in a Filter, put that logic in a Dimension and reference the Dimension by name in the Filter.
Filters that apply to every query¶
Some Filters apply automatically to every query on their Model, without you naming them. These are guardrails set by your data team.
Availability
- Who can do this: members with the Edit capability in the active Workspace.
To make a Filter apply to every query:
- In Studio, go to Models and select the Model.
- Open the Filters tab and select the Filter, or select New filter.
- Turn on Apply to every query.
- Select Save filter.
AI Analyst can see the Filter and cannot turn it off. For example, if the transactions Model has a Filter for status = 'completed' with Apply to every query turned on, FROM transactions always excludes incomplete transactions, even if you don't ask for it.
See Pre-defined Filters for how to create and manage Filters.
Filters on outer joins¶
A Filter restricts its own Model's rows, not the joined result. On the optional side of an outer join, a row removed by the Filter simply counts as "no match", so the rows from the other side are kept.
-- Every organization is kept. Only verified users are joined;
-- organizations with no verified users get NULL user columns.
SELECT organizations.name, users.email
FROM organizations
LEFT JOIN users[verified] ON organizations.organization_id = users.organization_id
This applies to Filters you name in brackets and to Filters with Apply to every query turned on.
Filters don't belong in SELECT¶
This is a syntax error:
-- WRONG: Filter in SELECT
SELECT organizations[active_only].name FROM organizations
-- CORRECT: Filter in FROM
SELECT name FROM organizations[active_only]
Joins and fan-out¶
When you join two Models, one side's rows can repeat. For example, if one customer has many orders, joining customers to orders repeats each customer row once per order. A SUM, COUNT or AVG over the repeated Model would then be inflated.
SemQL detects this from the relationships defined between your Models and refuses the query with a fan-out error. The check applies when you join on the keys of a relationship defined between the Models. It covers aggregate functions you write yourself and Measures defined with SUM, COUNT or AVG. COUNT(DISTINCT …), MIN and MAX are not affected, because repeated rows don't change them. A Measure defined with COUNT(DISTINCT …), MIN or MAX isn't refused.
-- REFUSED (when customer_count is defined as COUNT(customer_id) or similar):
-- each customer row repeats once per order in this join
SELECT customers.region, customer_count, order_count
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
-- WORKS: count distinct customers instead
SELECT customers.region,
COUNT(DISTINCT customers.customer_id) AS customers,
order_count
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
To fix a fan-out error, either:
- use
COUNT(DISTINCT …),MINorMAX, or - aggregate the repeated Model first, in a CTE or a separate query, and then join the result.
See Relationships for how relationships and their cardinality are defined.