I work with a system very similar to a BI dashboard. Basically, suppose the dashboard shows some business metrics for the business, for example, revenue, refunds, number of orders, average order value, etc.
At the front it will display the data for one year, at this time the daily value will be displayed on a line graph for one year. But later, this will start to allow the user to select different aggregation options, as one year data will be aggregated by week, by month, etc. (or it could be 7 days, 14 days, etc., yes, this is still unknown to this day). point). On the backend we are using a big data warehouse (sql) solution and a Node.js server
Now I am considering 3 options, I don't know which approach to choose. If you have experience / ideas to share, it will be really appreciated!
1) the backend aggregation logic, especially the data layer, essentially does aggregation in SQL queries.
pro: 1) fast 2) evolves well if the size of the data increases (let's say that we start to show data over 3 years, more metrics)
con: 1) if the query aggregation logic changes (like from calendar month / week to rollover x days), you might end up rewriting most of the queries (maybe not true, if that's the case) . 2) Requires more work to set up a solid test.
2) aggregation logic on the backend, especially the application layer. Basically, the query returns daily data points and the application manages the aggregation logic.
pro: 1) easier to change if the aggregation logic changes (relatively)
con: 1) slower than having this in the data layer (more network traffic, difference in language performance, more load on the server) 2) worse scales compared to the approach of the data layer
3) Aggregation logic on the frontend, most of the graphics libraries allow to support different aggregation scenarios. The API essentially returns all daily data points.
pro: 1) very flexible if the logic of aggregation changes.
con: 1) slow (network traffic, browser engine, we also support mobile, so it could be very bad on mobile) 2) scales musts