/ 
Database-BI-0.018.0
/ README.md

Name

Database::BI - Web-based Business Intelligence viewer for data files

Version

0.018.0

Description

Database::BI is a self-contained Mojolicious web application that reads and views arbitrary data (CSV, PSV, TSV, SQLite, JSON, XML, XLSX, etc.) and presents them as styled, sortable, reorderable HTML tables. It has no persistent database of its own, it reads your files on every request.

Users navigate to pick a file, open the filesystem browser to navigate anywhere on disc, drag-and-drop files to upload, join multiple tables in memory, apply server-side filters, and export results as CSV, SQLite, JSON, or XLSX. Nine D3.js v7 chart types, a pivot/crosstab table, per-column statistics, and multi-visual saved canvases are available from the toolbar.

A tutorial in plain English is built into the application at /tutorial. It is written for people who do not know Perl, and it is the place to start.

Key features:

UTF-8 and Encoding

All file data is returned as Perl character strings. CSV/PSV/TSV files are read by Text::xSV::Slurp or DBD::CSV, both of which pass bytes through without re-encoding; the application serves the resulting page as text/html; charset=UTF-8, so full Unicode is displayed correctly as long as the source file itself is UTF-8.

Filter values (f=col:op:val) are decoded from the URL by Mojolicious before reaching the controller and are compared against cell values as Perl character strings. Unicode characters in filter values are therefore supported transparently.

URL-imported HTML tables are fetched with LWP::UserAgent::Cached; Database::Abstraction uses the page's own charset declaration to decode the body. If the remote page declares an incorrect charset, cell values may contain mojibake -- this is a limitation of the source data, not the application.

Synopsis

Start the development server (restarts automatically when you edit a file):

script/database-bi

morbo script/database-bi does the same thing, and script/database-bi daemon starts a plain server with no file watcher; any other Mojolicious command word (routes, get, ...) is passed to the application.

Start the production server:

hypnotoad script/database-bi

Use a different data directory:

# In database_bi.conf (create this file in the same folder as script/):
{ data_dir => '/home/user/data' }

Change the language used for templates:

# In database_bi.conf:
{ data_dir => 'data', language => 'fr', platform => 'web' }
# Then create templates/web/fr/ and put your French .html.tt files there.

Run the test suite to verify everything is working:

make test

Generate the Makefile for the first time or after editing Makefile.PL:

perl Makefile.PL

Routes

Every page shows where it is: Home, then each page between Home and this one, then this page, which is not a link. The trail is read from the page's own URL -- a chart's back=, drilled rows' back2=, a file's folder -- and each step is named from its own URL ("Bar chart: amount by region", "sales (region = North)"). Text from the URL is shown only if it is short and has no markup, a back= to another site or scheme is not followed, and a chain of back= links stops after eight steps.

An address that matches no route, a missing table (/view/:table) and a missing canvas (/a/:id) answer 404 with an HTML page that keeps the breadcrumb and the site navigation. The JSON API (/api/...) still answers a 404 with a JSON body.

Relative Time Ranges

Every data route accepts a named window in place of a pair of dates:

GET /view/sales?range=last_30_days&rcol=sale_date
GET /a/mycanvas?range=this_quarter&rcol=order_date

range= names the window and rcol= names the column it applies to. The window is worked out when the request arrives, so a link, a bookmark or a saved canvas keeps meaning the same thing whenever it is opened rather than the dates it happened to resolve to when it was written. The vocabulary is

last_24_hours   the 24 hours ending now, to the second
today, yesterday
last_7_days, last_30_days, last_90_days     including today
this_month, last_month
this_quarter, last_quarter
this_year, last_year

A this period ends today; the completed one is last_month, last_quarter and so on.

The range is applied as two ordinary filters on that column -- col:ge:lo and col:le:hi -- so the table view, every chart, the pivot, the column statistics, the insights page, the exports and a saved canvas all show the same rows, and one chip in the toolbar names the window with an x to remove it. Filters the reader wrote are untouched: the range travels in the URL as itself, so submitting a panel form or reopening a link does not turn it into frozen dates.

A window narrower than a day (last_24_hours) is compared to the second, so it is exact only on a column that holds a time as well as a date. A day-granular window compared against a column of timestamps covers each day it reaches in full, which is what a date filter does on its own as well.

The filter bar offers the same windows: choose one and the column it applies to. Entries whose window falls entirely outside the data are disabled, with the column's own earliest and latest value -- from GET /api/range -- in the tooltip, so a window that can only show nothing says so before it is chosen. An unknown range= is a 400 rather than a silently unfiltered view, and a range with no rcol= is a 400 as well.

