Data Modeling for Pivot

The quality of your Pivot analysis depends entirely on how your data is structured. To get the most out of RevoGrid Pivot, you should understand the difference between Facts and Dimensions.

Facts vs. Dimensions

In the world of data modeling (specifically the Star Schema), data is divided into two categories:

1. Fact Tables (The “Events”)

A Fact Table contains the quantitative data you want to measure. Each row represents a specific occurrence or transaction.

• Characteristics: Usually has many rows, contains numeric “measures,” and “foreign keys” to dimension tables.
• Example Fields: Price, Quantity, Duration, DiscountAmount.

2. Dimension Tables (The “Context”)

A Dimension Table contains descriptive attributes that provide context to your facts.

• Characteristics: Usually has fewer rows, contains categorical text or dates.
• Example Fields: Product Name, Color, Region, Customer Segment, Date (Year/Month/Day).

The “Perfect” Pivot Source

For RevoGrid’s client-side Pivot, the “perfect” input is a Denormalized Flat Table. This means you have “joined” your facts and dimensions into a single list of objects before passing them to the grid.

❌ Bad Data Shape (Normalized)

The grid has to look up values in multiple places. Hard to pivot.

// Transactions
[{ "id": 1, "product_id": 101, "amount": 50 }]
// Products
[{ "id": 101, "name": "Apple" }]

✅ Good Data Shape (Denormalized)

Everything the pivot needs is in one object.

[
  {
    "id": 1,
    "product": "Apple",
    "category": "Fruit",
    "region": "North",
    "amount": 50,
    "date": "2024-05-01"
  }
]

Modeling Best Practices

1. Numeric vs. Categorical

• Numbers should be raw, unformatted values (e.g., 1200.50, not "$1,200.50"). This allows aggregators like sum and avg to work correctly.
• Categories should be stable strings. Avoid using ID numbers (like 1, 2, 3) as labels; use human-readable names (like "Active", "Pending", "Closed") so the generated headers make sense.

2. Date Hierarchies

Dates are the most common pivot dimension. Instead of just a raw ISO string, it is often helpful to provide pre-calculated date parts if you are doing client-side pivoting:

{
  "orderDate": "2024-05-21",
  "year": 2024,
  "quarter": "Q2",
  "month": "May"
}

In your PivotConfig, you can then set rows: ['year', 'quarter', 'month'] to create an instant time-drilldown experience.

3. Stability of Fields

The prop in your Dimensions should be stable. If you change a field name in your data, you must update the prop in your configuration, or the Pivot will fail to find the data.

Large Scale Modeling (Server-Side)

If your “Fact Table” has millions of rows, you shouldn’t denormalize it into a single JSON array for the browser. Instead, you should keep it in an OLAP-optimized database (like ClickHouse, BigQuery, or Snowflake) and use the Server-Side Pivot mode.

In this mode, RevoGrid sends the “Analytical Request” (the rows, columns, and filters) to your backend, which performs the SQL JOIN and GROUP BY efficiently and returns only the visible summary.

Next Steps

Now that your data is modeled correctly, learn how to define your Dimensions to tell RevoGrid how to use these fields.