How to build an efficient user-facing dashboard?

#discuss #dashboard #sql #database

So I'm working on an early stage startup with some friends of mine. I'm building a backend web app for our customers (other businesses, we're B2B). Perhaps without surprise, our landing page after login is a dashboard.

For this first iteration, I'm just going to start with basic data points: trends over time in a line chart, and a breakdown of specific events in a pie chart.

I know the old adage, or know that it's something along the lines of don't do extra work for scaleability before you even need to scale. But I do want this to be efficient, and we're already talking with a few potential clients who are quite large (have a large number of customers). If signed on, one of these clients would be creating ~500 rows a day in the table that my dashboard would be querying. If I'm displaying trends on a monthly basis, and including 6 months in the graph, that's 90,000 relevant rows that the dashboard would be asking Postgres to calculate averages on. And that's for just one line on the graph. Now I'm sure the database could handle that on principle in an acceptable amount of time. But I'm worried about the stress that may be caused as more large clients sign on.

Am I being paranoid here?
Has anyone built user-facing dashboards like this in the past? Any advice?

One thing I was thinking, is should I create a secondary table of daily averages for datapoints? We're going to introduce a feature to customers that would allow them to.. essentially they would be creating a new datapoint, resulting in a new line in that trends chart.

Anyways. I haven't thought this all the way through yet, which may show in some of my written thought process above. I was just wondering if anyone has gone down this road before, and has any advice. Might not be using the right search terms, but I'm having a hard time finding relevant articles online.

Just to say it upfront, while I have a decent grasp on most of what I'm doing thanks to working under an awesome lead at my internship -> employment for the last year, I am definitely still bit of a n00b in terms of.. idk, how to treat my database right / the above.

Top comments (2)

Joseph Jude • May 25 '17

I have not built b2b dashboards, but designed and built multiple dashboards for CXO level officers. These dashboards have to query silo systems with voluminous and in-transit data. Without knowing more details, here are my comments:

separate transaction and analytic data (if possible even put them into different dbs). This gives you flexibility to optimize each differently.
separate real-time, near-real-time, and delayed info. Unless otherwise it is a monitoring system, you don't need real-time dashboards. Almost 80% of dashboards can show delayed info (a hour old/week old); another 10 - 15% types of dashboard can live with near-real-time (10 min old data). This in itself will help you avoid lot of headache regarding compute, optimization, and truthfulness of data.
limit computation to one side. In one of the dashboard project, we started with computing certain values in the client side (browser), then we had to add mobile apps, and few other display points. Because of the way we designed, we had to replicate this compute in these devices. It was a pain. We had to rewrite to bring all compute to a single location. I prefer to do it in db, but if you are sure all traffic will go through application, you can do it in app too. [I don't know your use case. We assumed all traffic will go through the app and built logic in app; but as soon as the marketing department knew there is an analytic db, they threw in a report builder on top of the db and started building reports. This lead to questions of "truthfulness of data" since data we showed via app and the data they saw via reports were different].

Hope these give you some pointers to chew. If you need some specific discussions, don't hesitate to ask.