Skip to main content

BI feed

The BI feed gives Power BI, Looker Studio and Metabase read-only SQL access to your company's own call, queue, SMS and AI metrics. Each credential is a read-only database user that sees only your company's rows. There is no write path.

Open Admin > Settings > Integrations > BI feed (/admin/bi-feed). You need integrations:read to see the connection details and integrations:write to issue and revoke credentials.

Issue a credential​

  1. Press Create BI user and give it a Label of 1 to 80 characters, for example Power BI.
  2. Copy the Password at once. It is shown only once and cannot be recovered. If you lose it, revoke the credential and issue a new one.
  3. Use the Address, Database, User and password in your BI tool.

Before issuing you can limit what the credential reads: untick the views it does not need (for example only bi_cdr), and untick Include the AI summary text to keep the text of the summaries out of bi_ai (the size of each summary stays readable). A limited credential cannot read the other views, and the limit cannot be widened later: issue a new credential instead. The list shows what each credential reads.

A company can hold 10 active credentials. Revoke removes the database user at once. Issuing and revoking are written to the audit log.

If the Address shows "Not published by the administrator", or the page answers that the feature is not set up, the operator has not configured the BI feed. See Operator setup.

What a BI tool sees​

Four views, with times in UTC. Every view also has a tenant_id column, always your company.

ViewOne row perMain columns
bi_cdrcallcall_id, start_time, answer_time, end_time, direction, caller_e164, callee_e164, did, trunk_id, disposition, ring_ms, talk_ms, hold_ms, queue_wait_ms, cost_micros, tags, sip_final_code
bi_queuequeue eventts, queue_id, call_id, event, agent_extension_id, agent_user_id, wait_ms, position, reason
bi_smsSMS or MMSts, message_id, conversation_id, direction, status, kind, from_number, to_number, segments, media_count, error_code
bi_aicallcall_id, start_time, direction, disposition, talk_ms, has_ai_summary, ai_summary_chars, ai_summary

Recording references and the raw dialled strings are not exposed. ai_summary is the text of the AI call summary, so treat a BI workspace that holds it like you treat recordings.

The credential has SELECT on these four views and nothing else. A row policy on every view filters the rows to your company, so a WHERE, a subquery or a UNION cannot get around it. The user is read-only and its queries are limited (60 seconds each, 1,000,000 result rows).

Connect a tool​

  • Power BI. Use the ClickHouse HTTP interface through a web data source with Basic credentials (the user and password above). No driver is needed. A ClickHouse ODBC driver or connector works with the same user.
  • Looker Studio. Use the built-in MySQL connector against ClickHouse's MySQL port (9004) with the database, user and password, and a custom query per data source. The operator must open that port with TLS.
  • Metabase. Add the open-source ClickHouse driver to Metabase's plugins folder (Metabase Cloud has it), then add a ClickHouse database with the host, port, database, user and password.

Views read live data and are not materialised. Filter heavy dashboards by start_time or ts.

Operator setup​

Servers installed with the MonoPbx installer need no setup: the installer (and, on an older server, the next update) creates the database user that manages the BI users, stores its password with the other install secrets, and turns the feed on. The console's BI feed page then says BI feed ready and offers Create BI user. To keep the feed off, add MPBX_BI_FEED=off to the install's .env and restart.

BI tools connect to the analytics database directly only through an address the operator publishes (a reverse proxy or firewall rule limited to the networks that need it). Until then the page says so, and companies can still create BI users and download the views as CSV. To show the published address on the page, set MPBX_BI_PUBLIC_URL (for example https://bi.example.com) in .env.

Other installations:

  • Docker Compose by hand: run mpbx init again (it only adds missing secrets) and start with -f compose.yaml -f compose.bi-feed.yaml.
  • Kubernetes: mpbx init writes the password into the install Secret; add the bi-feed component to your overlay.
  • Your own configuration: set clickhouse.bi_admin_user, clickhouse.bi_admin_password and optionally clickhouse.bi_public_url.

When the feed is off, the BI feed page says so in one sentence with a link to this guide; the API answers 503 MPBX-API-NOT-CONFIGURED.

API​

CallPermission
GET /api/v1/integrations/bi-feed: connection details, views and credentialsintegrations:read
POST /api/v1/integrations/bi-feed/credentials with {"label": "Power BI"} (optional views and ai_summary limit it): the answer carries the password onceintegrations:write
DELETE /api/v1/integrations/bi-feed/credentials/{id}integrations:write
GET /api/v1/integrations/bi-feed/export/{view}?from=&to=&limit=: one view as CSV (works with an API key)integrations:read

Limits​

  • A credential sees all four views unless it was issued with a subset.
  • The CSV export (the CSV export card on the BI feed page, or the call above) is for installs that cannot publish ClickHouse: oldest rows first, up to 50,000 rows by default (200,000 at most), without the AI summary text; narrow from/to and fetch again when X-Mpbx-Truncated says more matched. Every export is written to the audit log.
  • The ready-made Power BI, Looker Studio and Metabase templates are in the repository (docs/guides/bi-feed/).