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.

Live Simulated Server Example

The example below uses the real HttpPivotRemoteStore API with a simulated in-memory /api/pivot/* server. The Request Log panel on the right shows the actual client and server events as they happen — load:started, load:received, load:responded, and load:succeeded.

Source code

Vue Example vue

<template>
  <div class="olap-demo">
    <div class="olap-demo__main">
      <p class="olap-demo__note">
        This live example uses <code>HttpPivotRemoteStore</code> plus a simulated <code>/api/pivot/*</code> server.
      </p>
      <RevoGrid
    class="grow h-full cell-border"
        hide-attribution
        range
        resize
        readonly
        :colSize="180"
        :source="[]"
        :pivot.prop="pivot"
        :additionalData="additionalData"
        :theme="isDark ? 'darkCompact' : 'compact'"
        :plugins="plugins"
        :column-types="columnTypes"
      />
    </div>

    <aside class="olap-demo__side">
      <div class="olap-demo__panel">
        <div class="olap-demo__panel-header">
          <strong>Request Log</strong>
          <button type="button" class="olap-demo__button olap-demo__button--ghost" @click="clearLog">
            Clear
          </button>
        </div>
        <ul class="olap-demo__log">
          <li v-for="entry in logs" :key="entry.id" class="olap-demo__log-item">
            <span class="olap-demo__badge" :data-layer="entry.layer">{{ entry.layer }}</span>
            <div>
              <div class="olap-demo__event">{{ entry.at }} · {{ entry.event }}</div>
              <div class="olap-demo__detail">{{ entry.detail }}</div>
            </div>
          </li>
        </ul>
      </div>
    </aside>
  </div>
</template>

<script setup lang="ts">
import { ref } from 'vue';
import RevoGrid from '@revolist/vue3-datagrid';
import NumberColumnType from '@revolist/revogrid-column-numeral';
import { PaginationPlugin } from '@revolist/revogrid-pro';
import { currentThemeVue } from '../composables/useRandomData';
import {
  createSimulatedOlapStore,
  PIVOT_OLAP_CONFIG,
  PIVOT_OLAP_FIELDS_VERSION,
  PIVOT_OLAP_VIEW_ID,
  type PivotOlapLogEntry,
} from '../sys-data/pivot.olap';
import { PIVOT_DRILLDOWN_PLUGINS } from '../sys-data/pivot.drilldown';

const { isDark } = currentThemeVue();

const logs = ref<PivotOlapLogEntry[]>([]);
const remoteStore = createSimulatedOlapStore({
  onLog(entry) {
    logs.value = [entry, ...logs.value].slice(0, 14);
  },
});

const columnTypes = ref({
  currency: new NumberColumnType('$0,0.00'),
});

const plugins = [...PIVOT_DRILLDOWN_PLUGINS, PaginationPlugin];
const pivot = {
  ...PIVOT_OLAP_CONFIG,
  engine: {
    mode: 'server' as const,
    remoteStore,
    viewId: PIVOT_OLAP_VIEW_ID,
    fieldsVersion: PIVOT_OLAP_FIELDS_VERSION,
    rowAxis: { offset: 0, expandedPaths: [['North']] },
    columnAxis: { offset: 0, limit: 8 },
  },
};
const additionalData = {
  pagination: {
    itemsPerPage: 3,
    initialPage: 0,
    total: 0,
  },
};

function clearLog() {
  logs.value = [];
}
</script>

<style scoped>
.olap-demo {
  display: flex;
  flex-direction: column;
  gap: 16px;
}

.olap-demo__main,
.olap-demo__panel {
  border: 1px solid var(--sl-color-gray-5);
  border-radius: 12px;
  background: var(--sl-color-bg);
  overflow: hidden;
}

.olap-demo__main {
  padding: 12px;
}

.olap-demo__note {
  font-size: 0.9rem;
  color: var(--sl-color-text-2);
  margin: 0 0 10px;
}

.olap-demo__side {
  display: flex;
  flex-direction: column;
}

.olap-demo__panel-header {
  display: flex;
  justify-content: space-between;
  align-items: center;
  gap: 12px;
  padding: 12px 12px 0;
}

.olap-demo__button {
  border: 1px solid var(--sl-color-accent);
  background: var(--sl-color-accent);
  color: white;
  border-radius: 8px;
  padding: 6px 10px;
  font-size: 0.85rem;
  cursor: pointer;
}

.olap-demo__button--ghost {
  background: transparent;
  color: var(--sl-color-accent);
}

.olap-demo__log {
  list-style: none;
  margin: 0;
  padding: 12px;
  display: flex;
  flex-direction: column;
  gap: 10px;
  max-height: 360px;
  overflow: auto;
}

.olap-demo__log-item {
  display: grid;
  grid-template-columns: auto 1fr;
  gap: 10px;
  align-items: start;
  font-size: 0.84rem;
}

.olap-demo__badge {
  display: inline-flex;
  align-items: center;
  justify-content: center;
  min-width: 54px;
  padding: 3px 8px;
  border-radius: 999px;
  font-size: 0.72rem;
  font-weight: 600;
  text-transform: uppercase;
  background: var(--sl-color-gray-6);
}

.olap-demo__badge[data-layer='server'] {
  background: #0f766e;
  color: white;
}

.olap-demo__badge[data-layer='client'] {
  background: #1d4ed8;
  color: white;
}

.olap-demo__event {
  font-weight: 600;
}

.olap-demo__detail {
  color: var(--sl-color-text-2);
}

</style>

Simulated Server ts

import {
  ClientPivotEngineAdapter,
  HttpPivotRemoteStore,
  type PivotDrilldownRequest,
  type PivotDrilldownResponse,
  type PivotLoadRequest,
  type PivotLoadResponse,
  type PivotRemoteStore,
  type PivotStateResponse,
} from '@revolist/revogrid-enterprise';
import type { DataType } from '@revolist/revogrid';
import {
  PIVOT_REMOTE_CONFIG,
  PIVOT_REMOTE_FIELDS_VERSION,
  PIVOT_REMOTE_ROWS,
  PIVOT_REMOTE_VIEW_ID,
} from './pivot.remote';

export {
  PIVOT_REMOTE_CONFIG as PIVOT_OLAP_CONFIG,
  PIVOT_REMOTE_FIELDS_VERSION as PIVOT_OLAP_FIELDS_VERSION,
  PIVOT_REMOTE_VIEW_ID as PIVOT_OLAP_VIEW_ID,
};

export interface PivotOlapLogEntry {
  id: string;
  at: string;
  layer: 'client' | 'server';
  event: string;
  detail: string;
  payload?: unknown;
}

interface PivotOlapOptions {
  onLog?: (entry: PivotOlapLogEntry) => void;
  rows?: DataType[];
}

type MockStateMap = Map<string, Record<string, unknown>>;

/**
 * Creates a live demo remote store backed by an in-memory `/api/pivot/*`
 * simulation so docs can show the real request flow without a real backend.
 */
