Business Intelligence Tools

What BI tools do

Business intelligence (BI) is the practice, and the set of tools, for turning an organization's data into reports, dashboards and analysis that people use to monitor performance and make decisions. The short introduction on Data Warehousing and Analytics places BI tools on top of a data warehouse; this page looks inside them.

At Kestrel Energy the monthly key performance indicator pack for Margriet de Wit, the chief financial officer (CFO), is still a workbook that Lucía Romero builds by hand. Joost Bakker is replacing the manual steps with a nightly load into an analytics store (see Data Pipelines), and the pack itself is to become a dashboard in a BI tool. Whatever product Kestrel picks, it will consist of the same parts:

• Connections to data sources: databases, files, cloud warehouses, other applications.
• A model of the data: which tables, how they relate, and the calculations (measures) on top; usually with security rules.
• Visual building blocks: charts, tables, single-number tiles, filters, arranged into reports and dashboards.
• A service that stores and shares the content, refreshes the data on a schedule, and controls who sees what.

A BI tool is not a database and not a pipeline. It works best on data that has already been cleaned and shaped, ideally into a star schema (see Data Modeling for Analytics), and it should never query the production database directly: Hamid Rahimi, Kestrel's database administrator, would rightly object to report traffic on db01 (see why analytics does not run on production).

The main products

The market changes quickly; the overview below is as of October 2026.

Others include Qlik, and the BI features built into cloud warehouses. Grafana also draws charts from SQL databases, but it is designed for operational monitoring rather than business reporting; see Dashboards.

Power BI

Power BI is Microsoft's BI product: Power BI Desktop, a Windows application for building data models and reports, and the Power BI service, a cloud service (part of Microsoft Fabric) for publishing, sharing and refreshing them. Because many organizations that already use Microsoft 365 meet it first, a few of its facts are worth knowing precisely (as of October 2026):

• What older material calls a Power BI dataset was renamed semantic model in November 2023. The programming interfaces still say "datasets".
• Power BI Desktop runs only on Windows (Windows 10 or Windows Server 2016 and later). There is no Mac or Linux version; Mac users work in the browser or in a Windows virtual machine.
• Reports and their measures are built in Desktop; the formula language for measures is DAX (Data Analysis Expressions).

Connecting to data

A BI model gets its data in one of two basic ways, and the choice affects freshness, speed and load on the source.

Microsoft's documentation states the consequence plainly: "only Import mode semantic models require a source data refresh". In live mode the model "returns results from the underlying data source whenever a report or dashboard queries the semantic model". Microsoft Fabric adds a third mode, Direct Lake, for data that is already stored in Fabric.

For Kestrel the choice is easy. The analytics store changes once a night, so an imported model refreshed after the nightly load is fast and puts no load on anything during the day.

The semantic layer

Ask three people at Kestrel how many contracts were active at the end of September, and you may get three answers, all computed correctly:

SELECT count(*) FILTER (WHERE c.end_date IS NULL) AS no_end_date,
       count(*) FILTER (WHERE c.start_date <= DATE '2026-09-30'
                          AND (c.end_date IS NULL OR c.end_date >= DATE '2026-09-30')) AS active_on_30_sep,
       count(cu.customer_id) FILTER (WHERE c.start_date <= DATE '2026-09-30'
                          AND (c.end_date IS NULL OR c.end_date >= DATE '2026-09-30')) AS active_known_customer
FROM contracts c
LEFT JOIN customers cu USING (customer_id);

┌─────────────┬──────────────────┬───────────────────────┐
│ no_end_date │ active_on_30_sep │ active_known_customer │
│    int64    │      int64       │         int64         │
├─────────────┼──────────────────┼───────────────────────┤
│          13 │               12 │                    11 │
└─────────────┴──────────────────┴───────────────────────┘

"No end date" counts contract CT-20013, which only starts on 1 October. "Active on 30 September" counts CT-20014, whose customer does not exist in the customers table. A report that joins contracts to customers loses that contract without a warning, and gets 11. If Lucía's pack says 13 and a new dashboard says 11, the meeting is about the numbers instead of the business. Femke Jansen's approved definition, below, also gives 11, but on purpose: contracts without a known customer are left out and reported separately.

A semantic layer is a shared definition of business concepts on top of the stored data: which tables relate how, what the dimensions are called, and how each measure is calculated, so that every report, dashboard and query tool uses the same definitions. Power BI calls its version a semantic model; in Looker it is the LookML model; dbt has a semantic layer of its own based on MetricFlow (Apache License 2.0 since late 2025). The idea is the same: define "active contract" once, in one place, with an owner, and let every report reuse it.

Measures

