How to design a flexible solution for custom dashboards and reports

7 min readJan 21, 2021

Trying to build a solution that is simple, fast, scalable, highly available yet flexible to handle custom requests from your clients? A solution that can serve a group of customers or can dedicatedly be deployed for a single customer in a multi-tenant environment? Then, this post is for you.

Why do we need this?

No matter how vast or fine the solution you provide to your clients, there are always custom requirements. Some requests can be fulfilled with small tweaks but others can take a big effort and a complete development cycle. For a SaaS solution, these requests are very common and always a challenging task. Dashboards and Reports are the most common areas for these types of requests where every customer needs data that make more sense to them and in the format, they understand better.

History behind —

We have tried a few solutions over the years to deliver Dashboards and Reports, but they were never up to the expectations.

First Solution: It all started a few years back when we published the first solution. It was implemented using Java, MySQL, JavaScript, and elycharts (an open-source javascript charting library). The publisher was sending the data on two different services, which were processing and storing the data in MySQL tables. For every dashboard and report, data was being fetched from MySQL (yeah, even for the live data we used MySQL as a queue). Both independent dashboard and reporting applications were serving the data what “we” think makes sense to the customers but the solution has multiple drawbacks and limitations. The major ones are —

Data discrepancy on dashboards and reports due to different data sources.
Very frequent queries(polling) on the database just to keep refreshing the dashboard views.
Custom requirements need implementation of new views, APIs, and deployment of the application to ship the changes.
UI freeze due to frequent data refreshes API hit and view rendering.

Second Solution: To overcome the challenges we tried integrating with a third-party UI provider for dashboards. We also did some changes around data sources, data categorization, data polling, etc.

To solve data discrepancy, we divided the data into two categories live vs delayed. Earlier we were trying to present the whole data on dashboards as soon as possible and to do that, we were managing a different data source for dashboards and putting a heavy read load on it.

But when we took a closer look at the data, not all the KPIs required to be presented as live. Very few KPIs that help supervisors to take dynamic decisions can be presented quickly and the rest of the data can be presented with some delay and we really do not need to keep refreshing it every few seconds. So, for the live data, we implemented the WebSockets using Node and for delayed data, we started polling from the reporting database every 2–3 minutes.

Although after implementing this solution we were left with handling custom requirement challenges and integration issues with the third party. But overall we reached into more bad condition as handling custom requirements was still a big challenge and their implementation was impacting the whole server. Also, due to third party involvement every requirement and fix turnaround time increased a lot too. As a result, we have to start thinking about other solutions even before onboarding all our customers to this new solution.

Third Solution:

Keeping all the problems in mind I started thinking to make a flexible solution where I can —

Create on-demand Dashboards and Reports.
Add new data widgets.
Avoid server downtime for new requests.
Maintain sanity of the application.
No impact on other customers running on the same server (in a multitenant system).
Live data can be presented without any delay.
Cumulative data can be presented without any discrepancies.

And started looking for all the possible solutions. After doing some research and brainstorming I noted few points that can help me achieve the goal. Let’s have a look at some major points and how they help to solve the problem statements.

Configuration based views for Dashboards, Reports, and widgets to handle custom requirements.
Manage widgets-based data queries in the database to avoid server downtime.
A common API to fetch the data for every widget to make the solution flexible and avoid new development for every new view.
Independent UI application with the capabilities of handling the configuration, and flexible enough to add new presentation formats so we can easily add new features on UI without asking for any deployment and downtime.
Even on the dashboard, not all the data needs to be presented live. Data can be processed and presented based on the priority (Live vs Delayed). This helps to avoid putting unnecessary load on the application and databases.
Using WebSockets for handling streaming data to present live data without any delay.
A common data source for both Dashboards and Reports to avoid data discrepancies. Other than streaming data rest of the data can be present with a fixed refresh interval on dashboards.

Implementation —

To implement configuration based view and data queries, I designed the following schema —

Views (Capture Dashboard or Report specific settings)
Widgets (Capture widget specific configuration)
Data Queries (Capture SELECT queries for every cumulative widget that returns the data in required format)
View_Widgets (Manages the mapping of views and widgets)
User_Settings (Manages user preferences)

Every widget either gets the data from the database or receives it from WebSocket. To feed the data from the database we implemented a generic query that takes the widgetId and applied filters as request parameters to fetch the data. Based on the widgetId, API figures out the query to be executed. Each query has the placeholders for all the possible filters and returns the data in the required format.

To handle the UI part, we implemented an Angular based application and hosted it on S3. For tabular data presentation, we used ag-grid and for graphical views, we used the echarts library. For rendering every type of widget only basic implementation was done inside the project and major configuration and properties were passed through the database.

Complete Solution & Functioning —

Here is a broad picture of the complete solution. The user opens up the browser for data monitoring. As soon as the user tries to access the Dashboard or Reports all the available views get listed (based on the user’s access list).

When the user clicks on a specific Dashboard or Report, an API with the request parameter view_id gets hit. This API fetches configuration for that particular View (Report or Dashboard) and all the mapped widgets. Every widget’s configuration has the data endpoint API and filters to subscribe from View to fetch the data. Based on the request params, the server identifies the data query to be executed and returns the data as explained above.

With a simple form, we can insert new entries into the configuration tables. As these entries are managed into a customer-specific database it won’t impact the other customer. There is no downtime required as the application is running while we are adding the new configurations. There are no changes required on the applications and we just need to test the data query if it returns the expected data and has no performance impacts. List of available Dashboards & Reports getting render on UI dynamically from configuration so we can deliver on-demand Dashboards, Reports, and widgets.

Final Result —

More on the solution —

I know there are few more topics that will help to understand the complete solution but I am giving it a break here with a high-level explanation of the overall system. I will explain more about following topics in my future blogs-

WebSocket implementation
Generic API and Dynamic Filters
Multitenancy
Response data formats
Configurations stored in database
Role/ACL

All these are part of the complete solution and will take another chapter to explain. I will link back here once they are available.

Scope of Improvements —

Live data presentation with complex calculations without application changes. (will explain along with WebSockets)