Native Multidimensional Pivot

QueryOLAPAnalytics

Beta Works · surface still evolving · multi-clause PIVOT shipping; write-back evolving

What it is

Traditional databases pivot data by reading rows, hashing them by dimension, and writing temporary tables. That cost is what pushed organisations to ship copies of their data out to dedicated OLAP cubes for fast analyst access.

Aether-Pivot does the rotation natively. You declare a pivot view once and query it like a table: turning rows into columns, rolling up a hierarchy, drilling down, and writing aggregate changes back to base rows — all on the SQL surface, without an external OLAP server and without materialising an intermediate copy. Because pivoting is a way of addressing the existing data rather than reshaping it, the rotation itself is cheap.

Why it matters

Analysts get OLAP-class interactivity directly against operational tables: slice, dice, roll-up, drill-down, and write-back without leaving SQL. Forecasting and “what-if” workflows that once required a separate EPM stack collapse into a few statements against the database of record.

• Cheap rotation — pivoting reads the existing data a different way, not a copy.
• Roll-up and drill-down — move up and down a hierarchy in the same view.
• Write-back to base rows — updates to an aggregated cell distribute back to source rows under an allocation rule, with a full audit trail.

flowchart TB
  classDef src fill:#1f2640,stroke:#7c5cff,color:#e8eaf0
  classDef op  fill:#1f2640,stroke:#00d4ff,color:#e8eaf0
  classDef out fill:#1f2640,stroke:#4ade80,color:#4ade80

  R[Fact rows]:::src
  R --> P[Pivot view<br/>logical rotation]:::op
  P --> RU[Roll-up<br/>to parent levels]:::op
  RU --> D[Drill-down<br/>to children]:::op
  D --> A[Analyst result]:::out

How you use it

Declare a pivot view once and query it like a table. The view stores the rotation logic, not the data, so it costs nothing on disk and stays current with the underlying fact table.

-- Declare a pivot view that flips quarters into columns.
CREATE PIVOT VIEW sales_by_quarter AS
  SELECT region, product,
    SUM(revenue) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
  FROM sales;

-- Read it like any table.
SELECT * FROM sales_by_quarter WHERE region = 'EMEA';

-- Write-back: change an aggregate; the engine spreads the delta to base rows.
UPDATE sales_by_quarter
  SET Q1 = 1200000
  WHERE region = 'EMEA' AND product = 'SKU-7'
  USING ALLOCATION 'proportional';

If your dashboards keep asking for the same pivot shape, the engine can begin caching it in the background (see Horizon Materialization).

Key concepts

Term	What it means
Pivot view	A declared rotation you query like a table; it stores the logic, not a copy of the data.
Roll-up / drill-down	Aggregating up a hierarchy (City → State → Country) and descending back down.
Write-back allocation	A rule (proportional, equal, weighted, custom) that distributes an aggregate update across base rows.

Notes & caveats

Tip

Define pivot views to match your dashboards’ rotations. The planner uses them as both query targets and hints for materialisation — the more named views you declare, the more of your hot shapes the engine pre-builds.

Heads up

Write-back with a custom allocation rule produces base-row deltas that satisfy the aggregate constraint but may surprise downstream consumers expecting exact original values. Always document the allocation policy alongside the pivot view definition.

Horizon Materialization →

Promotes hot pivot rotations into auto-maintained views.

Deep RL Query Optimizer →

Learns which pivot shapes are worth materialising.

Storage Overview →

The coordinate-addressed storage format underneath.

SQL Grammar & Types →

The full SQL surface.