I'm working with a system that is very similar to a BI dashboard. Basically let's say the dashboard will show a couple of company's business metrics, for example, revenue, refunds, number of orders, average order value, etc.
On the front end, it will show one year's data, right now daily value will be shown on a line graph for one year. But later, it will start to allow user to select different aggregation options, like one years data will be aggregated by week, by month, etc (or it could be by 7 days, 14 days, etc, yes this is still unknown at this point). On the backend we are using a big data warehouse solution (sql), and a Node.js server
Now I'm considering 3 options, not sure which approach to go with. If you have some experience / insights to share, it will be really appreciated!
1) aggregation logic on backend, specifically the data layer, basically does the aggregation in sql queries.
pro: 1)fast 2)scales well if data size grows (say we start to show 3 years data, more metrics)
con: 1)if the query aggregation logic changes (like from calendar month/week to rolling x days), you might end up re-write most queries (might not be true, if so pls point out). 2) Require more work to setup solid test.
2) aggregation logic on backend, specifically the application layer. basically the query will return daily data points, and application handles aggregation logic.
pro: 1)easier to change if the aggregation logic changes (relatively)
con: 1)slower than having this in data layer (more network traffic, language performance diff, more load on server) 2)scales worse compared with the data layer approach
3) aggregation logic on frontend, most charts libraries allows support different aggregation scenarios. Basically api returns all daily data points.
pro: 1)very flexible if the aggregation logic changes.
con: 1)slow (network traffic, browser engine, we also support mobile, so it could be very bad on mobile) 2)scales the worts
Edit: no real-time requirement. Right now the data needed for display charts is around 365 data points for each metric (basically a timestamp and a value pair) and there are around 20 metrics.
