How To Build a Custom Business Intelligence Tool: A Complete Guide

Back in 2019, someone on r/BusinessIntelligence posted a question that still ranks on the first page of Google for "custom business intelligence software": Custom Built BI Tool. Worth the time? (opens in a new tab) The thread collected more than 80 replies, and the interest behind it has only grown since: people search "custom business intelligence" about three times as often as they did two years ago.

The question keeps coming back because the wish behind it never went away. Most data teams have, at some point, wanted a BI tool shaped around the way their company actually works, with the exact pages, the exact metrics and the exact click paths their people use, and none of the menus, licence tiers and admin screens they never asked for. The trouble has always been the cost. A team that builds its own tool takes on the whole job of a BI vendor: a query layer, a charting layer, permissions, filters, caching, and the training and support every new user needs. In 2019 the honest answer to "is it worth it?" was usually no, because a two-person data team that set out to build a dashboard framework would end up maintaining one instead of answering questions.

That answer has changed over the last two years, for two reasons.

The first is that the hard part of a custom BI tool, keeping metric definitions consistent across every page, now has open-source answers in the form of semantic layers you can run yourself. The second is that coding agents such as Claude Code, Codex and Cursor can now write the front-end code (the HTML, the charts, the filter wiring) in minutes, which used to be the most expensive part of the job. What was a pipe dream in 2019 can now be a weekend project for one analytics engineer, provided the metrics live somewhere the agent cannot redefine them.

This guide walks through the whole process of building a custom BI tool, and it is written around one constraint: doing it as economically as possible. Every component in the build is open source or free, everything runs on a laptop, and the only bills left are ones most data teams already pay, namely warehouse compute and a coding-agent subscription. The build involves no per-seat BI licences, no hosted platform fees and no implementation partner.

What a custom BI tool is made of

A custom BI tool looks like a set of dashboards from the outside, but underneath it is six separate systems that each need an owner. Teams that skip one of them usually discover it a few months later, when two pages show different revenue totals or a new hire cannot find out what a column means.

1. Data integration. Raw data from your product database, billing system, CRM and ad platforms has to land in one warehouse before anything else can happen. Open-source options here include Airbyte, Meltano and dlt for extraction and loading, and dbt for transforming raw tables into clean, joined models.
2. The semantic model. The semantic model is where "revenue", "active customer" and "churn rate" get one definition each, written as code and reviewed like code. This is the component that separates a BI tool from a pile of SQL queries, because every page and every agent asks the semantic model for a metric by name instead of writing its own calculation. Open-source options include anfra's AMQL, dbt's MetricFlow, Cube and Malloy.
3. Dashboards and visuals. The pages people actually look at need a charting library. In a custom tool this is the part you control completely, and the usual open-source choices are Apache ECharts, Vega-Lite, Observable Plot, Chart.js and D3 for anything unusual.
4. Interactions. Filters, date-grain switches, drill-downs and cross-filtering (clicking a bar to filter another chart) are what turn a static report into something people can explore. Interactions are also where hand-built tools usually fall apart, because each one has to rewrite the underlying query correctly, and a wrong join in a filter produces a plausible but wrong number.
5. AI. In 2026 there are two places AI fits in a custom BI tool. A coding agent builds and edits the pages, and an AI assistant inside the tool answers questions for business users. Both need the same thing from the stack: governed definitions they can read, usually through an MCP server, so they query metrics instead of guessing at raw tables.
6. Documentation and catalog. Somebody will eventually ask what a table or column means, and a custom tool needs a place to answer that. Open-source options range from DBML-based docs (dbdocs, dbdiagram) for the warehouse schema to full catalogs such as OpenMetadata and DataHub for larger teams.

Which tools to choose

The table below lists the open-source pick for each component in this guide, along with common alternatives. The pieces are independent, so a team that already runs dbt or Cube can keep them.

