SS: Add Pivot Tables as a first-class workspace surface

[BorisTyshkevich]

Summary

Add Pivot Tables as a first-class workspace resource and application surface, parallel to Dashboards—not as another saved-query panel type.

A Pivot Table opens in its own top-level tab/route, owns its own authoring and viewing lifecycle, references one or more ordinary saved queries as data sources, and persists a dedicated portable Pivot document in the workspace.

The surface should let analysts arrange dimensions on rows and columns, aggregate one or more measures, inspect subtotals and grand totals, sort, conditionally format values, apply filters, and export the visible result.

Product model

The workspace should treat these as distinct first-class resources:

Saved queries
Dashboards
Pivot Tables

A Pivot Table is not:

• a Dashboard tile;
• a Workbench result panel;
• a visualization branch inside query.spec.panel;
• an ephemeral transpose of the current query result.

It is a named analytical document that can be created, opened, edited, duplicated, exported, imported, and linked directly.

User value

Pivot tables are a primary analysis surface for Superset and spreadsheet-oriented BI users. They need to answer questions such as:

• revenue by region and month;
• incidents by service and severity;
• customer count by plan and cohort;
• totals and subtotals across several measures;
• where values are unusually high, low, or changing.

The separate surface provides room for serious BI interaction without forcing dense controls or a large matrix into a Dashboard tile.

Navigation and lifecycle

• Add a first-class Pivot Tables entry alongside the existing Workbench and Dashboard navigation.
• Opening a Pivot Table uses a dedicated top-level tab/route, following the same broad ownership model as Dashboard.
• Support editable and read-only/view modes.
• A Pivot Table has a stable workspace id and a direct/shareable open reference.
• Route teardown must destroy requests, reactive effects, menus, observers, and grid resources.
• Reopening or rebuilding the route must not leak listeners or retain stale query results.
• Unsaved authoring changes must be protected from accidental replacement or navigation.

The exact URL shape may follow the established Dashboard open-source pattern, but Pivot Tables must have their own resource identity and route semantics.

Workspace document

Introduce a versioned portable document, conceptually:

interface PivotTableDocumentV1 {
  version: 1;
  id: string;
  name: string;
  description?: string;
  sourceQueryId: string;
  rows: PivotDimensionV1[];
  columns: PivotDimensionV1[];
  measures: PivotMeasureV1[];
  filters?: PivotFilterV1[];
  presentation?: PivotPresentationV1;
}

The concrete schema should follow repository conventions, canonical ordering, generated types/validators, migrations, diagnostics, and portable Library/workspace round-tripping.

A Pivot Table references an ordinary saved query rather than copying SQL. The saved query remains the owner of SQL and typed parameter declarations; the Pivot document owns analytical arrangement and presentation.

Deleting or replacing a referenced query must produce an explicit broken-reference diagnostic rather than silently removing or rewriting the Pivot Table.

Authoring experience

The dedicated Pivot authoring surface should provide explicit selectors for:

• source saved query;
• row dimensions;
• column dimensions;
• measures and aggregation functions;
• ordering;
• subtotals and grand totals;
• filters and parameter values;
• null labels;
• field formatting;
• conditional formatting;
• matrix limits.

Authoring should use cached columns from the most recent successful source execution. Changing the configuration should not execute on every keystroke; provide an explicit Apply/Run boundary where appropriate.

A normal workflow should be:

1. Create a Pivot Table.
2. Select a saved query as its source.
3. Run or preview the source.
4. Arrange fields into Rows, Columns, Measures, and Filters.
5. Save the Pivot document.
6. Reopen it later from the workspace Pivot Tables list.

Data contract

The source query returns ordinary rows. Pivot configuration explicitly selects raw result columns for:

• rows: zero or more ordered dimensions;
• columns: zero or more ordered dimensions;
• measures: one or more measure definitions;
• optional filter, ordering, totals, null-label, and formatting settings.

A measure definition should include:

• exact source result-column identity;
• aggregation: sum, count, min, max, avg, or value where input is already uniquely aggregated;
• optional label and field-format override.

Prefer stable column names over positional indices in the persisted Pivot document. Result-schema changes must diagnose missing or incompatible fields and offer an explicit repair flow; they must not silently bind a different column by position.

Do not infer business metrics from column names. Auto-configuration may suggest dimensions and numeric measures, but persisted configuration is authoritative.

Aggregation semantics

• Use raw values for grouping identity; formatting applies only to presentation.
• Preserve first-seen ordering by default; allow explicit ascending/descending ordering by label or measure.
• Define deterministic null grouping and display.
• Multiple rows contributing to one pivot cell aggregate according to the authored operation.
• value diagnoses duplicate contributing rows rather than silently choosing first or last.
• Numeric aggregation rejects or visibly diagnoses incompatible values.
• Empty combinations render as blank/null, not zero unless explicitly configured.
• Subtotals and grand totals are computed from source rows using the same aggregation semantics, not from formatted strings or previously rounded cells.
• Sorting and totals must remain deterministic across browsers.

Filtering and execution

• Reuse the existing typed parameter pipeline for Pivot filters.
• Pivot filter state is owned by the Pivot session, not by a Dashboard.
• Validate complete proposed filter changes before mutation.
• Reserve execution generations before asynchronous preflight.
• Rapid filter/source/config changes must not allow stale results to replace newer state.
• Run the source query once per committed execution wave.
• Keep relative-time expressions and optional filter blocks consistent with Workbench and Dashboard behavior.
• Do not mutate the source saved query merely by viewing or filtering a Pivot Table.

