I work with a system that is very similar to a BI dashboard. For example, suppose the dashboard shows some of the company's business metrics, such as B. Sales, Refund, Number of Orders, Average Order Value, etc.
The data for one year is displayed on the front end. The daily value for one year is currently displayed in a line chart. However, later the user will be able to select various aggregation options, e.g. For example, data is aggregated by year, week, month, etc. (or after 7 days, 14 days, etc.). Yes, this is not yet known point). In the backend we use a Big Data Warehouse solution (SQL) and a Node.js server
Now I'm thinking about 3 options and I'm not sure which approach to take. If you want to share some experiences / insights, this will be greatly appreciated!
1) The aggregation logic in the backend, especially the data layer, basically performs the aggregation in SQL queries.
pro: 1) fast 2) scales well as the data size grows (let's say we show 3 year data, more metrics)
con: 1) If the logic of query aggregation changes (e.g., from calendar month / week to consecutive x days), most queries may be rewritten (may not be true if this is the case). 2) Need more work to set up a solid test.
2) Aggregation logic in the backend, especially on the application layer. Basically, the query returns daily data points and the application processes the aggregation logic.
pro: 1) easier to change if the aggregation logic changes (relatively)
con: 1) slower than this in the data layer (more network traffic, voice performance difference, more load on the server) 2) worse scaled compared to the data layer approach
3) Aggregation logic in the frontend, most diagram libraries support various 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 can be very bad on mobile) 2) scales the wort