# Report on a dataset

URL: https://briq.cloud/docs/guides/report-on-a-dataset

> Load a public dataset into Postgres, chart it in Metabase, and share a link to the dashboard.

Recipe:

- [Metabase](https://briq.cloud/cookbook/metabase.md): Reports on a Postgres warehouse: load a dataset, build a dashboard, share a link to it.

You want the answer to a question about some data as a dashboard you can send, not a table in
chat. The agent loads the data into a Postgres, charts it in [Metabase](https://briq.cloud/cookbook/metabase)
(open source BI) and gives you a link. Here: where the world's electricity comes from, from Our
World in Data. First, [connect your agent](https://briq.cloud/docs/getting-started).

## Ask

> Start Metabase on briq, load 'Our World in Data' energy dataset into it, generate a report on
> world energy generation, and share it with me.

## What the agent does

**1. Start the stack.** One recipe, two briqs: a Postgres warehouse, and Metabase already set up
with the warehouse connected, read-only. `expose: "private"` gives Metabase a URL only whoever
has it can open.

```text
briq_run({ recipe: "metabase", stack: "reports", expose: "private" })
→ postgres running · metabase running · url: https://metabase--reports--acme.briq.run/?briq_token=…
```

**2. Load the data.** Through `briq_exec` in the `postgres` briq, which downloads the file
itself, so the data never goes through the agent. One row per country and year, and some 130
measures: electricity by source in TWh (`fossil_electricity`, `nuclear_electricity`,
`renewables_electricity`, `solar_electricity`…), their shares in %, energy use. The table's columns
come from the file's header: `country` and `iso_code` are text, `year` an integer, the rest
numbers.

```sh
wget -qO /tmp/energy.csv https://raw.githubusercontent.com/owid/energy-data/master/owid-energy-data.csv
cols=$(head -1 /tmp/energy.csv | sed 's/,/ numeric, /g; s/$/ numeric/; s/^country numeric/country text/;
  s/year numeric/year int/; s/iso_code numeric/iso_code text/')
psql -U postgres -d warehouse -c "CREATE TABLE energy ($cols)" -c "\copy energy from /tmp/energy.csv with (format csv, header)"
```

Rows without an `iso_code` are aggregates (World, continents, income groups, the same regions as
each source draws them): `country = 'World'` gives the totals (its `iso_code` is empty, not
`OWID_WRL`), `iso_code IS NOT NULL` keeps the countries.
World figures run to 2025.

**3. Build the dashboard.** Through `briq_exec` in the `metabase` briq, where `mb` calls
Metabase's API. A chart is a SQL query and a display (`table`, `row`, `line`, `scalar`…); the
dashboard places it:

```sh
mb POST /api/card '{"name": "Largest producers, 2025 (TWh)", "display": "row",
  "visualization_settings": {"stackable.stack_type": "stacked"}, "dataset_query": {"type": "native", "database": 1,
  "native": {"query": "select country, fossil_electricity as fossil, nuclear_electricity as nuclear,
  renewables_electricity as renewables from energy where iso_code is not null and year = 2025
  order by electricity_generation desc nulls last limit 12"}}}'
mb POST /api/dashboard '{"name": "Where the world'\''s electricity comes from"}'
mb PUT /api/dashboard/1 '{"dashcards": [{"id": -1, "card_id": 1, "row": 0, "col": 0, "size_x": 12, "size_y": 8}]}'
```

One card per question: the world by source since 1985, solar and wind against hydro, the
low-carbon share of the largest producers, renewables' share country by country.

**4. Share it.** A public link shows that one dashboard, read-only, without an account:

```sh
mb POST /api/dashboard/1/public_link
→ {"uuid":"26caf580-…"}
```

The link to send is the briq's private URL with the dashboard's path:
`https://metabase--reports--acme.briq.run/public/dashboard/26caf580-…?briq_token=…`. briq turns
away every request without the token before it reaches Metabase, so only people with the link see
the report.

## Tips

* **First boot takes about three minutes** while Metabase migrates its database.
* **Polish it by hand** at `URL`, signed in with `METABASE_ADMIN_EMAIL` and
  `METABASE_ADMIN_PASSWORD`.
* **Fresh data, same dashboard**: Our World in Data updates the file as Ember and the Energy
  Institute publish; load it again and every chart, a query run on each view, follows.
* **Withdraw the link** with `briq_expose({ briq, port: 3000, rotate: true })`: the URL stays, the
  old token stops working. Destroy both briqs when the report has served its purpose.