Business Intelligence & Analytics

Self-hosted BI platforms, data visualisation tools, SQL explorers, and analytical dashboards. Query your databases, build charts, and share insights — without sending your business data to a cloud analytics vendor.

Why self-host BI? Your databases already live on your server. Running your analytics stack next to them eliminates egress costs, keeps sensitive data on-premises, and removes per-seat licensing that makes cloud BI prohibitively expensive for small teams.

---

Key Concepts

OLAP vs OLTP — the fundamental access pattern split

OLTP (Online Transaction Processing) databases (PostgreSQL, MySQL) are row-oriented — optimised for reading/writing single rows quickly. Each INSERT or SELECT retrieves entire rows. OLAP (Online Analytical Processing) databases (ClickHouse, DuckDB, Redshift) are column-oriented — data for one column is stored contiguously on disk. SELECT AVG(revenue) FROM orders reads only the revenue column, skipping all other fields. At 1 billion rows, this is a 10–100× query time difference. BI tools that query OLAP databases feel instant; the same queries against PostgreSQL time out. The architectural decision: use PostgreSQL for your app's operational data, replicate or ETL to ClickHouse/DuckDB for analytics.

Star schema and dimensional modelling

Dimensional modelling organises analytical data around a central fact table (transactions, events, measurements) surrounded by dimension tables (customers, products, dates). The fact table holds numeric measures (revenue, quantity, duration) and foreign keys to dimensions. Queries join the fact table to dimensions to slice and dice — "total revenue by country and product category this quarter" is one query. This schema is called a star because the fact table is at the centre with dimension tables radiating out. BI tools (Metabase, Superset, Looker) assume this structure and generate SQL against it. A data warehouse without dimensional modelling produces slow, complex, unmaintainable queries.

ETL vs ELT — the modern data pipeline shift

ETL (Extract, Transform, Load) transforms data before loading it into the warehouse — useful when transformation is expensive or the destination schema is rigid. ELT (Extract, Load, Transform) loads raw data first, then transforms it inside the warehouse using SQL — the modern pattern enabled by cheap columnar storage. Tools: dbt (data build tool) is the standard ELT transformation layer — it defines transformations as SQL SELECT statements, manages dependencies, tests data quality, and generates documentation. Lightdash reads dbt models directly. This is the dominant data engineering stack in 2025: source → Airbyte/Fivetran (extract/load) → dbt (transform) → ClickHouse/Redshift (warehouse) → Metabase/Superset (BI).

Data freshness, materialisation, and query performance

A dashboard that runs a 30-second query on every page load is unusable. Three solutions: (1) Materialised views — pre-computed query results stored as a table, refreshed on a schedule or trigger. ClickHouse continuous materialised views update in real time as data arrives. (2) Dashboard caching — Metabase and Superset cache query results for a configurable TTL; stale data trades off against query load. (3) Pre-aggregation — aggregate raw events into daily/hourly summaries at ingest time. Cube.js and dbt handle this. The rule: analytical queries should complete in under 2 seconds for interactive use. If they don't, materialise or aggregate.

Role-based access control in BI tools

BI tools often have access to sensitive data. Row-level security (RLS) restricts which rows a user can see — a sales manager sees only their region's data even when running a global query. Superset implements RLS via SQL WHERE clause injection on the dataset level. Column-level permissions prevent certain roles from seeing PII fields. Data masking replaces sensitive values with asterisks or tokens for lower-trust roles. In interviews for data engineering or analytics engineering roles, RLS and data governance come up frequently — it's the difference between a BI tool that just works internally versus one that can be safely exposed to external users or regulated environments.

Metrics layers and semantic consistency

Without a metrics layer, the same business metric is defined differently in 20 different Metabase queries — "monthly active users" means different things to different dashboards. A metrics layer (dbt metrics, Cube.js, LookML) defines metrics once and exposes them to all BI tools. The metric definition (SQL logic, time grain, filters) lives in one place; BI tools query the layer rather than raw tables. This is a standard concept in mature data organisations — the distinction between a "data analyst who builds dashboards" and a "analytics engineer who builds the semantic layer the dashboards query."