Every pick in the middle column was chosen for cost as much as for fit: each one is free to use, runs on your own machine or your own server, and stores its work as plain files in your Git repository, so nothing locks you into a paid tier later. The reason this guide uses anfra for the middle of the stack is that it covers the semantic model, the interactions and the agent integration in one place, and its apps contain no SQL. Each page asks for metrics by name, and anfra turns every click into a query against definitions your data team owns. The same approach works with the other tools in the right-hand column, but you would write the glue between them yourself.

Prerequisites: what to set up before you start

The build needs a handful of things in place before the first command. None of them requires a purchase you would not already make, and two of them (the data and the agent) can be swapped for free stand-ins while you try things out.

1. A Linux or macOS machine, or Docker. anfra ships as a single binary for Linux and macOS on x64 and arm64. Windows users can run the Docker image instead (ghcr.io/holistics/anfra), which needs Docker Desktop or another container runtime.
2. Data to query. A warehouse you already run works best: PostgreSQL, Redshift, BigQuery, Snowflake, Databricks, ClickHouse, MySQL, SQL Server, Oracle, Athena, Presto, DuckDB and MotherDuck are all supported. A local DuckDB file is the zero-cost option for a first pass, because it needs no server and no credentials.
3. Warehouse credentials with read access. The connection fields differ by database (a host, port, user and database name for PostgreSQL; a project ID and service-account JSON for BigQuery), so it helps to have them ready. A read-only user is the sensible choice, since the BI tool only ever reads.
4. A few modelled tables. The examples in this guide use orders, order_items and products. Clean, joined tables (from dbt or any other transformation tool) make the modelling step much shorter than raw source tables do.
5. Git. An anfra project is a folder of plain files, and its value comes from reviewing metric changes through pull requests, so the project should live in a Git repository from day one.
6. A coding agent. Claude Code, Codex or Cursor can use anfra's agent skills directly, and any other agent can install them with npx skills add holistics/anfra-skills. anfra does not include an AI model, so this is the subscription you bring. An agent is optional for the steps below, since every file can be written by hand, but it is where most of the time savings come from.
7. Working knowledge of SQL and a little HTML/JavaScript. The semantic model reads like SQL, and a data app is one HTML file. You can lean on the agent for both, but reviewing what it writes is easier when the syntax is familiar.

Building a custom BI app, step by step

The steps below follow the anfra documentation (opens in a new tab) in condensed form. By the end you will have a project in Git, a connection to your warehouse, a governed sales dataset with a revenue metric, an interactive data app with a product filter, a week/month switch and a cross-filter, and a published catalog of the underlying tables. The whole thing runs locally, and none of it needs a cloud account.

Step 1: Install anfra

anfra ships as a single binary for Linux and macOS (x64 and arm64), with no daemon to run. The installer puts anfra in ~/.anfra/bin and adds it to your PATH:

curl -fsSL https://anfra.ai/install.sh | bash

Teams that prefer containers can run the same thing from the Docker image, which serves the apps, the API and the MCP server on port 7878:

docker run -p 7878:7878 -v "$PWD:/repo" ghcr.io/holistics/anfra

The next command installs anfra's agent skills into Claude Code and Codex. The skills teach the agent how to write models, write and validate queries, and build data apps, and a hook validates model files as the agent edits them. Cursor users add --agent cursor, and other agents can use npx skills add holistics/anfra-skills.

anfra skills install

The last step scaffolds a new project:

anfra init custom-bi
cd custom-bi/

Step 2: Connect your data sources

anfra reads warehouse connections from .anfra/data_sources.yml. Each entry has a name, a type and a connection block, and the name is how your models refer to that source. A PostgreSQL warehouse looks like this:

# .anfra/data_sources.yml
data_sources:
  warehouse:
    type: postgresql
    connection:
      host: localhost
      port: 5432
      user: anfra
      password: anfra
      dbname: analytics

