Most sales dashboards I have worked on send every question to a server. You change a filter, a request goes out, a SQL query runs and a page of rows comes back. That is the right design when the data is huge or private. It is also a lot of machinery for a question like "which country bought the most in November?" when the whole dataset would fit in a browser tab.
So we tried it the other way round. We took a real sales dataset of just over a million rows, sent it to the browser once, and did everything else there: sorting, searching, grouping, pivoting and charting. This post walks through each part with the React code, and says where it gets slow.
The data
We used Online Retail II from the UCI Machine Learning Repository. It is two years of transactions from a UK online retailer that sells gifts and homeware, from December 2009 to December 2011: 1,067,371 rows, one per invoice line. Each row has an invoice number, a stock code, a product description, a quantity, a unit price in pounds, a timestamp, a customer ID and a country. It is published under CC BY 4.0, so you can build on it too.
It is real data, so it is untidy in the way sales data always is. Cancelled invoices start with a "C" and carry negative quantities. Some lines are not products at all: "Manual", "AMAZON FEE", "Adjust bad debt". We kept every row.
The finished page is live at /demos/online-retail. It has two parts, a data grid with every transaction and a pivot table with a chart, and we will build them in that order.
Getting a million rows into the browser
As a CSV the dataset is 91 MB, and 14.8 MB gzipped. That is too much to ask a visitor to download, but most of it is repetition. Five of the eight columns draw from a small set of values: 43 countries, about 5,300 products and about 53,600 invoices. We ship each of those columns as a dictionary plus one integer per row, which brings the download to 5.8 MB gzipped.
Decoding it back into plain row objects is a loop:
const res = await fetch('/demos/online-retail/retail.v1.json.gz');
const text = await new Response(
res.body!.pipeThrough(new DecompressionStream('gzip')),
).text();
const { dicts, cols, rows: n } = JSON.parse(text);
const rows = new Array(n);
for (let i = 0; i < n; i++) {
const quantity = cols.quantity[i];
const price = cols.price[i];
rows[i] = {
id: i + 1,
invoice: dicts.invoice[cols.invoice[i]],
description: dicts.description[cols.description[i]],
country: dicts.country[cols.country[i]],
quantity,
price,
revenue: quantity * price,
};
}
The version on the page decodes in slices of 50,000 rows and yields to the browser between slices, so the progress bar keeps moving while a million objects are built.
The grid
The grid takes those rows as they are:
import { DataGrid } from '@kanunilabs/datagrid-react';
const gbp = new Intl.NumberFormat('en-GB', { style: 'currency', currency: 'GBP' });
const columns = [
{ field: 'invoice', headerName: 'Invoice' },
{ field: 'description', headerName: 'Product', width: 300 },
{ field: 'quantity', headerName: 'Qty', dataType: 'number' },
{ field: 'revenue', headerName: 'Revenue', dataType: 'number',
valueFormatter: (v) => gbp.format(v) },
{ field: 'country', headerName: 'Country' },
];
<DataGrid
dataSource={rows}
rowKey="id"
columns={columns}
toolbar
statusBar
groupPanel
summaries={[
{ columnId: 'quantity', type: 'sum' },
{ columnId: 'revenue', type: 'sum' },
]}
/>
Only the rows on screen are in the DOM, so scrolling to the bottom of a million rows is the same work as scrolling to the top. The footer shows the totals for the whole result: 10,608,492 units and £19,287,250.55 over the two years.
Sorting by revenue
Click the Revenue header. The sort runs in a Web Worker, so the page keeps responding while a million rows are reordered. Our benchmarks page has the measured times on a million generated rows, and a script to run them yourself.
The top line is a single order for 80,995 paper craft birds at £2.08 each, £168,469.60 in one row. Sort the other way and the first row is the same order again, cancelled the same day as invoice C581484 at −£168,469.60. The second-largest line, 74,215 ceramic storage jars, has a matching cancellation too. Nobody would plan a dashboard around that, but it is the first thing a sort shows you on real data.
Searching
Type into the search box and the grid filters every column at once and highlights the match. "WHITE HANGING HEART" matches 5,918 of the 1,067,371 rows, and the footer updates to that result: 93,050 units and £257,533.90 in revenue.
The search matches what you see, so a search for "£2.95" works as well as a search for "2.95". That means the grid formats every row of every formatted column once, the first time you search, and this is where one line of our own code cost us a minute.
We first wrote the formatter as v.toLocaleString('en-GB', { style: 'currency', currency: 'GBP' }). With options, toLocaleString builds a new Intl.NumberFormat on every call, so the first search sat on "Preparing data" for over a minute in a development build. With one shared formatter, as in the code above, it took about six seconds on the same laptop. If you format numbers in a large grid, build the formatter once.
Grouping by country
Drag the Country header into the bar above the grid and the rows fold into one group per country, each with its own row count and totals.
Grouping, the group bar and the footer totals are in the free Community package. The per-group totals shown here are part of the paid EnterpriseDataGrid, which this page uses.
A pivot of country by year
A pivot table answers the next question, how each market did in each year, without writing a query. Three fields describe it:
import { BasePivotGrid as PivotGrid } from '@kanunilabs/pivotgrid-react';
const fields = [
{ id: 'country', dataField: 'country', caption: 'Country',
dataType: 'string', area: 'row', areaIndex: 0 },
{ id: 'year', dataField: 'year', caption: 'Year',
dataType: 'string', area: 'column', areaIndex: 0 },
{ id: 'revenue', dataField: 'revenue', caption: 'Revenue',
dataType: 'number', area: 'data', areaIndex: 0, summaryType: 'sum' },
];
<div style={{ position: 'relative', height: 520 }}>
<PivotGrid data={rows} initialFields={fields} />
</div>
Note the wrapper. The pivot lays itself out against its nearest positioned ancestor, so give it a container with a height and position: relative. On our page, without it, the pivot drew itself over the grid above.
Every field in the pivot can be dragged between the rows, columns, values and filter areas, and it re-aggregates the million rows on each drop.
Revenue by month
Swap the layout for months in the rows and revenue as the only measure, and the chart becomes readable. With countries in the rows it is not: the UK accounts for most of the revenue, so every other bar is a sliver.
November is the peak in both years, £1,422,655 in 2010 and £1,461,756 in 2011. December 2011 looks like a collapse at £433,686, but the dataset stops on 9 December. That is worth checking before anyone builds a forecast on it.
The chart follows the pivot, so filtering the pivot to one country redraws the chart for that country. Charts are part of the paid PivotGrid Enterprise package.
A dark theme
The grid ships six themes. This one is theme="kanuni-midnight":
What this approach costs
Sending the whole dataset to the browser is a trade, and it is not the right one for every dashboard.
- The download. 5.8 MB, once per visit. Fine for an internal tool, heavy for a phone on a bad connection.
- Memory. A million row objects live in the tab for as long as it is open. We did not measure the footprint for this post.
- First-time costs. The first search indexes the formatted text, and the first pivot layout aggregates everything. Both are cached afterwards, but the first time is seconds rather than milliseconds.
- It is all in one tab. If the data is private, changes every second, or is ten times bigger, a server is still the right place for it.
Try it
The dashboard is at /demos/online-retail. A few links open it in a given state: sorted by revenue, grouped by country, with the monthly chart and in the dark theme. The DataGrid docs and PivotGrid docs cover every prop used here.
Data: Online Retail II, UCI Machine Learning Repository (Chen, 2019), CC BY 4.0.
Top comments (5)
i like that this doesn't just focus on the "1 million rows" part, but also shows where the real bottlenecks appear. the
Intl.NumberFormatexample is a good one too, because small implementation details can become surprisingly expensive at this scale.nice write-up and a useful real-world example.
thanks
how did you find the memory usage once the million rows were decoded into objects? i'm curious whether the browser memory footprint became the main limitation before sorting, grouping or pivoting did.
Good question. We hadn't measured it when we wrote the post, so we did now, on the live demo in desktop Chrome, forcing garbage collection before each reading:
So about 350 MB on top of an empty page once everything has been used once. On a desktop, memory wasn't what we hit first; the first-time costs were, mostly the -6 s the first search spends building its index. On a phone I'd expect memory to become the limit sooner, but we haven't tested one, so I won't guess a number.
the trade offs around download size, memory and first time processing make the approach much clearer. good example of where client side processing can make sense.