Metabase

Purpose: The most approachable self-hosted BI tool. Non-technical users can build charts and dashboards by clicking through a question builder — no SQL required. For power users, the native query editor supports full SQL with autocomplete, query versioning, and parameterised questions. Connects to PostgreSQL, MySQL, MariaDB, MongoDB, SQLite, ClickHouse, Redshift, BigQuery, Snowflake, and more.

# ~/metabase/compose.yaml
services:
  metabase:
    image: metabase/metabase:latest
    ports:
      - "127.0.0.1:3000:3000"
    volumes:
      - /home/user/metabase/data:/metabase-data:Z
    environment:
      MB_DB_TYPE: postgres
      MB_DB_DBNAME: metabase
      MB_DB_PORT: 5432
      MB_DB_USER: metabase
      MB_DB_PASS: changeme
      MB_DB_HOST: host.containers.internal
      MB_SITE_URL: https://metabase.home.local
    restart: unless-stopped

cd ~/metabase && podman-compose up -d

Common operations

# Check Metabase health
curl http://localhost:3000/api/health

# View logs
podman logs -f metabase

# Reset admin password (if locked out)
podman exec metabase java -jar metabase.jar reset-password admin@example.com

# Export a question/dashboard result via API
curl -X POST http://localhost:3000/api/dataset   -H "X-Metabase-Session: YOUR_SESSION_TOKEN"   -H "Content-Type: application/json"   -d '{"database":1,"type":"native","native":{"query":"SELECT count(*) FROM orders"}}'   | python3 -m json.tool

# Get session token for API use
curl -X POST http://localhost:3000/api/session   -H "Content-Type: application/json"   -d '{"username":"admin@example.com","password":"changeme"}'

Metabase can use its built-in H2 database for evaluation, but PostgreSQL is strongly recommended for production — it handles concurrent users and stores question/dashboard history reliably.

Key features to explore after setup

• Questions — saved queries that auto-refresh on a schedule
• Dashboards — drag-and-drop canvas combining multiple questions with filters
• Subscriptions — email or Slack delivery of dashboard snapshots on a cron schedule
• Alerts — notify when a metric crosses a threshold
• Embedding — embed signed charts into other apps or internal tools via iframes
• Models — curated, reusable data layers that hide raw table complexity from end users

Caddy:

metabase.home.local { tls internal; reverse_proxy localhost:3000 }

---

Apache Superset

Purpose: Enterprise-grade BI and data exploration platform from Apache. More powerful and more configurable than Metabase — supports 40+ database connectors, a drag-and-drop chart builder, a full SQL IDE (SQL Lab), role-based access control, row-level security, and advanced chart types (Sankey, sunburst, heatmap, geospatial). Steeper learning curve but no feature ceilings.

# ~/superset/compose.yaml
services:
  redis:
    image: redis:7-alpine
    restart: unless-stopped

  db:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: superset
      POSTGRES_PASSWORD: changeme
      POSTGRES_DB: superset
    volumes: [pg_data:/var/lib/postgresql/data]
    restart: unless-stopped

  superset:
    image: apache/superset:latest
    ports: ["127.0.0.1:8088:8088"]
    environment:
      SUPERSET_SECRET_KEY: changeme-run-openssl-rand-base64-42
      DATABASE_URL: postgresql+psycopg2://superset:changeme@db:5432/superset
      REDIS_URL: redis://redis:6379/0
    volumes:
      - /home/user/superset/config:/app/superset_home:Z
    depends_on: [db, redis]
    restart: unless-stopped

  superset-init:
    image: apache/superset:latest
    command: >
      bash -c "
        superset db upgrade &&
        superset fab create-admin
          --username admin --firstname Admin --lastname Admin
          --email admin@example.com --password changeme &&
        superset init"
    environment:
      SUPERSET_SECRET_KEY: changeme-run-openssl-rand-base64-42
      DATABASE_URL: postgresql+psycopg2://superset:changeme@db:5432/superset
    depends_on: [db]

