Skip to content
RevoGrid [Pro]

OLAP Best Practices

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

Section titled “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

Section titled “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

Section titled “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

Section titled “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

Section titled “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

Section titled “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

Section titled “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

Section titled “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 for the full breakdown of how these keys are constructed and why they matter for safety and performance.

Section titled “Recommended Implementation Pattern”

The most reliable production wiring is:

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 for a practical client + backend example.

What To Avoid

Section titled “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

Section titled “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.

Section titled “Related Guides”