/ 
Database-BI-0.017.1
/ README.md

Name

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

Version

0.017.1

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

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' }

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.

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 saved analysis, and turns a crossed threshold into a non-zero exit status so cron can act on it:

./script/bi-alert --analysis ID [--threshold MEASURE:OP:VALUE] \
                  [--data-dir DIR] [--model-dir DIR] [--dry-run]

The digest goes to STDOUT and nothing is sent: pipe it into mail, a webhook or a ticket creator if the alert has to leave the machine. Exit status is 0 when no threshold is crossed, 1 when one is, and 2 on error. There is no delivery to suppress, so --dry-run is accepted for compatibility only and changes neither the output nor the exit status. The script keeps no state.

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.