volumes:
  pg_data:

cd ~/superset && podman-compose up -d

Access at http://localhost:8088.

SQL Lab: — the built-in IDE supports multi-tab SQL editing, query history, schema explorer, result export to CSV/Excel, and saved queries shared across the team. It is a full replacement for tools like DBeaver for query work.

---

Redash

Purpose: Query-first BI tool. Write SQL (or use the query builder), visualise the results, and assemble dashboards. Strong focus on scheduled query refreshes and alerting — ideal for operational dashboards that need to stay current. Supports PostgreSQL, MySQL, MongoDB, Elasticsearch, InfluxDB, Google Sheets, and REST APIs as data sources.

# ~/redash/compose.yaml
x-redash-service: &redash-service
  image: redash/redash:latest
  environment:
    REDASH_DATABASE_URL: postgresql://redash:changeme@postgres/redash
    REDASH_REDIS_URL: redis://redis:6379/0
    REDASH_SECRET_KEY: changeme
    REDASH_COOKIE_SECRET: changeme
  depends_on: [postgres, redis]

services:
  server:
    <<: *redash-service
    ports: ["127.0.0.1:5000:5000"]
    command: server
    restart: unless-stopped

  scheduler:
    <<: *redash-service
    command: scheduler
    restart: unless-stopped

  worker:
    <<: *redash-service
    command: worker
    restart: unless-stopped

  postgres:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: redash
      POSTGRES_PASSWORD: changeme
      POSTGRES_DB: redash
    volumes: [pg_data:/var/lib/postgresql/data]
    restart: unless-stopped

  redis:
    image: redis:7-alpine
    restart: unless-stopped

volumes:
  pg_data:

cd ~/redash && podman-compose up -d

Initialise the database (first run only)

podman-compose run --rm server create_db

Version note: Redash jumped from v10.1 to v25.1 in early 2025 (a 3-year release gap). The :latest tag will pull v25.x. If upgrading from v10.x, review the release notes — the scheduler service structure changed in v10.

Access at http://localhost:5000.

---

Evidence.dev

Purpose: Code-first BI tool — write SQL queries and Markdown in .md files, and Evidence renders them as a polished interactive report site. Version-controlled in Git, deployed as a static site. Ideal for analysts who prefer code over drag-and-drop and want reports that live in the same repo as the data pipelines that produce them.

# ~/evidence/compose.yaml
services:
  evidence:
    image: nginx:alpine
    ports:
      - 127.0.0.1:3002:3000
    volumes:
      - /home/user/evidence/build:/usr/share/nginx/html:ro,Z
    restart: unless-stopped

cd ~/evidence && podman-compose up -d

Example report page (pages/sales.md)

# Sales Overview

sql orders_by_month SELECT date_trunc('month', created_at) AS month, COUNT(*) AS orders, SUM(total) AS revenue FROM orders WHERE created_at >= NOW() - INTERVAL '12 months' GROUP BY 1 ORDER BY 1

<LineChart data={orders_by_month} x=month y=revenue title="Monthly Revenue" />

Total orders last 12 months: **<Value data={orders_by_month} column=orders fmt=num0 agg=sum />**

---

ClickHouse (OLAP Database)

Purpose: Columnar OLAP database that executes analytical queries orders of magnitude faster than row-oriented databases. If you are running Metabase or Superset against a PostgreSQL table with hundreds of millions of rows and queries are slow, ClickHouse is the answer. Used by Cloudflare, Uber, and Bytedance for petabyte-scale analytics.