A later phase may allow a Pivot Table to consume shared workspace filter presets, but it must not depend on Dashboard runtime state.

Presentation

The dedicated surface should support:

• sticky row and column headers;
• expandable/collapsible hierarchical row dimensions;
• optional row subtotals, column subtotals, and grand totals;
• multiple measures with a documented layout when column dimensions are also present;
• frozen dimension headers where practical;
• field formatting for labels, units, decimals, null text, and display names;
• conditional formatting, initially threshold and min-to-max scale modes;
• visible empty, invalid-config, truncated, broken-source, and duplicate-value states;
• responsive horizontal and vertical scrolling inside the Pivot workspace without page-level overflow;
• a compact configuration sidebar or drawer separate from the matrix viewport.

Because this is a full-page analytical surface, do not constrain the design to Dashboard tile dimensions.

Scale and execution limits

This is a browser-side pivot over a fetched result, not an unlimited in-memory BI engine.

• Define explicit maximum source rows, distinct row keys, distinct column keys, and rendered cells.
• Detect overflow before constructing an excessively large matrix.
• Surface the applicable limit and recommend pre-aggregation, stronger SQL filtering, or a narrower source query.
• Do not silently discard dimensions or cells.
• Keep transformation logic in a pure core module with deterministic tests.
• No new runtime dependency unless the focused in-repository implementation is demonstrably inadequate.

A later server-generated pivot/query-builder mode may be tracked separately. V1 operates on ordinary saved-query results.

Workspace operations

Support the same class of resource operations expected for Dashboard documents:

• New Pivot Table;
• Open;
• Save;
• Save As / Duplicate;
• Rename and edit description;
• Delete with confirmation;
• Export portable Pivot Table;
• Import with validation and id-conflict handling;
• Open read-only/view link;
• preserve references during full workspace export/import.

The existing Library/workspace format must gain a versioned Pivot Table collection or equivalent explicit resource section. Migration must preserve older workspaces unchanged.

Integration seams

• Selecting a Pivot cell should expose its raw row/column dimension context for future cross-filtering (SS: Add Dashboard cross-filtering from chart and table selections #392) and drill-to-detail (SS: Add drill-to-detail from Dashboard charts and tables #393).
• Cell inspection should reuse the existing cell-detail drawer where appropriate.
• Source-query editing remains in the Workbench; provide a clear Open source query action.
• Pivot Tables may later be embeddable in Dashboards through a separate snapshot/reference design, but embedding is not part of this issue and must not drive the V1 architecture.

Accessibility

• Use semantic table/grid structure where practical.
• Provide keyboard navigation across headers and cells.
• Announce expand/collapse state, totals, and conditional-format meaning without relying on color alone.
• Preserve readable text and contrast in light and dark themes.
• Provide full labels/tooltips for truncated headers.
• All authoring controls must have keyboard-accessible equivalents.

Tests

Cover:

Document and workspace

• Pivot document schema, canonicalization, generated types, and migrations;
• workspace save/load/export/import round-trip;
• broken source-query references;
• duplicate ids and import conflicts;
• editable and read-only route resolution;
• route rebuild and teardown without leaks.

Aggregation

• one and multiple row dimensions;
• one and multiple column dimensions;
• multiple measures;
• every aggregation operation;
• duplicate detection for value;
• null and empty combinations;
• row/column subtotals and grand totals;
• deterministic ordering;
• formatted labels versus raw grouping identity;
• conditional formatting;
• limits with no silent truncation.

Execution

• typed filters and optional blocks;
• relative-time values;
• exactly one source execution per committed change;
• validation failure performs no partial mutation;
• rapid reruns cannot publish stale results;
• source-query schema changes produce repair diagnostics.

UI and browser

• create/open/save/duplicate/delete flows;
• dedicated route/tab rather than panel rendering;
• CSV export with unambiguous flattened headers and raw values;
• keyboard navigation and accessible labels;
• narrow and large viewport behavior;
• light/dark themes;
• Chromium, Firefox, and WebKit.

Acceptance criteria

• Pivot Tables are first-class named workspace resources, separate from saved-query panels and Dashboards.
• A Pivot Table opens in its own top-level tab/route with editable and read-only modes.
• Pivot documents persist through workspace save/load/export/import with schema validation and migration support.
• Authors can select a saved query source and explicitly configure rows, columns, measures, filters, totals, and presentation.
• Raw-value grouping and aggregation are deterministic and tested.
• Subtotals and grand totals are correct for every supported aggregation.
• Existing typed parameters, optional blocks, and relative-time behavior are reused.
• Conditional formatting is available and accessible.
• Size limits fail visibly without silent data loss or page overflow.
• The visible Pivot Table can be exported to CSV.
• Route/session teardown prevents stale requests and resource leaks.
• Unit and real-browser tests pass.

Non-goals

• implementing Pivot Table as a Dashboard tile or saved-query panel;
• a full no-code dataset/query builder;
• arbitrary calculated metrics or semantic-layer joins;
• spreadsheet editing or writeback;
• unlimited client-side datasets;
• server-side pagination of a partially materialized matrix;
• Excel file generation;
• Dashboard embedding;
• cross-filtering and drill-to-detail implementation, tracked separately in SS: Add Dashboard cross-filtering from chart and table selections #392 and SS: Add drill-to-detail from Dashboard charts and tables #393.