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.
A saved question defines a measure in one place. A model defines it in the only place.
Five parts. None of them requires a separate modelling language or a repository of files to keep in sync with the database.
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.
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.
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.
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.
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.
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.
Subtotals are where BI tools quietly diverge from SQL. Here is exactly what compiles where.
NONEROLLUPWITH ROLLUP.CUBEGROUPING_SETSWITH ROLLUP only — no CUBE, no arbitrary grouping sets
Stated rather than emulated badly
Every business asks the same two questions about a number: how did it move, and how does it compare with this time last year.
<measure>_prior_period
<measure>_prior_year
Requires exactly one time dimension
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.
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.
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".
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.
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.
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.
Modelling a warehouse from scratch is tedious. Having it modelled for you without review is worse. This is the middle.
FACT, DIMENSION or BRIDGE. 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.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.
The single most common complaint about a semantic layer is that you cannot see what it did. So the designer shows you.
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.
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.
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.
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.
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.
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.
Define the measure once, attach the boundary to the model, and let every dashboard, embed, subscription and agent read from the same definition.