# ~/clickhouse/compose.yaml
services:
  clickhouse:
    image: clickhouse/clickhouse-server:latest
    ports:
      - "127.0.0.1:8123:8123"
      - "127.0.0.1:9000:9000"
    volumes:
      - /home/user/clickhouse/data:/var/lib/clickhouse:Z
      - /home/user/clickhouse/logs:/var/log/clickhouse-server:Z
    environment:
      CLICKHOUSE_USER: admin
      CLICKHOUSE_PASSWORD: changeme
      CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT: 1
    ulimits:
      nofile:
        soft: 262144
        hard: 262144
    restart: unless-stopped

cd ~/clickhouse && podman-compose up -d

Connect and run queries

podman exec -it clickhouse clickhouse-client --user admin --password changeme

-- Create a table and insert data
CREATE TABLE events (
  event_date Date,
  event_type String,
  user_id UInt64,
  properties String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, event_type, user_id);

-- ClickHouse can ingest from Kafka directly
CREATE TABLE kafka_events (...)
ENGINE = Kafka
SETTINGS kafka_broker_list = 'localhost:9092',
         kafka_topic_list = 'events',
         kafka_group_name = 'clickhouse',
         kafka_format = 'JSONEachRow';

Common operations

# Connect to ClickHouse SQL shell
podman exec -it clickhouse clickhouse-client --user admin --password changeme

# Run a query non-interactively
podman exec clickhouse clickhouse-client --user admin --password changeme   --query "SELECT count() FROM system.tables"

# Show all databases
podman exec clickhouse clickhouse-client --user admin --password changeme   --query "SHOW DATABASES"

# Show tables in a database
podman exec clickhouse clickhouse-client --user admin --password changeme   --query "SHOW TABLES FROM default"

# Ingest CSV data
cat data.csv | podman exec -i clickhouse clickhouse-client   --user admin --password changeme   --query "INSERT INTO mydb.mytable FORMAT CSV"

# Check disk usage per table
podman exec clickhouse clickhouse-client --user admin --password changeme   --query "SELECT table, formatReadableSize(sum(bytes)) FROM system.parts GROUP BY table ORDER BY sum(bytes) DESC"

# View running queries
podman exec clickhouse clickhouse-client --user admin --password changeme   --query "SELECT query_id, elapsed, query FROM system.processes"

# Kill a long-running query
podman exec clickhouse clickhouse-client --user admin --password changeme   --query "KILL QUERY WHERE query_id='abc123'"

Connect Metabase or Superset to ClickHouse to get sub-second query times on datasets that would take minutes in PostgreSQL.

---

Lightdash (dbt-Native BI)

Purpose: Open-source BI tool built on top of dbt (data build tool). If your team already uses dbt for data transformation, Lightdash reads your dbt models and metrics directly — no reimporting schemas, no duplicated definitions. Metrics defined in dbt YAML automatically appear in Lightdash dashboards.

# ~/lightdash/compose.yaml
services:
  lightdash:
    image: lightdash/lightdash:latest
    ports: ["127.0.0.1:8080:8080"]
    environment:
      PGHOST: db
      PGPORT: 5432
      PGUSER: lightdash
      PGPASSWORD: changeme
      PGDATABASE: lightdash
      SECRET_KEY: changeme-run-openssl-rand-hex-32
      SITE_URL: https://lightdash.home.local
    volumes:
      - /home/user/lightdash/dbt:/usr/app/dbt:Z
    depends_on: [db]
    restart: unless-stopped

  db:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: lightdash
      POSTGRES_PASSWORD: changeme
      POSTGRES_DB: lightdash
    volumes: [pg_data:/var/lib/postgresql/data]
    restart: unless-stopped

volumes:
  pg_data:

cd ~/lightdash && podman-compose up -d

---

Plausible Analytics (Web Analytics)

Purpose: Lightweight, GDPR-compliant web analytics. No cookies, no cross-site tracking, no personal data stored. A one-line script tag replaces Google Analytics with a dashboard you own. See pageviews, referrers, top pages, devices, and conversion goals — without privacy violations.