export function createSimulatedOlapStore(
  options: PivotOlapOptions = {},
): PivotRemoteStore {
  const onLog = options.onLog;
  const rows = options.rows ?? PIVOT_REMOTE_ROWS;
  const fetchImpl = createSimulatedOlapFetch({
    rows,
    onLog,
  });

  return new HttpPivotRemoteStore({
    tenantId: 'portal-demo',
    datasetWatermark: 'portal-olap-v1',
    authProvider: async () => ({
      Authorization: 'Bearer portal-demo-token',
      'x-tenant-id': 'portal-demo',
    }),
    fetchImpl: fetchImpl as typeof fetch,
    hooks: {
      requestStarted(event) {
        emitLog(onLog, 'client', `${event.type}:started`, event.key, event);
      },
      requestSucceeded(event) {
        emitLog(
          onLog,
          'client',
          `${event.type}:succeeded`,
          event.cacheStatus ? `cache=${event.cacheStatus}` : 'ok',
          event,
        );
      },
      requestFailed(event) {
        emitLog(onLog, 'client', `${event.type}:failed`, 'request failed', event);
      },
      cacheStatusChanged(event) {
        emitLog(onLog, 'client', 'cache:status', event.cacheStatus, event);
      },
    },
  });
}

function createSimulatedOlapFetch(options: PivotOlapOptions) {
  const rows = options.rows ?? PIVOT_REMOTE_ROWS;
  const onLog = options.onLog;
  const state = new Map<string, Record<string, unknown>>() as MockStateMap;
  const adapter = new ClientPivotEngineAdapter(PIVOT_REMOTE_CONFIG, rows);

  return async (input: RequestInfo | URL, init?: RequestInit): Promise<Response> => {
    const url = typeof input === 'string'
      ? new URL(input, 'http://portal.local')
      : new URL(input.toString(), 'http://portal.local');
    const pathname = url.pathname;
    const method = init?.method ?? 'GET';

    if (pathname === '/api/pivot/load' && method === 'POST') {
      const request = JSON.parse(String(init?.body ?? '{}')) as PivotLoadRequest;
      emitLog(
        onLog,
        'server',
        'load:received',
        `${request.loadOptions.rows?.length ?? 0} row groups, ${request.loadOptions.columns?.length ?? 0} column groups`,
        request,
      );
      await delay(180);
      const response = await adapter.load(request);
      emitLog(
        onLog,
        'server',
        'load:responded',
        `${response.rowAxis.returned} rows, ${response.columnAxis.returned} columns in window`,
        response.meta,
      );
      return jsonResponse(response);
    }

    if (pathname === '/api/pivot/drilldown' && method === 'POST') {
      const request = JSON.parse(String(init?.body ?? '{}')) as PivotDrilldownRequest;
      emitLog(
        onLog,
        'server',
        'drilldown:received',
        `rowPath=${request.cell.rowPath.join(' / ') || 'root'}`,
        request,
      );
      await delay(120);
      const response = createMockDrilldownResponse(request, rows);
      emitLog(
        onLog,
        'server',
        'drilldown:responded',
        `${response.totalCount} fact rows matched`,
        response,
      );
      return jsonResponse(response);
    }

    if (pathname === '/api/pivot/state/save' && method === 'POST') {
      const request = JSON.parse(String(init?.body ?? '{}')) as {
        userId: string;
        viewId: string;
        state: Record<string, unknown>;
      };
      state.set(`${request.userId}:${request.viewId}`, request.state);
      emitLog(onLog, 'server', 'state:save', `${request.userId}/${request.viewId}`, request.state);
      return new Response(null, { status: 204 });
    }

    if (pathname.startsWith('/api/pivot/state/') && method === 'GET') {
      const [, , , userId, viewId] = pathname.split('/');
      const saved = state.get(`${userId}:${viewId}`) ?? { expanded: { North: true } };
      const response: PivotStateResponse = {
        userId,
        viewId,
        state: saved,
        version: 1,
      };
      emitLog(onLog, 'server', 'state:load', `${userId}/${viewId}`, response);
      return jsonResponse(response);
    }

    return jsonResponse({ message: 'Not found' }, 404);
  };
}

