# Connect An OLAP Backend
URL: https://pro.rv-grid.com/guides/pivot/server-side/connect-olap-backend/
Source: src/content/docs/guides/pivot/server-side/connect-olap-backend.mdx
Description: Wire RevoGrid Pivot to an OLAP-capable backend through HttpPivotRemoteStore and an application API.
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](/guides/pivot/server-side/) first. For the transport contract, see [Remote API Reference](/guides/pivot/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`.

## Architecture

The recommended production boundary looks like this:

```txt
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.

```ts
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.

```ts
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:

```ts
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.

```ts
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](/guides/pivot/server-side/)
- [Remote API Reference](/guides/pivot/remote-api-reference/)
- [OLAP Best Practices](/guides/pivot/olap-best-practices/)