# ~/plausible/compose.yaml
services:
  plausible_db:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: plausible
      POSTGRES_PASSWORD: changeme
      POSTGRES_DB: plausible
    volumes: [pg_data:/var/lib/postgresql/data]
    restart: unless-stopped

  plausible_events_db:
    image: clickhouse/clickhouse-server:latest
    volumes: [events_data:/var/lib/clickhouse]
    restart: unless-stopped

  plausible:
    image: ghcr.io/plausible/community-edition:v2
    ports: ["127.0.0.1:8000:8000"]
    environment:
      BASE_URL: https://analytics.example.com
      SECRET_KEY_BASE: changeme-run-openssl-rand-base64-64
      DATABASE_URL: postgres://plausible:changeme@plausible_db:5432/plausible
      CLICKHOUSE_DATABASE_URL: http://plausible_events_db:8123/plausible_events
    depends_on: [plausible_db, plausible_events_db]
    restart: unless-stopped

volumes: {pg_data: {}, events_data: {}}

cd ~/plausible && podman-compose up -d

Add to any website:

<script defer data-domain="yoursite.com" src="https://analytics.example.com/js/script.js"></script>

---

Umami (Simple Web Analytics)

Purpose: Simpler Plausible alternative. Single-service analytics with event tracking, funnel analysis, and an OpenAPI. Backed by PostgreSQL or MySQL.

# ~/umami/compose.yaml
services:
  umami:
    image: ghcr.io/umami-software/umami:postgresql-latest
    ports: ["127.0.0.1:3003:3000"]
    environment:
      DATABASE_URL: postgresql://umami:umami@db:5432/umami
      APP_SECRET: changeme
    depends_on: [db]
    restart: unless-stopped

  db:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: umami
      POSTGRES_PASSWORD: umami
      POSTGRES_DB: umami
    volumes: [pg_data:/var/lib/postgresql/data]
    restart: unless-stopped

volumes: {pg_data: {}}

cd ~/umami && podman-compose up -d

---

Choosing the Right Tool

| Use Case | Recommended Tool | |----------|-----------------| | Non-technical users, quick setup | Metabase | | Large teams, enterprise features, 40+ connectors | Apache Superset | | Operational dashboards, alerting on query results | Redash | | Time-series, infrastructure metrics | Grafana | | Code-first reports in Git | Evidence.dev | | dbt-native BI | Lightdash | | Fast analytics on 100M+ row tables | ClickHouse | | GDPR-compliant web analytics | Plausible | | Simple web analytics | Umami |

---

Caddy Configuration

metabase.home.local    { tls internal; reverse_proxy localhost:3000 }
superset.home.local    { tls internal; reverse_proxy localhost:8088 }
redash.home.local      { tls internal; reverse_proxy localhost:5000 }
analytics.example.com  { reverse_proxy localhost:8000 }
lightdash.home.local   { tls internal; reverse_proxy localhost:8080 }

---

Troubleshooting

| Issue | Solution | |-------|----------| | Metabase blank on first load | Wait 60–90 s for initialisation; check logs with podman logs metabase; ensure PostgreSQL is reachable on host.containers.internal | | Metabase Cannot connect to database | Use host.containers.internal not localhost for the DB host; verify credentials match the PostgreSQL container | | Superset No module named psycopg2 | Add psycopg2-binary to the image or use apache/superset:latest which includes it | | Superset charts not loading | Check SUPERSET_SECRET_KEY is set and consistent; clear browser cache | | Redash worker not processing queries | Verify Redis is running; check podman-compose logs worker for connection errors | | ClickHouse OOM | Add --memory 4g to limit container memory; tune max_memory_usage in ClickHouse config | | Plausible no events received | Verify BASE_URL matches your site's script src; check CSP headers aren't blocking the script | | Evidence build fails | Ensure your database credentials in sources/ are correct; run npm run sources to retest connections |

💡 Tip: For the best Metabase experience, connect it to a read replica of your production database rather than the primary — long-running analytical queries won't block application writes.