BI Next

BI Next replaces a classic dashboard BI with an AI analyst over a PostgreSQL database. Managers ask in plain language; a grounded LLM agent calls read-only tools, writes and runs analysis code in a sandbox in real time, and answers with figures that tie out to the cent with what the screens show.

The client's legacy dashboard BI was slow (2–4 s per page), reported different values for the same KPI on different pages, was organised by database table instead of management question, and explained none of its figures. A business rule applied to only some views inflated revenue by 22 % and contribution margin by 34 %. Its first AI assistant read those wrong numbers from a fixed snapshot and was told never to admit missing data, which invited hallucination.

BI Next rebuilds the data layer in PostgreSQL around one canonical line-level fact and five materialised marts, with every KPI defined once in a metric registry that drives the SQL, labels, tooltips, glossary and exports. On top sits an AI analyst: a tool-calling LLM that queries the same scoped path as every screen, writes pandas code that runs in an isolated sandbox when a question needs a calculation over the whole population, and returns a full trace of every call and result. Classic pages remain for routine controlling, but the main interface is a question.

• Grounded analysis assistant

  Implemented

  A tool-calling LLM (Gemini 2.5 Flash) with read-only tools for KPIs, breakdowns, time series, alerts and the metric registry. Every number must come from a tool result, and the loop is bounded to eight steps.

• Live code execution

  Implemented

  For counts, Pareto analyses and distributions the model writes pandas code that runs in an isolated subprocess with an AST gate, restricted builtins and a hard 8 s timeout, and the exact result is returned.

• Auditable answers

  Implemented

  Each answer ships with the full trace of tool calls and their real results, so any figure in the prose can be checked.

• Honest refusals

  Implemented

  The data boundary is stated in the prompt: the assistant refuses questions about order intake, forecasts or stock that invoice data cannot answer, instead of guessing.

• Canonical data layer

  Implemented

  One line-level materialised fact over the ERP tables and five monthly marts, with indexes and a nightly refresh, leaving the ERP source tables untouched.

• Metric registry

  Implemented

  21 metrics across 13 dimensions, each defined once; a KPI cannot be shown without a definition, and the glossary is generated from it.

• Management pillars

  Partial

  Controlling, sales steering and reports with Excel export organised by management question; the customer-pricing and assortment pillars are still being built.

• Sales agent cockpit

  Implemented

  Scoped logins for field agents show their own margin leaks (terms, free goods, returns) per customer, paired with the lever they control.

• Audited global data filter

  Implemented

  Admins can exclude intercompany or test entities from every report; the effect is previewed in euros, shown on every page and logged.

• Voice questions

  Planned

  Speaking a question instead of typing it.

ERP source tables in PostgreSQL stay read-only. A canonical line-level materialised view is the only place business rules are written; five monthly marts aggregate from it. A FastAPI service exposes generic KPI, time-series and breakdown endpoints driven by a metric registry, applies row-level scope and the global filter server-side, and hosts the AI analyst. The analyst calls the same scoped query path as the screens and hands already-scoped rows to an isolated Python sandbox for live calculations. A React SPA renders the pillars, reports and the assistant chat with its tool trace.

frontend

React 19TypeScriptViteTailwind CSS 4React Router 7TanStack QueryRecharts

backend

PythonFastAPIpandasNumPy

data

PostgreSQLMaterialised viewspg_cron

ai

Google Gemini 2.5 FlashFunction callingSandboxed code execution

infra

Docker ComposenginxGoogle Cloud SQL

integrations

ERP invoice tablesPimcoreExcel (XLSX)

• Tool-grounded answers

  The model can only state figures that came back from read-only tools over the reconciled data layer; when a tool returns nothing, it says so.

• Real-time analysis code

  When a question needs a count or cumulative share over thousands of rows, the model writes pandas code that is executed, rather than tallying in context.

• Inherited security scope

  Tools route through the same scoped path as the screens, so a sales agent's assistant can never see another agent's figures.

• Tested with a tie-out harness

  Answers are re-run against the API and the database: the tool trace must match, and every figure in the prose must appear in the trace.

1. 01

   Numbers that disagreed

   The root bug was a filter that dropped credit-note lines, inflating revenue by 22 % and margin by 34 %. The rule now lives in exactly one view, and a test asserts every mart agrees to 0.000 % drift.

2. 02

   LLMs cannot count

   Given all rows, the model answered a population count with 49, 68 and 99 across runs. Executing model-written code instead made it exact, without the sandbox ever touching the database.

3. 03

   Executing model-written code safely

   Import allowlists alone are not enough, because pandas itself can do IO. An AST gate also blocks the IO and eval methods, dunder traversal and getattr bypasses; ten escape attempts are part of the test suite.

4. 04

   Row-level security that cannot leak

   An empty scope means no rows, never no filter, and scoped queries are rerouted to the line-level fact when a mart has no agent column.

• A question, not a dashboard

  Managers ask what they want to know in their own words and get a decision-shaped answer with figures and the levers behind them.

• Proven, not plausible

  KPIs reconcile to the official ERP export within 0.44 %. On ten hard benchmark questions the assistant was exact on eight and refused the unanswerable ones; the two counting misses became exact once it could run code.

• Fast where it counts

  Like-for-like queries dropped from 3.85 s to 0.13 s, and a 105.7 MB map file was simplified to 0.64 MB with every region kept.

The source repository is private. A live walkthrough is available on request.