Define the measure once.
Star and snowflake models over any connection, with a dimension catalogue, a measure catalogue, named hierarchies and always-applied filters that travel with the model. ROLLUP, CUBE and GROUPING SETS, time-hierarchy drill, period-over-period comparisons, Top-N and pivot — and the compiled SQL, always visible.
Three reasons a semantic model earns its keep
A saved question defines a measure in one place. A model defines it in the only place.
The same number everywhere
- A measure catalogue: aggregation, column or expression, format hint
- Revenue means one thing across every dashboard and subscription
- Embeds and API consumers get the same definition, not a copy
- An agent answering a question uses your measure, not its own
- Change it once and every consumer moves together
Dimensions, hierarchies, joins
- Base fact table plus joins to dimensions — INNER, LEFT, RIGHT or FULL
- A dimension catalogue with type hints and hierarchy levels
- Named hierarchies as ordered level chains
- A built-in time hierarchy: year > quarter > month > week > day > hour
- Query with dimensions, measures, filters, order-by, limit and offset
Model filters are always applied
- A scoping rule attached to the model, not to each question
- Applied on every query against the model, without exception
- It travels: dashboards, embeds, subscriptions, agents
- Nothing to forget on the twelfth question someone builds
- A security boundary, and treated as one
What a model actually holds
Five parts. None of them requires a separate modelling language or a repository of files to keep in sync with the database.
A base fact table, and joins out to dimensions
The model names its fact table, then joins to dimension tables with an explicit join type — INNER, LEFT, RIGHT or FULL. Star or snowflake, both are expressible, and the choice is yours to make explicitly rather than a shape inferred from foreign keys behind your back.
Columns, or expressions, with type hints
Each dimension is a column or a raw expression, carrying a type hint and, where it belongs to one, a hierarchy level. The type hint is what lets the filter interface offer the right operators, and the level is what makes drill-down mean something.
Aggregation, expression, format
Each measure pairs an aggregation with a column or expression and a format hint. The OLAP compiler extends the base five aggregations with COUNT(DISTINCT …) and case-conditional measures — so a headcount and a conditional revenue split are model concepts rather than things each analyst reinvents in a calculated field.
Ordered level chains
A hierarchy is a named, ordered chain of levels — the drill path a business already thinks in. Region → country → city. Category → subcategory → product. Naming it once means every dashboard that drills does so along the same path, in the same order, with the same labels.
Always applied. This is the security boundary.
Model filters are not defaults that a query can override. They are applied to every query against the model, on every surface. That is what makes them useful as a boundary: a tenant scope, a legal-entity restriction or an exclusion of test data is defined at the model and cannot be dropped by a question, a dashboard card, an embed or an agent.
The alternative — re-implementing the same predicate in every question — works right up until the day somebody builds the twelfth question and forgets.
Six endpoints, including the ones you use before committing
Compile-only to see the SQL without running it. Run. Drill to move down a hierarchy. Explain for the plan. Validate for a verdict on the model itself. And AI-suggest for a candidate model derived from the connection.
Four modes, and an honest table of which engines have them
Subtotals are where BI tools quietly diverge from SQL. Here is exactly what compiles where.
| ModeAggregationSet on the model query | What it produces | Engine support |
|---|---|---|
NONE | A plain grouped result. No subtotal rows. | Every engine. |
ROLLUP | Subtotals along the grouping order, plus a grand total — the classic hierarchical report. | Postgres, BigQuery, Snowflake, Redshift, SQL Server, H2. MySQL and MariaDB via WITH ROLLUP. |
CUBE | Every combination of the grouping columns — all the cross-cutting subtotals at once. | Postgres, BigQuery, Snowflake, Redshift, SQL Server, H2. |
GROUPING_SETS | Exactly the combinations you name, and no others. Explicit sets, for when CUBE is too much and ROLLUP is the wrong shape. | Postgres, BigQuery, Snowflake, Redshift, SQL Server, H2. |
A built-in time hierarchy, and comparisons that scale with the level
Every business asks the same two questions about a number: how did it move, and how does it compare with this time last year.
| LevelTime hierarchyyear > quarter > month > week > day > hour | Prior period | Prior year — LAG scaled per level |
|---|---|---|
| Year | LAG 1 | LAG 1 |
| Quarter | LAG 1 | LAG 4 |
| Month | LAG 1 | LAG 12 |
| Week | LAG 1 | LAG 52 |
| Day | LAG 1 | LAG 365 |
| Hour | LAG 1 | LAG 8760 |
Compiled through each dialect's date functions
The six levels are compiled through whatever the target engine actually spells them as — DATE_TRUNC, DATETRUNC, TRUNC_TIMESTAMP, DATE_FORMAT, STRFTIME, or DATEADD/DATEDIFF arithmetic. Where a level does not exist the compiler says so rather than approximating: SQLite, for instance, rejects week and quarter buckets.
Data in the result set, not a rendering trick
Prior-period and prior-year values arrive as their own columns next to the measure. That means they export, they feed a chart, they feed an alert threshold, they feed a subscription, and they feed an agent's answer — rather than existing only inside one visualisation's own idea of a comparison.
Top-N, pivot, and the one combination the compiler refuses
ROW_NUMBER, partitioned by your dimensions
The top three products per region. The five slowest routes per depot. Top-N compiles to a ROW_NUMBER partitioned by the grouping dimensions, ranked ascending or descending, so "per group" means per group rather than "overall, then filtered".
Two passes, because the values are in the data
Row dimensions, one column dimension, one measure. Qrly first probes for the distinct values of the column dimension, then compiles conditional aggregation per discovered value.
The two-pass design is the point: the pivot's columns come from what is in the table today, not from a list somebody typed into a configuration screen last quarter and never revisited.
Rollup cannot combine with comparisons or Top-N
A subtotal row has no meaningful prior period, and a rank within a group has no meaningful grand total. Rather than emitting SQL that runs and returns something misleading, the compiler rejects the combination outright.
This is the sort of guard that costs you thirty seconds of annoyance once, and saves a number nobody would have checked.
Down the hierarchy you named
The drill endpoint moves down a named hierarchy or the built-in time hierarchy, carrying the filters that got you there. Because the levels were declared in the model, drilling is a defined operation rather than a guess about which column comes next.
Proposed nightly. Approved by a person.
Modelling a warehouse from scratch is tedious. Having it modelled for you without review is worse. This is the middle.
How a candidate model is derived
- Classify every table. A heuristic classifier tags each one
FACT,DIMENSIONorBRIDGE. Facts look like high row counts, many foreign keys and numeric measures; dimensions look like low cardinality, descriptive columns and inbound foreign-key references; bridges are what many-to-many relationships need instead of a silently duplicated grain. - Derive candidate measures. Numeric columns on the fact table become measure candidates with an aggregation attached — and additivity is respected, so a ratio is not proposed as something to SUM.
- Derive candidate dimensions and hierarchies. Including the two that nearly always apply: a temporal hierarchy wherever a date column exists, and a geographic one wherever country, region or city columns do.
- Propose, on a schedule. Suggestions are generated nightly, so a new table that appeared in the warehouse this week shows up as a proposal rather than as an absence nobody noticed.
- A human approves or rejects. Each suggestion is accepted or declined explicitly. Nothing becomes a live model — and therefore nothing becomes an authoritative measure definition — on a machine's say-so.
The same capability is available on demand through the AI-suggest endpoint, and the agent layer exposes it as suggest_olap_model alongside validate_olap_model. There too, the write path stages a proposal: create_olap_model and update_olap_model never execute, they stage a pending action for approval.
Nothing here is a black box
The single most common complaint about a semantic layer is that you cannot see what it did. So the designer shows you.
| DesignerControlTry it, in place | What it does |
|---|---|
| Group by | Pick the dimensions from the catalogue you just defined. |
| Measures | Pick from the measure catalogue — the same definitions every consumer will use. |
| Subtotal mode | NONE, ROLLUP, CUBE or GROUPING SETS, subject to what the engine supports. |
| Comparisons | Prior period, prior year, or neither — and the guard tells you when the combination is invalid. |
| Top-N | Rank within the group, ascending or descending. |
| Pivot | Rows, one column dimension, one measure — probed and compiled live. |
| Limit | Keep the trial cheap while you are still iterating on the model. |
| Result table | The rows the model produces, right there in the designer. |
| Compiled SQL | The exact statement, with a copy button. Paste it into your own client and get the same number. |
Common questions
Why do I need a semantic model if I already have saved questions?
Because a saved question defines a measure in one place, and a semantic model defines it in the only place. A model holds a measure catalogue — the aggregation, the column or expression, and a format hint — so revenue means one thing across every dashboard, subscription, embed and agent answer that touches the model.
The second reason is the security boundary: model filters are always applied, so a scoping rule travels with the model rather than being re-implemented in each question and forgotten in one of them.
Which engines support ROLLUP, CUBE and GROUPING SETS?
All four aggregation modes — NONE, ROLLUP, CUBE and GROUPING_SETS with explicit sets — are supported on PostgreSQL, BigQuery, Snowflake, Redshift, SQL Server and H2.
MySQL and MariaDB get WITH ROLLUP only, which means subtotals along the grouping order but no CUBE and no arbitrary grouping sets. That is an engine limitation, and Qrly states it rather than emulating it badly.
How do period-over-period comparisons work?
Two comparisons are available: prior period, compiled as LAG 1, and prior year, compiled as a LAG scaled to the time level — 1 at year, 4 at quarter, 12 at month, 52 at week, 365 at day and 8760 at hour.
Each emits its own columns alongside the measure, named <measure>_prior_period and <measure>_prior_year, so the comparison is data in the result set rather than a rendering trick in one chart. Comparisons require exactly one time dimension, and cannot be combined with rollup.
How does pivot work?
You supply row dimensions, one column dimension and one measure. Qrly runs it in two passes: first a distinct-value probe to discover what the column dimension actually contains, then conditional aggregation per discovered value.
That is why the pivot's columns match your data rather than a list someone typed into a configuration screen last quarter.
Does the AI build the model on its own?
It proposes; a human approves. A heuristic classifier tags tables FACT, DIMENSION or BRIDGE, then derives candidate measures, dimensions and temporal and geographic hierarchies. Suggestions are proposed nightly and are approved or rejected by a person.
There is also an AI-suggest endpoint alongside compile, run, drill, explain and validate, so you can ask for a candidate model on demand — but nothing becomes a live model without an explicit approval, and the agent's model-writing tools stage a proposal rather than executing.
Can I see the SQL a model query produces?
Always. The designer's Try-it pane lets you set group-by, measures, subtotal mode, limit, comparisons, Top-N and pivot, then shows both the result table and the compiled SQL with a copy button. There is also a compile-only endpoint and an explain endpoint.
A semantic layer you cannot read the output of is a semantic layer you cannot debug, so the compiled SQL is never hidden.