On a saved canvas the range narrows every card, and a card added while one is active is drawn through it: query in POST /api/analyses/:id/visuals carries range=/rcol= as it carries cf=.

Time Buckets

The aggregating routes bucket the dimension they already take:

GET /bar?l=table:sales&cat=at&val=amount&span=1h
GET /pivot?l=table:sales&rows=at&m=amount:sum:total&span=1mon
GET /view/sales?span=1d&tcol=at
GET /graph?l=table:sales&x=at&y=amount&span=1h&agg=sum&series=region

The chart and pivot panels carry a Time bucket menu for it, and the line graph panel an Aggregate menu beside it.

span= takes a count and one of s, m, h, d, w, mon, q or y -- 15m, 6h, 2d, 3mon, 1q -- and is refused, naming the interval and the parameter, when the count is zero or the unit is not one of those. It expands into the calculated field a reader could have typed, <col>_span:span(<col>,'1h'), so there is one pipeline and one set of semantics; tcol= names the column where the route has no single dimension or names more than one, and as= names the bucket column. tcol= without a span=, an unknown column, a name that is already a column and a span= that disagrees with the reader's own calculated field are each a 400.

Labels are ISO at the bucket's own resolution -- 2026-10-27T14:00:00, 2026-10-27, 2026-10-01 -- so they sort chronologically as strings and a bucketed chart comes out in the right order with no second sort. d, w, mon, q and y are computed from the local calendar, so a daylight-saving day is one bucket and a leap day is its own date; s, m and h floor the interval out of the epoch. A value that cannot be read as a time lands in one visible (unparseable) bucket rather than disappearing.

agg= on /graph is the timechart: one point per bucket, aggregated with the measure vocabulary a pivot's m= uses (sum, avg, min, max, count, count_distinct, median, stdev, first, last, range, p<N>). Without agg= every row is still plotted.

The same bucket is available by hand, because it is a calculated field: ?c=hour:span(at,'1h'). The timestamp functions are epoch(v[, fmt]), hour(v), minute(v), second(v) and span(v, interval); see Database::BI::Calc for the shapes each reads and the local-time rule.

Cross-Row Columns

Every data route, /export and a saved canvas accept a cross-row plan: a column worked out from many rows and written beside each one.

cs=<column>:<function>:<alias>[:<count>]   repeatable
by=<column>[,<column>...]                  partition (optional)
order=<column>[:asc|desc]                  the order the rows are read in

pct_of_total, rank and zscore need no order. running_total, running_count, running_pct, window_sum, window_avg, lag, delta and pct_change need order=; only window_sum and window_avg take the count, the width of the window. The pass runs after the filters and before pagination, so page 2 continues page 1's running total. An unknown function or column, or a parameter where none is taken, is a 400. The table page's Cross-row panel builds the same parameters. See "Cross-row columns" in Database::BI::Controller::Dashboard for the full rules.

Configuration

Place a database_bi.conf file in the application root to override defaults:

{
    data_dir => 'data',   # directory scanned for data files on the home page
    uploads_dir => '.uploads',  # staging directory for uploaded files
    platform => 'web',    # VWF template dimension
    language => 'en',     # VWF template dimension

    # Browser-side libraries.  d3_url overrides the chart and canvas
    # script; the leaflet_* keys override the map assets, and tile_url
    # overrides the tile server.  See "Self-hosting D3 and Leaflet"
    # below.
    d3_url => 'https://cdnjs.cloudflare.com/ajax/libs/d3/7.9.0/d3.min.js',
}

uploads_dir is where POST /upload stages the files a user drags onto the page, and the directory POST /uploads/clear empties. It defaults to .uploads under the application home. A relative value resolves against the application home, an absolute one is used as given, so a deployment whose application directory is read-only can stage uploads somewhere writable:

{ uploads_dir => '/var/tmp/database-bi-uploads' }

Compression and the Page Cache

