Business Intelligence Dashboard Development
A BI dashboard is more than just a collection of charts—it's a decision-making tool based on data. The difference from a standard analytics dashboard: users can build reports independently without developer assistance (self-service BI), work with multidimensional data, OLAP analysis, cohort analysis, and predictive metrics.
Data architecture for BI
BI works with data optimized for analytical queries, not transactions. The standard approach is a Data Warehouse with a dimensional model:
Star schema:
dim_customers
|
dim_products — fact_orders — dim_dates
|
dim_locations
fact_orders are facts (transactions) containing numerical metrics (sum, quantity) and keys to dimensions. dim_* are dimensions (descriptive data). Queries like "sales by region for Q3" execute quickly thanks to this structure.
ClickHouse as an analytical storage
ClickHouse is a columnar database with enormous aggregation speed. In practice: a query COUNT(*) + SUM(revenue) on a table with 1 billion rows takes seconds, not minutes.
-- ClickHouse: sales by category for the last 30 days
SELECT
category,
sum(revenue) AS total_revenue,
uniqExact(customer_id) AS unique_customers,
count() AS orders
FROM orders_mv
WHERE toDate(created_at) >= today() - 30
GROUP BY category
ORDER BY total_revenue DESC;
ETL from PostgreSQL to ClickHouse: via clickhouse-local + scheduled job or Apache Airflow.
Self-service BI
Self-service means an analyst or manager can build the required report themselves. Components:
- Query builder (query builder UI)—drag & drop fields, select aggregations and filters without SQL
- Dashboard builder—add a widget, choose chart type, configure axes
- Parameterized reports—template with variables, user enters values
Ready-made tools for embedding BI in your own application:
- Metabase Embedded—iframe or API, white-label capabilities
- Apache Superset—open-source, full-featured BI
- Lightdash—BI on top of dbt models
- Custom—TanStack Table + ECharts + headless query engine
OLAP and slice & dice
OLAP allows you to "slice" data across multiple dimensions:
- Drill-down: year → quarter → month → day
- Slice: only one region from all
- Dice: region × category × period
- Pivot: rows and columns swap places
In a BI dashboard, this is implemented through hierarchical filters and pivot tables.
Cohort analysis
Cohort analysis groups users by their first action period and tracks metrics over time:
| Cohort | M0 | M1 | M2 | M3 |
|---|---|---|---|---|
| Jan 2024 | 100% | 42% | 31% | 28% |
| Feb 2024 | 100% | 39% | 28% | — |
SQL for cohort retention:
WITH cohorts AS (
SELECT user_id, DATE_TRUNC('month', created_at) AS cohort_month
FROM users
),
activity AS (
SELECT user_id, DATE_TRUNC('month', event_at) AS activity_month
FROM user_events WHERE event_type = 'purchase'
)
SELECT
cohort_month,
EXTRACT(MONTH FROM AGE(activity_month, cohort_month)) AS period,
COUNT(DISTINCT a.user_id)::FLOAT / COUNT(DISTINCT c.user_id) AS retention_rate
FROM cohorts c
LEFT JOIN activity a USING (user_id)
GROUP BY 1, 2;
Access and security
- Row-level security: each manager sees only their own clients/region
- Dataset-level policies: who can create reports, who can only view
- Query audit: who and when viewed which reports
Timeline
MVP BI dashboard (ClickHouse/PostgreSQL, 10–15 reports, basic filters, user roles): 3–4 months. Full-featured BI platform with self-service builder, cohorts, ETL, and embedded SDK: 5–9 months.