Statistics and filters: server-side aggregation and query limits

The number that stopped moving. Picture a storm night at an electric utility. The operations center has an outage dashboard on the big wall, and the indicator reads two thousand active outages. Twenty minutes later it still says two thousand. An hour later it's the same number, while crews report new outages every few minutes. The storm didn't stop. The dashboard did. The element was loading raw features into the browser, and the service only hands out two thousand of them per request. The real count was about three thousand four hundred. Nobody noticed, because a flat number looks like good news. This lesson is about where your dashboard numbers are calculated, and how to make sure they're complete.

What you will learn. Here's the plan for this lesson. First, you'll see the difference between a number the server calculates and a number your browser adds up from downloaded features, and why that difference matters. Second, you'll learn how statistics like count, sum and average work together with filters, so the feature service does the heavy lifting and sends back one small answer. Third, you'll learn about query limits: the maximum number of features a service returns at once, how to tell when you've hit it, and how to design your elements so you never depend on it. By the end you'll be able to look at any indicator or chart and say whether you can trust it.

Server-side aggregation. Aggregation just means rolling many rows up into a few numbers, the way a cashier totals a receipt instead of reading you every item. Server-side aggregation means the feature service does that rolling up. When an indicator is set to show a statistic, the dashboard sends the service a query with two parts: a filter saying which rows count, and a statistic saying what to compute. The service scans every matching row in its database, calculates the answer, and sends back a tiny response, often a single number. That's why a statistic stays fast whether the layer holds five hundred water main breaks or five million meter readings. The browser never has to download the individual features at all.

The statistic types. Dashboards offer the same handful of statistics in indicators, charts and tables. Count tells you how many rows match, like the number of open service requests. Sum adds up a field, such as customers without power across all outages. Average is the sum divided by the count, so it gives a typical value, like the average days a pothole ticket stays open. Min and max give the extremes, for example the oldest unresolved traffic incident. Standard deviation sounds scary but it simply measures spread. Think of two crews that both average four hours per repair: one always takes about four, the other swings between one and ten. The second crew has the larger standard deviation.

Worked example: grouping requests. Let's watch what the server does for a serial chart set to grouped values. Here are eight service requests from a city layer. The chart's settings become the statistic query you see typed here, the same idea in SQL form. First the filter runs, so the closed tickets drop out and six open requests remain. Then the service builds one row per request type, counting them and averaging the days open. The order puts the busiest first. What comes back to the dashboard is just three short rows: water main with three requests averaging six days, pothole with two averaging three days, and streetlight with one averaging nine days. The chart draws the counts as three bars, and the averages can ride along as a second series or a table column, no matter how big the layer is.

Filters travel to the server too. Filters are the other half of the query. In an element's data options you can add a filter, such as status equals open, or reported date in the last twenty four hours. That condition becomes part of the where clause the service runs, the part of the query that says which rows count. When a user picks a district in a category selector, or clicks a chart that has a filter action, the dashboard adds another condition and sends a new query. Here's the nice part: every extra condition makes the server's job smaller, so filtering is almost free for your users. For busy layers, filter on fields the owner has indexed, such as status or date, because an index lets the database jump straight to matching rows instead of reading everything.

Query limits in action. Now the trap from the storm night. Every feature service has a max record count, the most features it will return in one request, and for hosted layers that's often two thousand. This little Python sketch mimics it with three thousand four hundred outages. A statistic query counts every row on the server, so a single total returns the full number. One nuance: a grouped statistic with thousands of categories can still be cut at the limit, so keep the number of groups sensible. A request for raw features stops at two thousand, and the real service marks that response with a flag called exceeded transfer limit set to true. If an element adds up downloaded features instead of asking for a statistic, fourteen hundred outages simply vanish from the total, and the display gives no warning at all.

Statistic mode versus feature mode. So how do you choose in the dashboard editor? For an indicator showing a total, pick the statistic value type, not feature. Feature mode reads a single feature's field and is meant for things like the latest reservoir level. For charts and tables that summarize, choose grouped values, so each category is computed on the server. Lists and details elements do show individual features, which is fine when a filter keeps them small, such as the twenty most recent traffic incidents sorted by time. A simple rule works almost everywhere: let the server summarize, and only list features when a person actually needs to read them one by one.

Data expressions and limits. Data expressions, which you met in the previous lesson, add one more layer to think about. An Arcade data expression builds a FeatureSet, a table of results, inside the dashboard. Functions like Filter and GroupBy can usually be sent to the service as queries, so the heavy work stays on the server. A loop that visits every feature one at a time can't, and on a large layer it's slow or runs into the same limits. So what should you do first in an expression? Filter and group as early as possible, then loop over the small result. And always test with your biggest realistic layer, like a full season of storm damage reports.

Common mistake: the silent cap. The most common mistake is a total that quietly depends on downloaded features. It looks perfect in testing, because the test layer has three hundred records, and then it freezes in production at exactly one thousand or two thousand. That round number is your clue. The fix is almost always to switch the element to a statistic or to grouped values. To check, open the layer's item page or data table and compare its record count with your dashboard number under the same filter. If they differ, something is being cut. Make this comparison part of your release checklist, especially before storm season or any major incident.

Recap. Let's pull it together. First, when an element uses a statistic with a filter, the feature service calculates the answer over every matching row and sends back something tiny, which is fast and complete. Second, requests for raw features stop at the max record count, often two thousand, and a total built from them can freeze silently, as it did on our storm night. Third, the working rule: summarize on the server with statistics and grouped values, filter early in data expressions, and only list individual features when a filter keeps them few. Follow that and your executives can trust the number on the wall.

Your exercise. Before the next lesson, audit one real dashboard. It can be your own or a copy of a public one built on a Living Atlas layer. List every indicator, chart and table, and for each one note whether it uses a statistic, grouped values or individual features. Then pick two totals and compare them with the layer's record count under the same filter. If any element depends on features and could grow past the max record count, switch it to a statistic and write down the before and after numbers. Next time we'll use those elements to design for readability: color, hierarchy and number formats for executives.