eudaimos opened a new issue, #40917:
URL: https://github.com/apache/superset/issues/40917
### Bug or Feature Request?
Feature request / API completeness gap. The MCP server's chart-authoring
tools cannot express Custom-SQL aggregate metrics, which are required for any
composed/ratio measure that can't be reduced to one of the built-in aggregate
keywords. This makes the MCP a noticeably less-than-equal alternative to the
Explore UI for a common analytics shape.
### Affected MCP Tools
- `generate_chart`
- `update_chart`
- `create_virtual_dataset` (related — see below)
### What the schema currently exposes
In the `xy` (and every other) chart-type config, the metric is a `ColumnRef`:
```jsonc
{
"name": "youtube_share_pct",
"aggregate": "SUM" | "COUNT" | "AVG" | "MIN" | "MAX" | "COUNT_DISTINCT" |
"STDDEV" | "VAR" | "MEDIAN" | "PERCENTILE",
"saved_metric": false
}
```
There is no field for `sqlExpression` / `expressionType="SQL"`. Internally
Superset's form_data fully supports adhoc Custom SQL metrics (the Explore UI
uses them constantly):
```jsonc
{
"expressionType": "SQL",
"sqlExpression": "100.0 * SUM(youtube_deliverables) /
nullIf(SUM(total_deliverables), 0)",
"label": "YouTube Share %",
"hasCustomLabel": true
}
```
The MCP schema simply has no surface that maps to this.
### Concrete failing use-case
Building a dashboard with a virtual dataset that pre-aggregates monthly
counters across multiple dimension partitions:
```
| month | client_status | program_status | total_deliverables |
youtube_deliverables | ... |
```
Dashboard filters can target any subset of the dimensions. We want a Share %
chart whose metric is **stable under any filter combination** — that requires
`100 * SUM(youtube_deliverables) / SUM(total_deliverables)`. Cannot be
expressed because:
- `SUM`, `AVG`, `MAX` on a *pre-computed* `youtube_share_pct` column compose
wrong (weighting a percentage equally across differently-sized partitions).
- The fixed `aggregate` enum has no ratio operator.
- `saved_metric: true` requires the metric to be pre-registered on the
dataset, but...
### Related gap: `create_virtual_dataset` can't author saved metrics or
computed columns either
The current request schema only accepts `dataset_name`, `sql`,
`database_id`, `schema`, `catalog`, `description`. There is no way to attach
`metrics: [...]` (saved Custom-SQL metrics) or computed `columns: [...]` at
dataset-creation time. The Explore-side UI supports both. Result: an
MCP-authored virtual dataset is permanently lacking these capabilities until a
human edits it in the Superset UI.
The MCP equivalent of the Explore "Custom SQL" metric tab + the dataset's
"Metrics" / "Calculated Columns" editor are both unreachable.
### Suggested API shape
For chart configs (`generate_chart` / `update_chart`), extend `ColumnRef`
(or accept an alternative `AdhocMetricRef` discriminated by `expressionType`):
```jsonc
{
"expressionType": "SQL",
"sqlExpression": "100.0 * SUM(num) / nullIf(SUM(den), 0)",
"label": "Share %",
"hasCustomLabel": true
}
```
For `create_virtual_dataset`, accept optional arrays:
```jsonc
{
"database_id": 1,
"sql": "...",
"dataset_name": "...",
"metrics": [
{"metric_name": "share_pct", "expression": "100.0 * SUM(num) /
nullIf(SUM(den), 0)", "verbose_name": "Share %"}
],
"calculated_columns": [
{"column_name": "month_label", "expression": "formatDateTime(month, '%b
%Y')"}
]
}
```
### Workaround today
After MCP creates the chart with a placeholder aggregate metric, open the
chart's Explore view manually, click the metric, switch to **Custom SQL**,
paste the expression, and save. This works but defeats the purpose of
MCP-driven chart authoring for any composed-measure chart.
### Repro
1. `create_virtual_dataset` with a query that emits `numerator` and
`denominator` columns at some grain.
2. Try to `generate_chart` with `chart_type: "xy"` and a `y` metric that
computes `100 * SUM(numerator) / SUM(denominator)`.
3. There is no schema-valid way to express this. Best available is
`aggregate: "AVG"` on a pre-computed ratio column, which composes wrong under
multi-row aggregation.
### Why this matters
Ratios, weighted averages, and conditional aggregates are core analytics
primitives. Sales-funnel conversion rates, time-windowed share %, weighted
CSAT, etc. all require Custom-SQL adhoc metrics. The MCP surface today routes
every such chart through "make it in the UI" — which is fine for one-offs but
not workable for AI-assistant flows that try to author dashboards end-to-end.
### Environment
- Superset version on which this is reproducible: latest (`v6.1.x`) — MCP
tool schemas as shipped, observed via the MCP `get_chart_type_schema` resource.
- Affected since the MCP service was introduced.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
