# OLAP Best Practices
URL: https://pro.rv-grid.com/guides/pivot/server-side/olap-best-practices/
Source: src/content/docs/guides/pivot/server-side/olap-best-practices.mdx
Description: Design Pivot-friendly analytical models with stable dimensions, secure field registries, and viewport-aware execution.
RevoGrid Pivot is not itself an OLAP database, but the server-side foundation is designed to work well with OLAP-style engines, semantic layers, and warehouse-backed pivot services. This page explains the practices that keep that integration correct and maintainable.

## Start With Facts And Dimensions

The best Pivot source model is:

- a fact table with additive events or transactions
- dimensions that describe those facts

Examples:

- facts: orders, sales lines, support incidents, page views
- dimensions: region, product, customer segment, time

This model keeps grouping and drilldown semantics predictable.

## Treat The Field Registry As A Semantic Layer

Your client should use public field ids. Your backend should map them through a field registry.

Good registry properties:

- stable field ids
- business-friendly labels
- safe backend expressions
- explicit allowed operations
- explicit allowed summaries
- explicit allowed grouping intervals

This prevents raw selector leakage and becomes the trust boundary for every analytical request.

## Design Hierarchies Intentionally

Rows and columns should represent real analytical hierarchies, not arbitrary field lists.

Good examples:

- `['Region', 'Rep']`
- `['Year', 'Quarter', 'Month']`
- `['Category', 'Subcategory']`

Poor examples:

- mixing unrelated dimensions only because the UI allows it
- saving unstable intermediate fields that are likely to change name or meaning

## Prefer Additive Measures

`sum` and `count` are easiest to reason about across subtotals and grand totals.

Be careful with:

- `avg`
- ratios
- percentages
- any metric derived from already-aggregated cells

For those, totals should come from base facts or planner-managed derived summaries, not by averaging visible children.

## Separate UI State From Analytical State

Keep these concerns separate:

- analytical definition: fields, filters, summaries, sort
- UI state: expanded paths, collapsed columns, viewport windows

This separation makes saved state more stable and keeps backends from mixing presentation-only state with semantic query meaning.

## Return Windows, Not The Whole Cube

For large datasets, the backend should return:

- only the visible row-axis window
- only the visible column-axis window
- only the summaries needed for the visible analytical block

Returning the full cube defeats the whole purpose of server-side Pivot.

## Drilldown Should Mean Facts

When users drill into a summary cell, the backend should return the underlying facts scoped by:

- the current filters
- the row path
- the column path
- the selected measure if needed

That keeps drilldown trustworthy and consistent with visible summaries.

## Keep Cache Keys Semantic

Stable, semantic cache keys are the difference between a useful analytical cache and an unsafe one. Because Pivot requests are complex, your backend must identify identical windows deterministically.

Good Pivot cache keys should include:

- tenant and view identifiers
- field registry versions
- dataset watermarks (freshness)
- normalized filters, groupings, and summaries
- viewport windows

See [Caching and Cache Keys](/guides/pivot/caching/) for the full breakdown of how these keys are constructed and why they matter for safety and performance.

## Recommended Implementation Pattern

The most reliable production wiring is:

```txt
browser Pivot UI
  -> HttpPivotRemoteStore
  -> application API
  -> field registry + normalization + planner/adapter
  -> OLAP cube / warehouse
```

This keeps the public Pivot model stable while allowing your backend to translate the same request into SQL, a semantic-layer API, or a cube-native query format.

Use [Connect An OLAP Backend](/guides/pivot/connect-olap-backend/) for a practical client + backend example.

## What To Avoid

- exposing backend column names to the client
- concatenating raw selectors into SQL
- computing totals from already-rendered UI cells
- using row indexes as expansion identifiers
- coupling the grid to a specific backend framework

## Current Product Position

Today, RevoGrid Pivot already provides:

- a shared client/server config model
- a field registry and normalization layer
- a planner abstraction
- a transport-agnostic remote store
- visible-window materialization into RevoGrid

What it does not claim to be:

- a full OLAP engine
- a warehouse execution service
- a finished large-scale remote virtualization system on its own

That distinction is important. RevoGrid should remain the UI and viewport layer, while the analytical engine remains replaceable.

## Related Guides

- [Server-Side Pivot](/guides/pivot/server-side/)
- [Connect An OLAP Backend](/guides/pivot/connect-olap-backend/)
- [Remote API Reference](/guides/pivot/remote-api-reference/)
- [Configuration Reference](/guides/pivot/configuration-reference/)
