The Semantic Ceiling
The semantic ceiling is the upper limit of analytical questions a semantic layer can answer natively. Below the ceiling, queries flow through governed metric definitions. Above it, logic escapes into workarounds – derived tables, table calculations, custom SQL, or analyst tickets.
Every BI tool with a semantic layer has a ceiling. The question is where it sits and what happens when users hit it.
Where the ceiling shows up
The ceiling is invisible for simple queries. Every semantic layer handles basic aggregations: revenue by month, order count by region, average deal size by segment. Slice-and-dice at a single grain, with simple filters, works universally.
The ceiling appears when queries require composition – when the answer depends on combining multiple analytical operations into a single result.
Six query types reliably expose the ceiling:
Nested aggregations. "Average order value by category, then rank categories by that average." The inner aggregation (average per category) must complete before the outer operation (ranking) can run. Most semantic layers treat metrics as terminal – they can't feed into further calculations.
Period-over-period comparisons. "Revenue this month vs. same month last year, with percentage change." This requires two passes over the data at different time windows, then a cross-window calculation. Dynamic date ranges make it harder.
Cross-grain ratios. "Each product's percentage of total revenue." The numerator (product-level revenue) operates at a different grain than the denominator (total revenue). The semantic layer must manage both grains simultaneously.
Running totals and moving averages. Window-function equivalents that accumulate across rows. The semantic layer needs to express ordering and frame boundaries, which most metric definition languages don't support.
Level-of-detail calculations. "Customer lifetime value shown on an order-level report." The metric is computed at customer grain regardless of the report's display grain.
Multi-step calculations. Chaining filter, aggregate, compare, and rank operations in sequence. "Filter to active customers, compute monthly spend, compare to previous month, rank by change." Each step depends on the previous one.
What happens above the ceiling
When users hit the ceiling, the response follows a predictable pattern:
The analyst path. A user files a ticket. An analyst writes custom SQL, delivers the answer, and moves on. The logic lives in the analyst's query history. It's undocumented, unrepeatable, and invisible to the semantic layer.
The engineering path. A data engineer builds a derived table – a pre-computed model in dbt or the BI tool's transformation layer – that materializes the complex metric. The logic is governed by version control, but it's outside the semantic layer. Changes require an engineering cycle. Business users can't modify it. And it only answers the specific question it was built for.
The spreadsheet path. A user exports the data and finishes the calculation in Excel or Google Sheets. The logic lives on someone's laptop. It may never be shared, and it will almost certainly diverge from whatever the "official" answer turns out to be.
All three paths produce semantic leakage – business logic escaping the governed layer into locations where it can't be audited, reused, or trusted at scale.
Raising the ceiling
The ceiling is a function of the semantic layer's query language. Languages that treat metrics as composable – where one metric can reference another, where aggregations can be chained, where grain can shift within a single expression – raise the ceiling substantially.
A practical evaluation method: take 20 real business questions from your organization, spanning simple lookups to multi-step analytical questions. Run each through the semantic layer. Count how many it handles natively. That ratio – the governed surface area – tells you where the ceiling sits for your specific use case.
The goal is a ceiling high enough that the vast majority of day-to-day analytical questions stay inside the governed layer. The remaining edge cases can route to custom SQL or engineering, but they should be exceptions rather than a routine escape path.
The Holistics Perspective
The semantic ceiling is the central evaluation criterion for BI tools with semantic layers. Holistics' AQL pushes this ceiling higher than conventional semantic layers by handling nested aggregations, cross-grain ratios, running totals, and multi-step calculations natively – the exact query types that cause most tools to force workarounds.
See how Holistics approaches this →