Floemdata
BI & Reporting

Faster Reports with Pre-Aggregated Tables

A slow report often re-sums raw rows on every open. How pre-aggregated tables work, what freshness costs, and what Floemdata does and does not have today.

Floemdata · · 4 min read
Nine thin bars grouped by brackets into three wide blocks; the last block is only a dashed amber outline

If the monthly sales report re-adds 12 million rows every time it opens, the slowness usually comes not from the query but from the job itself: the same total is recalculated every morning, for every user. The fix is often not a bigger server but computing the total once and storing it. The row count in this post is a fictional example.

What a pre-aggregated table is

The raw table keeps every transaction row by row. A pre-aggregated (summary) table keeps one row at the level the report needs, for example per day × product group × region. The report queries this small table instead of the raw rows.

  • Raw table: stays for detail and for new questions; nothing is deleted.
  • Summary table: for frequent questions with a fixed breakdown; a field that is not in the breakdown cannot be asked.
  • Logic in one place: the totalling logic sits in the load step of the summary table; the report only reads.

The price: freshness

A summary table shows the data as of the moment it was loaded. A record that changes in the source during the day does not reach the figure until the next refresh. So every summary table should show two facts next to it: when it was last refreshed and up to which source date it contains data.

  • For records corrected after the fact (returns, cancellations, late invoices), recompute the last few days, not only today.
  • Pick the refresh frequency by how the report is used: daily is often enough for a meeting report.
  • Compare the summary total with the raw table's total at intervals; if they differ, the report should warn before it is used.

When you do not need it

  • If the raw data is small and the report opens in a few seconds, a summary table only adds maintenance.
  • If a different breakdown is wanted every day, a summary table keeps falling short; first measure which questions repeat.
  • If the figure must be real-time, a summary table is the wrong tool; the choice between live data and a cache is a separate topic.

Where Floemdata stands

In Floemdata's Gold layer the sales movement table is kept at line level, and report measures are calculated from it. We could not find a separate pre-aggregated summary table layer in the product documents, so we are not claiming that such a feature exists. The approach in this post is a general method you can apply to your own reports today.

To start, pick your slowest report, write down the breakdowns it is asked with, and try a summary table with one row per those breakdowns for a week. Questions are welcome at info@floemdata.com or via Floemdata.

← All guides