function createMockDrilldownResponse(
  request: PivotDrilldownRequest,
  rows: DataType[],
): PivotDrilldownResponse {
  const [region, rep] = request.cell.rowPath;
  const [year, quarter] = request.cell.columnPath;
  const filtered = rows.filter((row) => {
    return (region ? row.region === region : true)
      && (rep ? row.rep === rep : true)
      && (year ? row.year === year : true)
      && (quarter ? row.quarter === quarter : true);
  });

  const selectedColumns = request.customColumns?.length
    ? request.customColumns
    : ['region', 'rep', 'year', 'quarter', 'sales'];

  return {
    requestId: request.requestId,
    data: filtered
      .slice(request.offset, request.offset + request.limit)
      .map((row) => Object.fromEntries(selectedColumns.map((column) => [column, row[column]]))),
    totalCount: filtered.length,
    meta: {
      cacheStatus: 'bypass',
    },
  };
}

function emitLog(
  onLog: PivotOlapOptions['onLog'],
  layer: PivotOlapLogEntry['layer'],
  event: string,
  detail: string,
  payload?: unknown,
) {
  onLog?.({
    id: `${Date.now()}-${Math.random().toString(36).slice(2, 8)}`,
    at: new Date().toLocaleTimeString(),
    layer,
    event,
    detail,
    payload,
  });
}

function jsonResponse(body: unknown, status = 200) {
  return new Response(JSON.stringify(body), {
    status,
    headers: {
      'content-type': 'application/json',
    },
  });
}

function delay(ms: number) {
  return new Promise((resolve) => setTimeout(resolve, ms));
}

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,
  commonAggregators,
  type PivotConfig,
} from '@revolist/revogrid-enterprise';
import { PaginationPlugin } from '@revolist/revogrid-pro';

const remoteStore = new HttpPivotRemoteStore({
  baseUrl: '/api',
  tenantId: 'tenant-a',
  datasetWatermark: 'sales-cube-v42',
  authProvider: async () => ({
    Authorization: `Bearer ${await getAccessToken()}`,
  }),
});

const pivot: PivotConfig = {
  dimensions: [
    { prop: 'Region', name: 'Region' },
    { prop: 'Category', name: 'Category' },
    { prop: 'Date', name: 'Date' },
    {
      prop: 'SalesAmount',
      name: 'Sales',
      aggregators: commonAggregators,
    },
  ],
  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/revogrid-enterprise';

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 -> hierarchy expansion 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,
      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/revogrid-enterprise';

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