Business Intelligence & Analytics

Self-Hosting & Servers 2026-08-28

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. Health check: curl localhost:8088/health returns true once the app is ready.

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

Build the site first (required)

Evidence is a build tool first — the container only serves static output. Scaffold a project, preview it, then produce the production build:

npm init evidence@latest   # scaffold a new project (or clone github.com/evidence-dev/evidence starter)
npx evidence dev           # live dev preview with hot reload at localhost:3000
npx evidence build         # production build — outputs to build/

The nginx container above serves /home/user/evidence/build — an empty or missing build/ directory means an empty site.

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

Total orders last 12 months:


---

## ClickHouse (OLAP Database)

**Purpose:** Columnar OLAP database that executes analytical queries orders of magnitude faster than row-oriented databases — the storage backend of choice when Metabase or Superset queries against Postgres get slow at hundreds of millions of rows. Full setup and compose file: [Databases wiki → ClickHouse](https://docs.shani.dev/doc/servers/databases/other#clickhouse-columnar-olap-database).


## 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.

```yaml
# ~/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

Access at http://localhost:8080. Health check: curl localhost:8080/api/v1/health.

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

Default login: admin / umami — change the password immediately under Settings → Profile.

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.

Matomo (Web Analytics)

Purpose: The leading open-source web analytics platform — a complete, self-hosted Google Analytics replacement. Tracks pageviews, sessions, bounce rate, goal conversions, funnels, heatmaps (with plugin), and e-commerce. GDPR-compliant by default when configured correctly. Unlike Plausible or Umami, Matomo tracks individual visitor sessions for deep funnel analysis.

# ~/matomo/compose.yml
services:
  matomo:
    image: matomo:latest
    ports: ["127.0.0.1:8500:80"]
    environment:
      MATOMO_DATABASE_HOST: db
      MATOMO_DATABASE_ADAPTER: mysql
      MATOMO_DATABASE_DBNAME: matomo
      MATOMO_DATABASE_USERNAME: matomo
      MATOMO_DATABASE_PASSWORD: changeme
    volumes:
      - /home/user/matomo/data:/var/www/html:Z
    depends_on: [db]
    restart: unless-stopped

  db:
    image: mariadb:11
    environment:
      MYSQL_ROOT_PASSWORD: rootchangeme
      MYSQL_DATABASE: matomo
      MYSQL_USER: matomo
      MYSQL_PASSWORD: changeme
    volumes: [db_data:/var/lib/mysql]
    restart: unless-stopped

volumes:
  db_data:

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

Access at http://localhost:8500 to complete the setup wizard. Add the tracking snippet to your sites.

Choosing between Matomo, Plausible, and Umami: Matomo is the choice when you need session-level tracking, funnel analysis, and A/B testing. Use Plausible or Umami for privacy-first aggregate-only analytics with no cookies.