Advanced Metrics
Advanced Metrics extend the Semantic Layer with computation capabilities beyond simple aggregations. These include cumulative metrics, period-over-period comparisons, moving averages, percentiles, ratios, compound annual growth rates (CAGR), and contribution analysis. All endpoints are served at the base path /api/v1/metrics/advanced.
Available Computations
| Computation | Description |
|---|---|
| Cumulative | Running total of a metric over time |
| Period Comparison | Period-over-period comparison (MoM, YoY, QoQ) |
| Moving Average | Sliding window average over a time dimension |
| Percentile | Percentile distribution of metric values |
| Ratio | Ratio between two metrics |
| CAGR | Compound annual growth rate |
| Contribution | Part-of-whole contribution analysis |
Cumulative Metrics
Computes the running total of a metric over a time dimension.
Endpoint: POST /api/v1/metrics/advanced/cumulative
Request:
{
"modelId": "550e8400-e29b-41d4-a716-446655440000",
"metricName": "total_revenue",
"timeDimension": "order_date",
"startDate": "2026-01-01",
"endDate": "2026-12-31",
"timeGrain": "MONTH"
}Response: A list of time-series data points with the cumulative value at each grain.
Period-Over-Period Comparison
Computes comparison metrics between the current period and the previous period.
Endpoint: POST /api/v1/metrics/advanced/period-comparison
Request:
{
"modelId": "550e8400-e29b-41d4-a716-446655440000",
"metricName": "total_revenue",
"timeDimension": "order_date",
"periodType": "MONTH",
"periodEnd": "2026-06-30",
"groupByDimensions": ["region"]
}Period Types:
| Type | Comparison |
|---|---|
DAY | Day over day |
WEEK | Week over week |
MONTH | Month over month |
QUARTER | Quarter over quarter |
YEAR | Year over year |
Response: A PeriodComparisonResult containing current period value, previous period value, absolute change, and percentage change.
Moving Average
Generates SQL for computing a moving average of a metric over a sliding time window.
Endpoint: GET /api/v1/metrics/advanced/moving-average
| Parameter | Type | Default | Description |
|---|---|---|---|
modelId | UUID | Required | Semantic model ID |
metricName | String | Required | Metric to average |
timeDimension | String | Required | Time dimension |
windowSize | Integer | 7 | Size of the sliding window |
windowUnit | String | DAY | Unit of the window (DAY, WEEK, MONTH) |
Response: Generated SQL for the moving average computation.
Percentile Metrics
Generates SQL for computing percentile distributions of a metric.
Endpoint: POST /api/v1/metrics/advanced/percentile
Request:
{
"modelId": "550e8400-e29b-41d4-a716-446655440000",
"metricName": "order_total",
"timeDimension": "order_date",
"percentiles": [0.25, 0.50, 0.75, 0.90, 0.99],
"groupByDimensions": ["region"]
}Response: Generated SQL for the percentile computation.
Metric Ratios
Generates SQL for computing the ratio between two metrics.
Endpoint: GET /api/v1/metrics/advanced/ratio
| Parameter | Type | Required | Description |
|---|---|---|---|
modelId | UUID | Yes | Semantic model ID |
numeratorMetric | String | Yes | Metric for the numerator |
denominatorMetric | String | Yes | Metric for the denominator |
groupByDimensions | List | No | Dimensions for grouping |
Response: Generated SQL for the ratio computation.
CAGR (Compound Annual Growth Rate)
Generates SQL for computing the compound annual growth rate of a metric.
Endpoint: GET /api/v1/metrics/advanced/cagr
| Parameter | Type | Required | Description |
|---|---|---|---|
modelId | UUID | Yes | Semantic model ID |
metricName | String | Yes | Metric to compute CAGR for |
timeDimension | String | Yes | Time dimension |
startDate | LocalDate | Yes | Start of the measurement period |
endDate | LocalDate | Yes | End of the measurement period |
Response: Generated SQL for the CAGR computation.
Contribution Analysis
Generates SQL for computing the contribution of each dimension value to the total metric.
Endpoint: GET /api/v1/metrics/advanced/contribution
| Parameter | Type | Required | Description |
|---|---|---|---|
modelId | UUID | Yes | Semantic model ID |
metricName | String | Yes | Metric to analyze |
groupByDimension | String | Yes | Dimension to break down by |
Response: Generated SQL that computes each dimension value's share of the total.
SQL Generation Pattern
Advanced metric endpoints that return SQL (moving average, percentile, ratio, CAGR, contribution) provide the generated SQL in a map response:
{
"sql": "SELECT region, SUM(order_total) / (SELECT SUM(order_total) FROM orders) AS contribution FROM orders GROUP BY region"
}The caller can then execute this SQL through the Query Engine or modify it further before execution.