1

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.

3
  • 1
    Given the pros and cons of each option, which one seems most favorable for your particular application? May 8, 2020 at 22:00
  • @RobertHarvey I'm leaning towards 1 and 2. Given the team's skillset, no2. is a better fit (as we don't have sql experts). But on the other hand, there is concern regarding long-term scalability May 9, 2020 at 22:09
  • Have you used any other BI products? ... What do they do?
    – svidgen
    May 10, 2020 at 4:32

1 Answer 1

1

It is hard to assess this without context. How much data is there? What are the requirements of the dashboard, i.e. is it real-time?

In general, I would tend towards doing aggregation in the backend, because I think I have more flexibility there in terms of implementation options and optimization, but it depends on your experience, deployment system, etc.

2
  • no real-time requirement. Right now the data needed for display is around 365 data points for each metric (basically a timestamp and a value pair) and there are around 20 metrics. On the backend, can you share some insights regarding aggregation on the data layer or vs application layer? thanks! May 9, 2020 at 22:17
  • Yeah, I guess the main thing is that SQL databases have a built in caching layer and they have aggregations implemented in efficient C code. You can build that stuff out, but it’s really nice to use the best implementations available. Nov 27, 2021 at 12:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.