Every response goes through Mojolicious::Plugin::Buffer: it is compressed (gzip or Brotli, as the browser accepts), HTML whitespace is minified (inline scripts and styles are left alone), and a 200 to GET or HEAD carries an ETag, so a repeat request can be answered 304 Not Modified. File downloads (/export as CSV, SQLite or JSON, the pivot as XLSX, and a canvas card's data from /a/:id/visual/:n/:fmt) opt out and are sent as they are.

Two optional keys keep finished pages on disc:

{
    page_cache_dir      => '/var/cache/database-bi',  # relative: under the application home
    page_cache_duration => '5 minutes',               # the default
}

When page_cache_dir is set, /catalog and /tutorial are stored in a CHI File cache there, one copy per Accept-Language, and served from it until they expire. No other page is cached, because the rest read data files that can change between requests. Without the key nothing is cached.

Self-Hosting D3 and Leaflet

The chart pages load D3 from a public CDN by default, and the map pages load Leaflet and leaflet.markercluster from unpkg. A deployment with no outbound network access, or one that prefers to serve its own assets, sets the URLs in database_bi.conf:

{
    d3_url                          => '/static/d3/d3.min.js',
    leaflet_js_url                  => '/static/leaflet/leaflet.js',
    leaflet_css_url                 => '/static/leaflet/leaflet.css',
    leaflet_cluster_js_url          => '/static/leaflet/leaflet.markercluster.js',
    leaflet_cluster_css_url         => '/static/leaflet/MarkerCluster.css',
    leaflet_cluster_default_css_url => '/static/leaflet/MarkerCluster.Default.css',
    tile_url                        => 'https://tiles.internal/{z}/{x}/{y}.png',
}

An absolute path is served like any other static file: put the library under the application's public/ directory, so /static/d3/d3.min.js is public/static/d3/d3.min.js. The five Leaflet keys may be set on their own; any left out keeps HTML::OSM's pinned CDN URL. Use a path beginning with /, or a full URL; a path without the leading slash is relative to the page. The versions to copy are the ones the defaults name: D3 7.9.0, Leaflet 1.9.4, and leaflet.markercluster 1.5.3. tile_url points the map layer at a self-hosted tile server instead of OpenStreetMap. It keeps Leaflet's {z}, {x} and {y} placeholders, and {s} too if the server uses subdomains. Set it and every request needed to draw a map can stay on the local network. The OpenStreetMap attribution stays on the map, since it is the usual credit for OSM-derived tiles. There is no in-page place search: HTML::OSM is always constructed with search => 0, so nothing typed on a map page is sent anywhere.

Map-specific config keys: map_geocode_max (new geocoder lookups per request, default 500), map_max_markers (markers per request, default 2000), map_region (a two-letter country hint for ambiguous places), map_max_zoom (the default tile-layer maxZoom) and tile_sources (a hashref of named tile sources, each { url => ... }, selected with tiles=).

Map colour rules. color= names the column and color_rule= supplies <op>:<value>:<colour> rules; they are tried in the order given and the first match wins. The operators are gt, ge, lt, le, eq and ne; a comparison is numeric when both the cell and the rule's value look like numbers, and otherwise only eq/ne are meaningful (case-insensitive). A colour is #rgb, #rrggbb or a CSS name such as red or teal. A value that matches no rule gets color_default=, which the legend shows as "Other". series= is the categorical alternative: each distinct value takes the next colour from scheme=, cycling through the palette, and the legend lists the categories. color= and series= are mutually exclusive.

Methods

Startup

Called once by Mojolicious when the application is loaded, by script/database-bi, morbo, hypnotoad or Test::Mojo->new('Database::BI'). It is not called directly.

Api Specification

Input
    $self   # the Database::BI application object

Configuration comes from database_bi.conf in the application home, if it is there (see "CONFIGURATION"), or from the hashref given to Test::Mojo->new('Database::BI', { ... }). Unset keys default to data_dir => 'data', platform => 'web', language => 'en' and the public D3 URL.

Output

Nothing useful is returned. By the time it returns, the application has:

A configuration file that does not parse makes the Config plugin croak, and the application does not start.

Command-Line Tools

Two maintenance scripts ship in script/; both use the same data and model directories as the web application and are safe to run from cron.

Script/Bi-Geocode

Warms the shared geocode cache for one column, so later map requests make no lookups:

./script/bi-geocode --spec table:customers --col city \
                    [--data-dir DIR] [--model-dir DIR] \
                    [--openaddr PATH] [--region CC] [--dry-run] [--verbose]

It is idempotent: a second run against a warm cache resolves nothing and says so. Exit status is 0 on success, 1 when the column is missing or no rows load, and 2 on bad arguments or an unopenable source.

Script/Bi-Alert

Reports one or more saved analyses, and turns a crossed threshold into a non-zero exit status so cron can act on it:

./script/bi-alert --analysis ID [--analysis ID ...] \
                  [--threshold MEASURE:OP:VALUE] \
                  [--threshold ANALYSIS:MEASURE:OP:VALUE ...] \
                  [--suppress DURATION] [--state-file PATH] [--recovery] \
                  [--notify-cmd COMMAND] \
                  [--data-dir DIR] [--model-dir DIR] [--dry-run]

The digest goes to STDOUT and nothing leaves the machine unless --notify-cmd says where: otherwise pipe it into mail, a webhook or a ticket creator yourself. Exit status is 0 when nothing is announced, 1 when a threshold is crossed and announced, 2 on error, and 3 when a crossing that was announced has ended and --recovery was asked to announce that. --dry-run reports without recording anything against the throttle and without running the delivery command.

Both of those options may be repeated, so one run can watch several analyses and several measures:

./script/bi-alert --analysis abc123 --analysis def456 \
                  --threshold count:gt:1000 \
                  --threshold def456:sum:lt:50 --suppress 1h

A threshold with no analysis prefix belongs to the first --analysis; the ANALYSIS:MEASURE:OP:VALUE form names another of those listed. Each analysis is reported in full, and each (analysis, threshold) pair is a check of its own: it prints its own line, keeps its own entry in the state file, and is throttled and recovered on its own schedule. One crossing never silences, recovers or delivers another. When a run announces both a crossing and a recovery it exits 1, so a threshold that is still crossed is not reported as an all-clear.

--suppress throttles a threshold that stays crossed, so a condition that is still true on the next cron run is not reported as news again: it takes a duration (30s, 15m, 6h, 2d, 1w, or a bare number of seconds), the first crossing is announced, and repeats inside the period are not. The throttle is one small JSON file, alerts-state.json in the model directory unless --state-file names elsewhere, keyed by analysis and threshold and written atomically. It is kept only when --suppress is used or such a file already exists, so with no --suppress the script is as stateless as it has always been and writes nothing.

A run inside the period prints THRESHOLD SUPPRESSED and exits 0: it is not announcing anything, so a cron wrapper keyed on the exit status stays quiet. When the crossing ends the run prints THRESHOLD CLEARED once and drops its entry, which means the next crossing is announced immediately instead of waiting out a period that belonged to an incident that is over. A state file that cannot be read is reported and treated as empty, so a damaged file costs one repeated alert rather than silence, and --dry-run records nothing, so a rehearsal cannot use up the alert a real run depends on.

--recovery announces that ending instead of merely reporting it: the run prints THRESHOLD RECOVERED and exits 3, so a wrapper that mails on any non-zero status mails the all-clear as well as the alert, and a wrapper that should not is a wrapper that does not pass --recovery. The recovery goes through the same state file, which is what makes it possible: an entry is the record of a crossing that was announced, so finding one is what tells the run that an incident has ended rather than that a threshold was never crossed. It is therefore said once per crossing and only for a crossing somebody was told about, which is also why --recovery keeps that file even with no --suppress to throttle. The recovery names both dates -- crossed since and cleared.

--notify-cmd delivers the announcement itself, so a cron line is one command rather than a shell wrapper around this one:

./script/bi-alert --analysis abc123 --threshold count:gt:1000 --suppress 1h \
    --recovery --notify-cmd 'mail -s "$BI_ALERT_SUBJECT" oncall@example.com'

The command runs once per run, through the shell, and only when something is announced: a crossing (exit 1), or its end with --recovery (exit 3). A suppressed repeat, an uncrossed threshold, an error and a --dry-run run it not at all, and twenty checks still send one mail. The whole run's text arrives on standard input, and three variables say what it is -- BI_ALERT_EVENT (crossed if any crossing was announced, otherwise recovery), BI_ALERT_SUBJECT (the verdict line when one thing was announced, otherwise a count such as 2 threshold crossings, 1 recovery) and BI_ALERT_MESSAGE_FILE (the same text in a file, for a command that has to post a body: curl -d @$BI_ALERT_MESSAGE_FILE ...). A delivery that fails is reported on STDERR and leaves the exit status alone, but the announcements are not recorded, so the next run announces them again rather than the alert being lost for the rest of the period.

script/database-bi is the web application launcher: run with no arguments it starts the reloading development server, with a command word it runs that command, and hypnotoad script/database-bi starts a preforking server (see "SYNOPSIS").

Common Pitfalls

Limitations

Roadmap

Features planned for future releases (post-0.018.0). Items are ordered by priority. Items shipped before this release (XLSX export, the copy-link button, the column statistics panel, folder filter/join propagation and the aggregate/measure layer) are no longer listed.

See Also

Repository

https://github.com/nigelhorne/Database-BI

Support

This module is provided as-is without any warranty.

Author

Nigel Horne <njh@nigelhorne.com>

Copyright 2026 Nigel Horne.

Usage is subject to the GPL2 licence terms. If you use it, please let me know.