Day 40. Open-source BI for companies without Microsoft licences

⏱ 4h · learn 60m · lab 145m · ship 20m · review 15m🏷 restaurants, retailBefore: Day 39

By the end of today you can

Run Metabase and Apache Superset on Postgres with Docker
Rebuild your Power BI pages in Metabase and compare honestly
Know where Evidence, Lightdash, Grafana and Streamlit fit
Choose a BI tool for a medium company by cost, skills and governance

1. Why this matters

Your mission includes profitable stacks for medium companies. Power BI Pro is cheap per user but adds up across 200 restaurant managers, and some companies run Google Workspace or nothing at all. Open-source BI tools are mature, free to self-host, and in many cases good enough. A senior person can recommend either with numbers.

2. The options

Tool	Style	Strengths	Weak spots
Metabase (OSS + paid cloud)	Point-and-click questions + SQL, dashboards, subscriptions	Fastest for business users; email/Slack subscriptions; embedding	Complex modelling, very large semantic layers
Apache Superset	SQL Lab + chart builder, many chart types	Scales to many users, RBAC, row-level security, rich viz	Steeper setup and authoring
Evidence	Reports as code (Markdown + SQL), static site	Git-native, beautiful, cheap to host	Not for self-service exploration
Lightdash	BI on top of dbt models/metrics	Uses dbt YAML as the semantic layer	Needs dbt
Grafana	Time-series and ops dashboards	Pipeline monitoring, alerts	Not a business BI tool
Streamlit / Dash	Python apps	Custom tools (forecast what-ifs, schedule builders)	You maintain code

3. Metabase in ten minutes

# docker-compose.metabase.yml
services:
  mb-db: { image: postgres:16, environment: { POSTGRES_USER: mb, POSTGRES_PASSWORD: mb, POSTGRES_DB: metabase } }
  metabase:
    image: metabase/metabase:latest        # pin a version in real use
    ports: ["3001:3000"]
    environment:
      MB_DB_TYPE: postgres
      MB_DB_DBNAME: metabase
      MB_DB_PORT: 5432
      MB_DB_USER: mb
      MB_DB_PASS: mb
      MB_DB_HOST: mb-db
    depends_on: [mb-db]

Metabase's own app database goes in Postgres (not the default H2 file) so it survives upgrades. Then add your warehouse (Postgres, or Azure SQL via the SQL Server driver) as a data source using the read-only report_reader login.

4. Superset in brief

Use the official Docker Compose setup from the Superset repo for local trials; production deployments use the Helm chart or a managed provider. Connect via SQLAlchemy URLs (postgresql+psycopg2://…, mssql+pyodbc://…). Define datasets (a table or SQL), then charts, then dashboards. Row-level security is configured per role and dataset.

Maths and principle

Principle: licence cost scales with users; self-hosting cost scales with servers and people time. The cheaper option flips at some user count.

Equation: $C_{\text{licensed}}(u) = p \cdot u$; $C_{\text{self}}(u) = C_{\text{vm}} + h \cdot w + \epsilon u$. Break-even $u^* = \dfrac{C_{\text{vm}} + h w}{p - \epsilon}$.

Analogy: fixed vs variable cost in a cost-volume-profit analysis. Where it breaks: hidden costs on both sides (training, SSO, support, the analyst's time building in a less familiar tool).

Try it: licence $14/user/month; self-hosted VM $60/month plus 4 h/month at $50/h; marginal user cost ≈ 0. Break-even users? ((60 + 200) / 14 ≈ 19 users. Above that, self-hosting is cheaper on paper.)

5. Choosing

Microsoft 365 company, Excel-heavy users → Power BI. The integration (Teams, Excel, Entra) is worth the licence.
No Microsoft, many viewers, few authors → Metabase (subscriptions and simple questions cover most needs).
Data team wants many chart types, SQL-first, strict RBAC → Superset.
Reports as code, published monthly or daily → Evidence.
A custom tool (what-if pricing, schedule builder) → Streamlit next to whichever BI tool.

The 5-year lens

The tool matters less than one set of metric definitions feeding it. If you move a company from Power BI to Metabase, the metric dictionary (Day 34) and the dbt marts don't change; only the presentation layer does. Say that in interviews: "I keep business logic in dbt so the BI tool is replaceable." It shows you think about the company's exit costs, not just your favourite tool.

Lab

Load your marts into Postgres (Week 4 resource with P2D_WAREHOUSE=postgres).
Run Metabase with Postgres app DB. Rebuild the QSR ops page: KPI tiles with comparisons, trend, restaurant bars, channel mix.
Set up a Metabase dashboard subscription to your own email (configure SMTP to a test account).
Run Superset locally; build the hotel pace chart and set one RLS rule.
Build the same QSR page in Evidence (npx degit evidence-dev/template), publish locally.
Write docs/bi-comparison.md: time to build each page, what was harder, what each did better, cost at 20 / 100 / 300 users using the formula above with current prices you look up.

Ship

feat(bi): Metabase, Superset and Evidence versions with a costed comparison.