The supported type values cover PostgreSQL, Amazon Redshift, Google BigQuery, Snowflake, Databricks, ClickHouse, MySQL, Microsoft SQL Server, Oracle, Amazon Athena, Presto, DuckDB and MotherDuck. Each has its own required fields (BigQuery takes a project_id and a service-account credentials_json, for example, and Snowflake adds warehouse), and the data sources page (opens in a new tab) lists them all. DuckDB is the quickest way to try anfra without a warehouse, since its only setting is an optional file path.

The file holds credentials, so anfra init adds it to .gitignore and creates a data_sources.yml.example without secrets for you to commit instead.

Step 3: Understand the project structure

An anfra project is a folder of plain files that lives in Git, which means every metric change and every new page goes through the same pull-request review as the rest of your code. anfra init creates this layout:

my-project/
├── models/                         # AML data models
├── datasets/                       # AML datasets and their metrics
├── apps/                           # Data apps, one HTML file each
├── .anfra/
│   ├── data_sources.yml            # Warehouse connections, with credentials (git-ignored)
│   ├── data_sources.yml.example    # The same without credentials, to commit
│   └── context_sources.yml         # What anfra ingest reads for anfra search
└── .gitignore                      # Ignores .anfra/data_sources.yml

anfra finds model files by their extension, so you can nest models/ and datasets/ by business domain once the project grows. Apps are the exception, since they have to stay under apps/. Logs, caches and the local search index live outside the project, in ~/.anfra/repos/, so they never end up in a commit.

Step 4: Model your data

The semantic model has two layers. A data model maps one table (or one SQL query) to dimensions, which are raw fields, and measures, which are aggregations inside that single table. A dataset combines several models, declares how they join, and holds the metrics that span them. Models never declare joins themselves, which keeps all the join logic in one reviewable place.

Here is a data model for an orders table:

