Connect An OLAP Backend

This guide shows the practical wiring pattern for connecting RevoGrid Pivot to an OLAP-capable backend. The important rule is simple: the browser talks to your application API, and your application API talks to the cube, warehouse, or semantic layer.

For the conceptual background, read Server-Side Pivot first. For the transport contract, see Remote API Reference.

Use aggregationSemantics: 'server' to pin plugin/controller execution to the trusted backend. A pin prevents accidental engine switching; it does not guarantee identical local/server results. Choose null, count, distinct, totals and precision policies in the backend contract and validate them against independent facts. For local analysis, use the V2 numeric/scalar aggregation configuration.

Live Simulated Server Example

Interactive example

Simulated OLAP integration

Use simulated load/drill plus paging and totals using an in-memory server

The example loads when visible.

Reset

The focused cases above exercise the public load and fact-drill contracts over deterministic in-memory facts. Inspect the request and response details to see the simulated server boundary. The HTTP route and lifecycle examples appear in Remote API Reference and Remote Tools.

Open the full demo and framework source.

Architecture

The recommended production boundary looks like this:

RevoGrid + PivotPlugin
  -> HttpPivotRemoteStore
  -> your /api/pivot/* endpoints
  -> field registry + validation + planner/adapter
  -> OLAP engine / warehouse / cube API

Do not connect the browser directly to the OLAP database. Your backend layer must own:

• authentication
• tenant isolation
• selector whitelisting
• request normalization
• query translation
• caching and observability

Minimum Pieces You Need

1. A client PivotConfig with engine.mode = 'server'
2. An HttpPivotRemoteStore
3. Backend POST /api/pivot/load
4. Backend POST /api/pivot/drilldown
5. A field registry for safe public field ids
6. A planner or adapter that translates Pivot requests into your backend’s OLAP query shape

Client Setup

The client stays small. It only needs to describe the Pivot layout and point the plugin at your application API.

import {
  HttpPivotRemoteStore,
  PivotPlugin,
  type PivotConfig,
} from '@revolist/pivot';
import { PaginationPlugin } from '@revolist/revogrid-pro';

const remoteStore = new HttpPivotRemoteStore({
  baseUrl: '', // Same-origin /api/pivot/* routes are already included by the store.
  tenantId: 'tenant-a',
  datasetWatermark: 'sales-cube-v42',
  authProvider: async () => ({
    Authorization: `Bearer ${await getAccessToken()}`,
  }),
});

const pivot: PivotConfig = {
  aggregationSemantics: 'server',
  dimensions: [
    { prop: 'Region', name: 'Region' },
    { prop: 'Category', name: 'Category' },
    { prop: 'Date', name: 'Date' },
    {
      prop: 'SalesAmount',
      name: 'Sales',
    },
  ],
  rows: ['Region', 'Category'],
  columns: ['Date'],
  values: [{ prop: 'SalesAmount', aggregator: 'sum' }],
  totals: { grandTotal: true, subtotals: true },
  engine: {
    mode: 'server',
    remoteStore,
    viewId: 'sales-by-region',
    fieldsVersion: 'sha256:fields-v1',
    rowAxis: { offset: 0 },       // limit driven by pagination.itemsPerPage
    columnAxis: { offset: 0, limit: 24 },
  },
};

// Add PaginationPlugin to enable server-side row paging.
// PivotPlugin will read itemsPerPage as the rowAxis limit and keep
// pagination.total in sync with response.rowAxis.totalCount.
grid.plugins = [PivotPlugin, PaginationPlugin];
grid.pivot = pivot;
grid.additionalData = {
  pagination: {
    itemsPerPage: 50,
    initialPage: 0,
    total: 0,
  },
};

What The Client Sends

At runtime, PivotPlugin converts the visible Pivot state into a PivotLoadRequest and sends it through HttpPivotRemoteStore.

That request includes:

• rows, columns, and values
• filters and sort directives
• rowAxis and columnAxis windows
• expansion state represented as analytical paths

The client does not build SQL and it does not know how your cube works internally.

Backend Load Endpoint

This example uses a lightweight Node/Express style handler for readability. The contract itself is framework-agnostic.

import type { Request, Response } from 'express';
import {
  createFieldRegistry,
  normalizePivotLoadRequest,
  type PivotLoadRequest,
  type PivotLoadResponse,
} from '@revolist/pivot';

const registry = createFieldRegistry([
  {
    id: 'Region',
    label: 'Region',
    dataType: 'string',
    expression: { kind: 'column', value: 'sales.region' },
    allowedOperations: ['=', '<>', 'in'],
  },
  {
    id: 'Category',
    label: 'Category',
    dataType: 'string',
    expression: { kind: 'column', value: 'sales.category' },
    allowedOperations: ['=', '<>', 'in'],
  },
  {
    id: 'Date',
    label: 'Date',
    dataType: 'date',
    expression: { kind: 'column', value: 'sales.order_date' },
    allowedOperations: ['=', '>=', '<='],
    allowedGroupIntervals: ['year', 'quarter', 'month', 'day'],
  },
  {
    id: 'SalesAmount',
    label: 'Sales Amount',
    dataType: 'number',
    expression: { kind: 'column', value: 'sales.amount' },
    allowedOperations: ['=', '>', '>=', '<', '<='],
    allowedSummaries: ['sum', 'avg', 'min', 'max', 'count'],
    drilldownVisible: true,
  },
]);

export async function postPivotLoad(req: Request, res: Response) {
  const tenantId = await requireTenant(req);
  const request = req.body as PivotLoadRequest;

  const normalized = normalizePivotLoadRequest(request, registry);
  const cubeResult = await runCubeLoad({
    tenantId,
    viewId: normalized.viewId,
    fieldsVersion: normalized.fieldsVersion,
    request: normalized,
  });

  const response: PivotLoadResponse = {
    requestId: request.requestId,
    version: 1,
    data: cubeResult.data,
    summary: cubeResult.summary,
    rowAxis: cubeResult.rowAxis,
    columnAxis: cubeResult.columnAxis,
    meta: cubeResult.meta,
  };

  res.json(response);
}

Translating The Pivot Request Into An OLAP Query

Your adapter layer is where Pivot concepts become backend-native OLAP concepts.

Recommended mapping:

• Pivot rows -> cube row dimensions
• Pivot columns -> cube column dimensions
• Pivot totalSummary and groupSummary -> cube measures
• Pivot filter -> cube filter tree
• expandedPaths / collapsedPaths -> hierarchy frontier exceptions
• uiState.columnGroupsCollapsedByDefault -> column hierarchy default state
• rowAxis / columnAxis -> returned analytical windows

Example adapter:

type RunCubeLoadArgs = {
  tenantId: string;
  viewId: string;
  fieldsVersion: string;
  request: ReturnType<typeof normalizePivotLoadRequest>;
};

async function runCubeLoad(args: RunCubeLoadArgs) {
  const { request } = args;

  const cubeQuery = {
    dimensions: {
      rows: (request.loadOptions.rows ?? []).map((item) => ({
        id: item.selector,
        interval: item.groupInterval,
        desc: item.desc,
      })),
      columns: (request.loadOptions.columns ?? []).map((item) => ({
        id: item.selector,
        interval: item.groupInterval,
        desc: item.desc,
      })),
    },
    measures: (request.loadOptions.totalSummary ?? []).map((summary) => ({
      id: summary.selector,
      aggregate: summary.summaryType,
    })),
    filters: request.loadOptions.filter,
    sort: request.loadOptions.sort ?? [],
    window: request.viewport,
    uiState: request.uiState,
  };

  const result = await cubeClient.loadPivotWindow(cubeQuery);

  return {
    data: result.cells,
    summary: result.summaryRows,
    rowAxis: {
      totalCount: result.rowAxis.totalCount,
      returned: result.rowAxis.returned,
      offset: request.viewport.rowAxis.offset,
      limit: request.viewport.rowAxis.limit,
      expandedPaths: request.viewport.rowAxis.expandedPaths,
      paths: result.rowAxis.paths,
      nodes: result.rowAxis.nodes,
    },
    columnAxis: {
      totalCount: result.columnAxis.totalCount,
      returned: result.columnAxis.returned,
      offset: request.viewport.columnAxis.offset,
      limit: request.viewport.columnAxis.limit,
      expandedPaths: request.viewport.columnAxis.expandedPaths,
      collapsedPaths: request.viewport.columnAxis.collapsedPaths,
      paths: result.columnAxis.paths,
      nodes: result.columnAxis.nodes,
    },
    meta: {
      cacheKey: result.cacheKey,
      generatedAt: result.generatedAt,
      elapsedMs: result.elapsedMs,
      cacheStatus: result.cacheStatus,
      warnings: result.warnings ?? [],
    },
  };
}

Drilldown Endpoint

Drilldown should return the underlying facts for a visible summary cell, not a second aggregated result.

import type { Request, Response } from 'express';
import {
  createFieldRegistry,
  normalizePivotDrilldownRequest,
  type PivotDrilldownRequest,
} from '@revolist/pivot';

export async function postPivotDrilldown(req: Request, res: Response) {
  await requireTenant(req);
  const request = req.body as PivotDrilldownRequest;
  const normalized = normalizePivotDrilldownRequest(request, registry);

  const facts = await cubeClient.drilldown({
    rowPath: normalized.cell.rowPath,
    columnPath: normalized.cell.columnPath,
    dataIndex: normalized.cell.dataIndex,
    fields: normalized.customColumns ?? ['Region', 'Category', 'Date', 'SalesAmount'],
    offset: normalized.offset,
    limit: normalized.limit,
  });

  res.json({
    requestId: request.requestId,
    data: facts.rows,
    totalCount: facts.totalCount,
    meta: {
      cacheStatus: facts.cacheStatus ?? 'bypass',
    },
  });
}

Request Lifecycle

1. The grid shows a Pivot layout with row and column windows.
2. PivotPlugin builds a PivotLoadRequest.
3. HttpPivotRemoteStore sends the request to your application API.
4. Your backend authenticates, validates selectors, and normalizes the request.
5. Your planner or cube adapter translates the normalized request into backend-native OLAP calls.
6. The cube or warehouse returns only the visible analytical block.
7. Your backend maps that result into PivotLoadResponse.
8. RevoGrid materializes the returned window into visible columns and rows.

What Changes For A True OLAP Cube

If your backend already exposes dimensions, measures, and drilldown concepts, the frontend setup stays the same. Only the backend adapter changes.

Typical differences:

• date grouping may map directly to cube hierarchy levels
• drilldown may map to cube “drill through” APIs
• row and column windows may map to axis slicing instead of SQL paging
• avg and derived measures may already be modeled by the cube

That is why the recommended contract stays generic HTTP. The transport is stable even when the backend engine changes.

Do Not Do This

• Do not connect the browser directly to the OLAP database.
• Do not trust selectors from the client.
• Do not return the full cube by default.
• Do not compute totals from rendered UI values.

Related Guides

• Server-Side Pivot
• Remote API Reference
• OLAP Best Practices