Semantic Layer Computation Limits

Semantic layer computation limits are the specific categories of analytical operations that a semantic layer cannot express natively within its metric definition language. These limits define the semantic ceiling – the boundary between questions the governed layer handles and questions that escape into workarounds.

Every semantic layer has computation limits. The differences between tools are which limits exist, how many, and what happens when users hit them.

The six common limit types

Six operation categories reliably expose computation limits across semantic layers:

1. Nested aggregations. An aggregation whose input is itself an aggregation. "Average order value by customer segment, then rank segments by that average." The inner step – average order value per segment – must complete before the outer step – ranking – can execute. Most semantic layers treat metrics as terminal: they compute a single aggregation and return it. The result can't serve as input to a second metric operation.

Example: "What is the median monthly spend across all customers?" This requires computing monthly spend per customer (first aggregation), then taking the median across customers (second aggregation).

2. Period-over-period comparisons. Computing the same metric across two time windows and returning the difference or ratio. "Revenue this month vs. revenue same month last year, with percentage change." This requires two evaluations of the same metric at different time boundaries, then arithmetic between the two results.

Example: "Show me week-over-week change in active users for each product line." The semantic layer must compute active users for week N and week N-1, per product line, then subtract.

3. Cross-grain ratios. Metrics where the numerator and denominator operate at different granularities. "Each region's percentage of total revenue." The numerator is revenue at region grain; the denominator is revenue at total grain. The semantic layer must manage both grains within a single expression.

Example: "What percentage of company-wide support tickets does each product account for?" Product-level count divided by total count – two grains in one calculation.

4. Running totals and moving averages. Window-function equivalents that accumulate or average across an ordered set of rows. "Cumulative revenue year-to-date" or "7-day moving average of daily signups." These require the semantic layer to express row ordering and frame boundaries – concepts most metric definition languages don't include.

Example: "Show me the running total of new subscriptions by month, resetting each fiscal year."

5. Level-of-detail calculations. Metrics computed at a fixed grain regardless of the report's display grain. "Customer lifetime value shown on an order-level report." The metric is always computed at customer grain, even when the surrounding context is order-level or product-level.

Example: "On a transaction-level report, show each customer's total historical spend next to each transaction."

6. Multi-step calculations. Chaining multiple operations – filter, aggregate, compare, rank – into a single governed expression. "Filter to enterprise customers, compute quarterly revenue, compare to previous quarter, rank by percentage change, show top 10."

Example: "Among customers who churned last quarter, what was their average monthly spend in the 6 months before churning, ranked by account size?"

What happens when limits are hit

The response is predictable across organizations. The logic migrates to one of three escape routes:

• Derived tables. An engineer builds a pre-computed model – in dbt, in LookML, or in the BI tool's transformation layer – that materializes the complex metric. This is governed by version control but lives outside the semantic layer. It answers one specific question. Changing the question requires an engineering cycle.

• Table calculations or DAX overrides. A user writes a formula at the dashboard level. The logic is ungoverned, invisible to other users, and doesn't benefit from semantic layer protections like join validation or grain management.

• Analyst tickets. A user requests the answer from the data team. An analyst writes custom SQL. The logic lives in someone's query history and is neither reusable nor auditable.

Each escape route produces semantic leakage. The more limits a semantic layer has, the more leakage occurs, and the lower the governed surface area.

How tools differ

Different semantic layers hit different limits at different points. Some support period-over-period natively while lacking nested aggregations. Some handle cross-grain ratios but omit running totals. Tools with composable metrics – where one metric can reference another – tend to cover more of the six categories, because composability is the mechanism that enables multi-step and nested operations.

Evaluating a tool's computation limits requires testing each category with real business questions rather than relying on feature checklists. A tool may claim "period-over-period support" but only handle it for a fixed set of date granularities, excluding dynamic ranges.