Self-Service Business Intelligence and Dashboards

A reporting workspace for visual data questions, SQL analysis and shared dashboards.

Reporting in this workspace starts with a saved question, which a user builds by choosing the data, the filters and the aggregations. The answer then becomes a chart or a dashboard. An analyst who needs finer control can drop down and write the SQL directly.

This self-service BI case study covers that shared layer. We linked the visual builder and the SQL editor to a single set of query definitions. One question could then feed dashboards and subscriptions, with its access checks intact.

The challenge

Visual reporting needs more than chart controls. A user has to see the fields and sample values they recognise. The backend then has to turn those choices into something the connected database can understand.

Permissions have to follow the question as well. A dashboard filter can ask for values from protected data. Checking access only when the main chart loads would let that request slip outside the question's rules.

What we built

The workspace lets a user save questions, drill into the rows behind a chart and group related answers into dashboards. Models, metrics and segments hold definitions that other questions reuse. Common logic is not rebuilt each time.

Once a database connection is set up, a background process reads its tables and columns. It samples the values and marks the fields that suit a category filter. The visual builder then offers meaningful choices. Saved content can also drive notifications, subscriptions and the available embedding options.

Technical approach

The browser and the server share one query model. The server preprocesses a question, compiles it for the chosen database and runs it through a driver. Decisions about SQL dialect stay inside that path. Rows come back as a stream, so the system can start working before the whole answer has arrived.

Before a query runs, the pipeline checks the acting user's access. That covers the saved question and the data it references. A filter-value query gets the same treatment, because it depends on the question behind the filter. An inactive table can be turned away before any database call. Runs that a person triggered keep their execution details and timing, which helps when something needs a closer look.

Quartz handles scheduling for the background work. Database-backed queues coordinate the supported jobs across instances, so the setup needs no separate message broker. The application database holds configuration and operational records. The analytical data stays in the connected databases. Persistent application storage and a working scheduler are therefore required parts of the setup.

Project success

The client has a clear path from one question to reusable reporting. A person selects the data, saves the logic and drops the result into a dashboard or a subscription. Visual users and SQL users work from the same shared definitions. Database drivers handle the execution itself. The permission checks still reach the underlying data requests, down to the filter values someone uses to explore a report.

Technology stack

Backend

Clojure, embedded Jetty

Frontend

TypeScript, React, Redux Toolkit, Mantine

Shared query model

ClojureScript

SQL construction

HoneySQL, database drivers

Scheduling

Quartz, database-backed queues

Application storage

PostgreSQL or MySQL/MariaDB