A measure is a named calculation in a BI model that aggregates data, such as "Billed amount" or "Overdue %". It is evaluated at whatever level of detail the report asks for (per month, per tariff, per contract, or in total), always with the same formula. A measure is not a stored column: it is computed at query time from the rows that match the current filters.

In plain SQL you can get part of the way with a view that applies the business rules once. The query on top then shows why a ratio must be a measure and not a stored number:

CREATE VIEW invoice_facts AS
SELECT i.period,
       c.tariff,
       i.contract_id,
       i.amount_eur AS billed_eur,
       CASE WHEN i.status = 'overdue' THEN i.amount_eur ELSE 0 END AS overdue_eur
FROM invoices i
JOIN contracts c USING (contract_id);

SELECT strftime(period, '%Y-%m') AS month,
       sum(billed_eur) AS billed_eur,
       sum(overdue_eur) AS overdue_eur,
       round(100 * sum(overdue_eur) / sum(billed_eur), 2) AS overdue_pct
FROM invoice_facts
GROUP BY month
ORDER BY month;

SELECT round(100 * sum(overdue_eur) / sum(billed_eur), 2) AS overdue_pct_total,
       round(avg(month_pct), 2) AS average_of_monthly_pct
FROM (SELECT period,
             sum(overdue_eur) AS overdue_eur,
             sum(billed_eur) AS billed_eur,
             100 * sum(overdue_eur) / sum(billed_eur) AS month_pct
      FROM invoice_facts
      GROUP BY period);

┌─────────┬───────────────┬───────────────┬─────────────┐
│  month  │  billed_eur   │  overdue_eur  │ overdue_pct │
│ varchar │ decimal(38,2) │ decimal(38,2) │   double    │
├─────────┼───────────────┼───────────────┼─────────────┤
│ 2026-06 │         92.40 │          0.00 │         0.0 │
│ 2026-07 │       1751.10 │         66.70 │        3.81 │
│ 2026-08 │       1750.70 │        118.10 │        6.75 │
│ 2026-09 │       1964.35 │          0.00 │         0.0 │
└─────────┴───────────────┴───────────────┴─────────────┘
┌───────────────────┬────────────────────────┐
│ overdue_pct_total │ average_of_monthly_pct │
│      double       │         double         │
├───────────────────┼────────────────────────┤
│              3.32 │                   2.64 │
└───────────────────┴────────────────────────┘

Over the four months, 3.32% of the billed amount is overdue. Averaging the four monthly percentages gives 2.64%, because the tiny June (one invoice of €92.40) counts as much as a full month. "Overdue %" must therefore be defined as total overdue divided by total billed, recomputed for every selection, which is exactly what a measure does. Note also that "overdue" here means the status KBS sets, not "due date before today": another definition that belongs to the business and must be written down.

The same measures in two BI tools. Neither tool is installed here, so these are not run:

Billed amount = SUM ( Invoices[amount_eur] )

Overdue amount =
CALCULATE ( [Billed amount], Invoices[status] = "overdue" )

Overdue % = DIVIDE ( [Overdue amount], [Billed amount] )

view: invoices {
  sql_table_name: analytics.invoices ;;

  dimension: status {
    type: string
    sql: ${TABLE}.status ;;
  }

  measure: billed_eur {
    type: sum
    sql: ${TABLE}.amount_eur ;;
  }

  measure: overdue_eur {
    type: sum
    sql: ${TABLE}.amount_eur ;;
    filters: [status: "overdue"]
  }
}

DIVIDE in DAX returns an empty result instead of an error when the denominator is zero, which matters for a month without invoices. Both definitions live in files or models that can be reviewed and versioned, unlike a formula typed into one cell of one workbook.

Defining KPIs

A dashboard is only as good as the definitions of its numbers. A key performance indicator (KPI) is a measure that someone has chosen to steer by, so it needs more than a formula. Femke Jansen, head of billing, owns the billing KPIs; before the dashboard is built, each one gets a short definition like this:

kpi: active_contracts
title: Active contracts
owner: Femke Jansen (head of billing)
question: How many contracts do we supply at the end of the period?
definition: >-
  Contracts whose start_date is on or before the last day of the period,
  whose end_date is empty or on or after that day, and whose customer
  exists in the customer table.
grain: contract
excludes: contracts without a known customer (counted and reported separately as a data-quality issue)
source: analytics store, table contracts (nightly load after the 02:30 KBS export)
refresh: daily, after the nightly load
unit: count
target: none (a volume, not a goal)
changed:
  - date: 2026-10-06
    note: first written definition; replaces the "no end date" count in the KPI pack

A useful KPI definition answers:

• What question does it answer, and which decision does it support?
• Exactly how is it calculated, including edge cases (contracts starting tomorrow, unknown customers, credit notes)?
• Who owns it and may change it, and where are changes recorded?
• Where does the data come from, and how fresh is it?
• Is there a target or threshold, and is higher better or worse?

