Building Blocks for Zero-Code Enterprise BI Dashboards
Introduction
Business Intelligence is set of tools that allow us to know more about anything. New technology advances have given companies access to exponentially more data about everything related to their business. Data is the key component for creating business intelligence. Business Intelligence (BI) dashboard is a powerful tool that reveal more actionable data, provide clear insights, and facilitate strategic decision making. If you take the example of banking sector, a bank can leverage BI dashboards to show relevant analytics (to a Relationship Manager for example) to unlock the goldmine of information to cater to their customers.
Business Intelligence (BI) dashboard
Conceptually, creating a BI dashboard involves following steps:
1. Data Sourcing — Companies typically have data in different systems, and they must build data connectors to bring the data to central data repository.
2. Data Wrangling — prepare, cleanse, and transform data across multiple data sources to fuel powerful metrics and dashboards.
3. Data Storage — store the data in central data warehouse, SQL database, No-SQL database or even in the cloud (S3)
4. Data Visualization — build data visualizations to enable users to navigate the data via charts, graphs, tables, KPI metrics etc.
In this article we limit our discussion to the topic of Data Visualizations. Here we present a simple yet powerful low-code framework which can be used by anyone to create BI dashboards very easily.
Features
We would like our BI Dashboards to have following features:
1. Declarative schema for data visualizations — ability to declaratively define dashboard specification including filters, data sources, layout, and visual specifications without the need for writing single line of code. In layman terms, a) we need a provision to define reports and data sources which specify what data is presented in it, b) have the ability to create dashboards by drag and drop reports from existing universe of reports and c) configure layout preferences of dashboard/widgets and filters
2. Dashboard metric filters — which control the metrics and data presented in the entire dashboard. These filters may have dependency on data sources. For example if a filter contains a dropdown for showing states in US, we need a mechanism to bind the data source to the dropdown
3. Individual metric filters — which control the data presented in a specific metric.
4. Customizable — clickable and expandable, drag-and-drop widgets.
5. Share — with anyone via email or a link.
6. Dynamic dashboard — Dynamically change the filters and grouping of all the metrics on a dashboard using date, number, or categorical logic.
7. Support various chart types — be able to change the look and feel of the presentation whether its
a. KPI Metrics
b. Full dial KPI chart
c. Scorecard KPI Charts
d. Ranking KPI Chart
e. Series (Column/Bar/Line/Area graphs) or Non-Series (Bubble, Radar, Funnel, Table, Map, Pie, Bullet, and Gauge) charts.
Now lets look at the technical design.
High Level Design
At a high level a dashboard has the following elements:
1. Metadata — captures various attributes of a dashboard including like name, description, status etc.
2. Widgets— Widget is an encapsulation of data, layout and the filters which change the data presented in it.
3. Reports— reports are another layer of abstraction for the data and the specification of the report.
4. Data Sources— data source can be SQL/NO-SQL based construct which can be plugged into a report.
Dashboard Database Model
Here we present the database structure required for storing the declarative specification for the dashboard.
Dashboard & widgets are the objects that contains:
· Dashboard fields like name, description, sharing status etc.
· Widget objects defines the report template & report name.
· Widget Properties like widgetConfigJson, reportFilterJson & layoutJson — here we store the metadata for widgets in JSON format.
TCM_REPORT_TEMPLATE.CHART_TYPE.Chart_Type
1. Chart
2. KPI
TCM_REPORT_TEMPLATE.CHART_TYPE.Chart_Sub_Type
for KPI:
1. Bullet_Chart
2. Dial_Chart
3. Full_Dial_Chart
4. Ranking
5. Scorecard
6. Single_Value
7. Single_Value_Secondary
8. Single_Value_Secondary_Indicator
for Chart :
1. Area
2. Donut
3. Funnel
4. Grouped_Bar
5. Line
6. Pie
7. Table
Dashboard Specification
POST /api/v1/reports/dashboard
Logical Flow for Dashboard Data Processing
GET /api/v1/reports/templates/{templateUUID}/data
Below diagram shows how a widget is rendered using reports and data sources
ReportTemplateProcessor is the main class that performs common logic for report creation. It makes proper ReportDataProvider (MySQL or MongoDb), execute data requests and pass their results to ReportResultBuilder. After getting the report data, process that report data to WidgetDataTransformer class. By passing the chartSubType & report data to the templating engine ( freemarker templating), it will convert report data to widget data.
Report parameters should be provided with the URL
Example with “ds_name” parameter:
GET /api/v1/reports/templates/6229815a-8b6e-4c87–877a-db0906d27d96/data?ds_name=DSNameDataTest
There are two reserved parameters names:
LIMIT_PARAM — limit report result data amount. The default value is 1000.
SORT_PARAM — sort report result data. Format — “property,property(,ASC|DESC)”.
User Interface
Reference implementation of the UI has the following highlights:
1. Angular — Angular is maintained by Google
2. NX Mono Repo — this is enterprise framework for creating microservices and micro-frontends
3. Gridster library — Is a drag and drop multi-column grid, enabling users to build intuitive draggable layouts from elements spanning multiple columns. It also supports dynamically adding and removing elements from the grid.
4. Ant V G2 charting library (backed by Alibaba) — G2 is a highly interactive data-driven visualization grammar for statistical charts. with a high level of usability and scalability. It provides a set of grammars, takes users beyond a limited set of charts to an almost unlimited world of graphical forms. The advantage of Ant V library is that they also have corresponding mobile charting library Ant V F2 charts.
UI Flow
UI Screens
- Create new dashboard page
Below UI is used to add the reports to the dashboard. The reports are differentiated by the categories. Every report has a default widget chartSubType.
2. Add Reports Page
3.Configure Filters
4. View Dashboard Page
- Share Dashboard with Link
5. Share Dashboard to Email
Conclusion
BI dashboards are powerful tools that help any organization see the big picture. Here we presented a simple yet very powerful enterprise framework for enabling BI dashboards for any application in your organization.