// models/orders.model.aml
Model orders {
  type: 'table'
  label: 'Orders'
  data_source_name: 'warehouse'
  table_name: '"public"."orders"'
 
  dimension id {
    label: 'Id'
    type: 'number'
    definition: @sql {{ #SOURCE.id }};;
  }
 
  dimension status {
    label: 'Status'
    type: 'text'
    definition: @sql {{ #SOURCE.status }};;
  }
 
  measure order_count {
    label: 'Order Count'
    type: 'number'
    definition: @aql count(orders.id);;
  }
}

And here is the sales dataset that joins orders, order items and products, and defines revenue once:

// datasets/sales.dataset.aml
Dataset sales {
  label: 'Sales'
  data_source_name: 'warehouse'
  models: [orders, order_items, products]
  relationships: [
    relationship(order_items.order_id > orders.id, true),
    relationship(order_items.product_id > products.id, true)
  ]
 
  metric revenue {
    label: 'Revenue'
    type: 'number'
    definition: @aql sum(order_items.quantity * products.price);;
  }
}

The relationship syntax deserves a careful look. In relationship(a > b, true), a is the many side and b is the one side, and true makes the join active. The direction matters, because a join declared the wrong way round fans out rows and inflates totals without any error message, which is exactly the kind of mistake a custom BI tool has to catch before it reaches a dashboard.

You can write these files yourself, or ask your coding agent to draft them with the aml skill and review the result like any other code change. anfra validate "models/*.aml" checks the files before you commit them.

Step 5: Build a data app

A data app in anfra is a single HTML file under apps/. The file declares its queries through the SDK's global Anfra object and renders the results with whatever HTML, CSS and charting library you like. anfra supplies the data and keeps track of state, and your code (or your agent's code) decides what the page looks like.

The pattern is the same in every app: you create the app with Anfra.createApp(), declare every query, subscribe a render function, and call app.execute(). This app shows a monthly revenue trend and revenue by order status:

<!-- apps/sales.html -->
<!doctype html>
<html>
<head>
  <meta charset="utf-8">
  <title>Sales</title>
</head>
<body>
  <div id="by-status"></div>
  <div id="trend"></div>
 
  <script>
    const app = Anfra.createApp({ title: 'Sales' })
 
    const trend = app.createQuery('trend', {
      dataset: 'sales',
      aql: `explore {
        dimensions { period: orders.created_at | month() }
        measures { revenue: revenue }
        sorts { period asc }
      }`,
    })
 
    const byStatus = app.createQuery('byStatus', {
      dataset: 'sales',
      aql: `explore {
        dimensions { status: orders.status }
        measures { revenue: revenue }
        sorts { revenue desc }
      }`,
    })
 
    function render () {
      if (trend.result) drawLine('#trend', trend.result.rows)          // [{ period, revenue }]
      if (byStatus.result) drawBars('#by-status', byStatus.result.rows) // [{ status, revenue }]
    }
 
    app.subscribe(render)
    app.execute()
  </script>
</body>
</html>

drawLine and drawBars stand in for your own charting code, and the complete example in the docs (opens in a new tab) uses ECharts. The important detail is the measures { revenue: revenue } line. The app asks for the revenue metric by name, so it gets exactly the calculation defined in the dataset, and the next app anyone builds will get the same number.

Two commands start the local server:

anfra status   # is a server already running for this repo, and where?
anfra serve    # if not, start one

The app is then live at http://127.0.0.1:7878/apps/sales. Apps run in a sandboxed iframe with scripts allowed and storage blocked, so localStorage, cookies and <form> elements are off the table.

In practice, most teams let the coding agent write this file. A prompt as plain as "Use the build-data-app skill. Show revenue over time and revenue by order status." produces the page above, and because the agent can only ask for metrics through the SDK, the numbers stay governed no matter how the page looks.

Step 6: Add interactions

Interactions are where the SDK saves the most work. Each control is a few lines of declarations, and the queries themselves never change: anfra rewrites each query from the reader's choices and sends the result to your render function.

A filter narrows both charts to one product. The filter is created on a field of the dataset and then mapped to each query it should affect:

<select id="product"><option value="">All products</option></select>

const product = app.createFilter('product', { dataset: 'sales', field: 'products.name' })
 
app.mapControl(product, trend, { field: 'products.name' })
app.mapControl(product, byStatus, { field: 'products.name' })
 
const productSelect = document.querySelector('#product')
product.loadOptions().then((options) => {
  productSelect.append(...options.map((value) => new Option(String(value), String(value))))
})
productSelect.addEventListener('change', () => {
  const value = productSelect.value
  product.setCondition({ operator: 'is', values: value ? [value] : [] })
  app.execute()
})

A date drill lets readers switch the trend between weekly and monthly grain:

<select id="grain">
  <option value="week">Week</option>
  <option value="month" selected>Month</option>
</select>

const grain = app.createDateDrill('grain', { default: 'month' })
app.mapControl(grain, trend, { field: 'orders.created_at' })
 
document.querySelector('#grain').addEventListener('change', (event) => {
  grain.setGrain(event.target.value)
  app.execute()
})

A cross-filter makes a click on a status bar filter the trend. The click handler passes the clicked row from the result, and a button clears the selection:

<button id="clear" type="button">Clear selection</button>

app.mapCrossFilter(byStatus, trend)
 
// Your chart library's click handler: pass the clicked row from the result
onBarClick('#by-status', (index) => {
  byStatus.select([byStatus.result.rows[index]])
  app.execute()
})
 
document.querySelector('#clear').addEventListener('click', () => {
  app.clearSelection()
  app.execute()
})

Every query also exposes its state, which makes loading and empty states easy to handle. While a query runs, query.result keeps the previous rows, so the page can draw them dimmed under a spinner instead of flashing blank:

function renderState (query, selector) {
  const el = document.querySelector(selector)
  if (query.state === 'error') el.textContent = `Couldn't load: ${query.error.message}`
  else if (query.state === 'success' && query.result.rows.length === 0) el.textContent = 'No sales for this selection'
}

The last thing to check is the numbers themselves. The Inspect panel in anfra serve (or query.result.debug in code) shows the AQL and the exact SQL that ran on your warehouse for any chart, so a reviewer can confirm what a number means without reading the app's code.

Step 7: Document and catalog your data with dbdocs

The semantic model documents your metrics, but the people who maintain it still need to know what sits underneath: which tables exist, what each column holds and how the tables relate. dbdocs (opens in a new tab) covers that layer. It turns a DBML file (a small open-source language for describing database schemas) into a browsable documentation site for your warehouse, and it comes from the same team that builds anfra and dbdiagram.

dbdocs needs Node.js and npm, and the CLI installs globally:

npm install -g dbdocs
dbdocs login

Writing DBML by hand is optional, because dbdocs can read the schema straight from your database. The command runs on your machine, and the credentials stay there. It supports PostgreSQL, MySQL, SQL Server, Snowflake and BigQuery:

dbdocs db2dbml postgres <connection-string> -o database.dbml

The generated file is worth editing before you publish it, because notes are where the catalog earns its keep. A trimmed version for the tables in this guide might look like this:

Project Ecommerce {
  database_type: 'PostgreSQL'
  Note: '''
    # Ecommerce warehouse
    Source tables for the `sales` dataset in our custom BI project.
  '''
}
 
Table orders {
  id int [pk]
  user_id int [not null]
  status varchar [note: 'pending, paid, shipped, cancelled']
  created_at timestamp [note: 'When the order was placed']
}
 
Table order_items {
  order_id int [ref: > orders.id]
  product_id int [ref: > products.id]
  quantity int [default: 1]
  note: 'One row per product in an order'
}
 
Table products {
  id int [pk]
  name varchar
  price int [note: 'Unit price in cents']
}

The Ref markers mirror the relationships in the sales dataset, so anyone reading the docs sees the same join directions the semantic model uses. One command publishes the site:

dbdocs build database.dbml --project custom-bi

New doc projects are private by default. A project can be shared as a public link or a password-protected link on the free plan (dbdocs password --set <password> --project custom-bi), and paid plans add invited viewers and editors.

A note on tooling: the dbdocs CLI is being folded into the dbdiagram CLI (opens in a new tab), which publishes the same doc project with dbdiagram build document. The dbdocs CLI keeps working and stays documented, but it stops receiving updates in 2027, so new projects may prefer the dbdiagram CLI from the start.

What this setup costs

The build above was put together to keep the bill as close to zero as possible, and the line items show where the money goes (and where it does not).

The comparison that matters is with a packaged BI tool, which typically charges per seat, so the bill grows with every person who needs to see a dashboard. The setup in this guide has no per-seat cost at all: adding a reader costs nothing, and adding a page costs one prompt and one review. The trade-off is that features a packaged tool includes, such as user logins, row-level permissions and scheduled email reports, are yours to add (or to get later from a hosted tier), so the most economical path is to start with one team, one dataset and a handful of pages, and grow from there.

So, is a custom-built BI tool worth it now?

The 2019 thread asked a fair question, and for most teams at the time the answer was no, because building the tool meant becoming a BI vendor. The steps above show how much of that job has moved. Data integration and transformation have mature open-source tools, the semantic model gives every page one definition of each metric, the SDK handles the query rewriting that filters and cross-filters need, coding agents write the pages, and dbdocs answers the "what does this column mean?" questions.

Most of the cost has gone with it: every component in this build is open source, and the remaining costs are warehouse queries and the coding agent your team likely pays for already. What remains is a smaller and more useful job: deciding what your metrics mean, reviewing the changes to them, and asking for the pages your team needs. A custom BI tool built this way is worth it, and costs very little to try, when your people need pages a packaged BI tool cannot give them, such as a page shaped around one team's workflow, a report embedded in an internal tool, or an app that combines metrics with your own interface. For a team whose needs fit a standard dashboard tool, a packaged tool is still less work.

The quickest way to find out which side you are on is to try it. The anfra init project above takes about ten minutes to set up (a local DuckDB file works if you would rather not connect a warehouse yet), and one prompt to your coding agent will show you whether the result is closer to what your team wants than the dashboards you have today.