Reports, dashboards and drill-down

A report in a BI tool is a designed set of one or more pages of visuals, tables and filters built on one model, for a known audience and purpose, often interactive and sometimes paginated for printing. A dashboard is the at-a-glance view: one screen with the few numbers and charts someone checks regularly. In Power BI's own vocabulary a dashboard is specifically a single page of tiles pinned from one or more reports; other tools use the words more loosely.

Margriet's dashboard follows the advice from Choosing the Right Chart and Honest and Accessible Visualization: a top row of four single numbers (active contracts, kWh delivered, billed amount, overdue amount), each with last month for comparison; below them billed per month as columns from zero and kWh per tariff as sorted bars; and a "data as of" line under the title.

Drill-down

Drill-down is moving from a summarized number to the more detailed data behind it, along a hierarchy such as year → month → day or tariff → contract → invoice, by clicking on the number or a bar. When Margriet sees €118.10 overdue for August, she clicks on it. The tool keeps the filter (August, overdue) and generates a more detailed query, roughly this one:

SELECT c.tariff, i.contract_id, i.invoice_id, i.amount_eur, i.due_date
FROM invoices i
JOIN contracts c USING (contract_id)
WHERE i.period = DATE '2026-08-01'
  AND i.status = 'overdue'
ORDER BY i.contract_id;

┌──────────┬─────────────┬────────────┬───────────────┬────────────┐
│  tariff  │ contract_id │ invoice_id │  amount_eur   │  due_date  │
│ varchar  │   varchar   │  varchar   │ decimal(10,2) │    date    │
├──────────┼─────────────┼────────────┼───────────────┼────────────┤
│ variable │ CT-20009    │ INV-40021  │         49.85 │ 2026-09-15 │
│ variable │ CT-20014    │ INV-40025  │         68.25 │ 2026-09-15 │
└──────────┴─────────────┴────────────┴───────────────┴────────────┘

Two contracts, and one of them, CT-20014, belongs to a customer who does not exist in the customer table. The drill-down took Margriet from a KPI to a data-quality problem in two clicks. Drill-down only works if the model holds the detail and the hierarchy, and it shows only rows the viewer is allowed to see.

Refresh and freshness

A scheduled refresh is a BI service's automatic re-import of an imported model's data from its sources at set times, so that reports show new data without anyone pressing a button. It is the moment the copy gets updated, and between refreshes the copy does not change, however much the source does.

Refresh must come after the data it depends on. At Kestrel the night runs like this:

The refresh is scheduled half an hour after the load's deadline, not at the moment the load usually finishes, because a refresh that runs before the load ends simply copies yesterday's data again, without any error. Power BI's limits (as of October 2026) are worth knowing when planning: a semantic model on shared capacity (a Pro license) can have up to 8 scheduled refreshes a day, and one on Premium, Premium Per User or Fabric capacity up to 48; a refresh on shared capacity must finish within two hours.

Access control

A BI tool collects data from many systems in one place, which makes it attractive to anyone curious and a target for attackers. Access has three layers:

• Who can open the content. Workspaces, apps or folders with roles (in Power BI: Admin, Member, Contributor and Viewer). Readers get viewer rights only. Margriet's access is requested as service request REQ-6134 "Access to the BI dashboard for Margriet de Wit" and approved by Femke Jansen as owner of the billing data, the normal route for a request that needs approval.
• Which rows they see. Row-level security (RLS) in the model filters the data per user or role, for example a future account manager for business customers who should see only business contracts.
• What they can take away. Export to Excel or CSV, "analyze in Excel" workbooks, subscriptions by email: each can leave copies of the data in files and mailboxes outside the model, where its security rules and later corrections no longer apply.

[segment] = "business"

Read the fine print of each tool. Power BI's documentation, for example, says that row-level security "only restricts data access for users with Viewer permissions": members and contributors of the workspace see all rows. Give report builders their own workspace, and readers only viewer access to the published app.

Self-service BI

Self-service BI is an approach in which business users build their own reports and analysis on data that IT or a data team has prepared, instead of asking a specialist for every new view. Done well, it removes a bottleneck: Daniel Mensah, head of customer service, can answer "did bill-related calls rise after the September invoices?" himself instead of waiting two weeks.

Done badly, it recreates spreadsheet chaos in a new tool. The difference is in a few rules:

• Shared, approved models. Users build reports on a small number of maintained semantic models with agreed measures, not on their own uploads of the same data.
• A clear line between personal and official. Anyone may explore; only reviewed reports are published to the wider organization, with an owner and a description.
• Housekeeping. Reports nobody opened in six months are archived; duplicate models are merged. Most BI services show usage statistics for exactly this.
• Training, including the honest-chart basics, because the tool will happily draw a truncated axis.