MCP `generate_chart` / `update_chart` should support Custom-SQL adhoc metrics (and saved metrics / computed columns on virtual datasets)
- Dominant language
- Python
- Stars
- 74.8k
- Forks
- 18.3k
- Avg merge
- 2d 7h
- Merged PRs (30d)
- 692
Description
### 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.
Contributor guide
Research direction
Start with the MCP schemas for generate_chart, update_chart, and create_virtual_dataset, focusing on ColumnRef and the current virtual-dataset request fields. Compare these entry points with the form_data and Explore capabilities described in the issue. Done means schema-valid chart requests can express Custom-SQL adhoc metrics and virtual datasets can accept saved metrics and calculated columns.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python, sql
- Domain
- api, data-visualization
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 48/100