Data

Where data can come from

Recognised extensions are csv, psv, tsv, sql, sqlite, sqlite3, db, xml, xlsx and JSON. A file whose columns cannot be identified is read as header-less and given synthetic column names rather than silently losing its first row.

A MariaDB or MySQL server is opened by name. Add it to database_bi.conf; the home page then has a card that lists its tables, and mariadb:shop/orders opens one anywhere a source is taken.

{
    mariadb_servers => {
        shop => {
            host     => 'db.example.com',  # default: localhost
            port     => 3306,              # optional
            database => 'shop',            # required
            username => 'bi_reader',
            password => 'secret',
        },
    },
}

The spec holds only the name, because a spec travels in URLs, shared links and saved canvases. The password stays in the configuration file, so keep that readable only by the account that runs the application. Give the server an account with only SELECT: anyone who can reach the application can open any table that account can read.

The pipeline

The stages run in a fixed order, so every view over the same parameters sees the same rows.

1. Open and join. The left source is read; each j=<spec>|<leftkey>|<rightkey> appends columns from another table, keeping every left row. c=<spec> stacks further files under the result on a unified column set.
2. Normalise dates, look up and compute fields. A stored date pattern turns a column into ISO-8601; a stored lookup adds columns from a second file, matched on one key; a stored extraction pulls columns out of another with a regular expression; calculated fields (c=name:expression) become virtual columns; named groups become virtual <col>_group columns.
3. Narrow. Filters (f=col:op:value) and a relative time range (range=, rcol=) are applied after the joins and before anything is aggregated.
4. Add cross-row columns. A running total, rank or z-score is written beside each row, after filtering and before pagination — so page two continues page one's running total.
5. Aggregate and draw. The chart, pivot, statistic or table sees the finished rows; SQL aggregation is used where the source can do it, and Perl otherwise.

Filters

GET /view/sales?f=region:eq:North&f=amount:gt:100

Operators

eq, ne, contains, starts, lt, le, gt, ge, empty, notempty. String comparisons are case-insensitive. The four order operators compare numbers, reading $1,234.56 and (1,234.56) as numbers too; a blank or a word in the cell matches none of them.

Dates

When the filter value is an ISO YYYY-MM-DD date, a cell written M/D/YYYY is normalised before the comparison, so a link works against either spelling.

Chips

Each active condition is a chip in the toolbar with its own remove button, so conditions can be dropped one at a time. A relative range is one chip, not two frozen dates.

Relative time ranges

Any data view, chart, pivot or canvas can be limited to a named window worked out from today rather than from dates typed into a link:

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

The vocabulary is:

last_24_hours
today, yesterday
last_7_days, last_30_days, last_90_days
this_month, last_month
this_quarter, last_quarter
this_year, last_year

The window is resolved when the request arrives and applied as two ordinary filters on that column, col:ge:lo and col:le:hi, so the table, every chart, the pivot, the statistics, the insights, the exports and a saved canvas all show the same rows. Filters you wrote are untouched. Entries whose window falls entirely outside the data are disabled in the filter bar, with the column's earliest and latest value in the tooltip.

An unknown range= is a 400 rather than a silently unfiltered view, and so is a range with no rcol=.

Calculated fields

A virtual column defined by an expression, either inline (?c=name:expr) or stored against the dataset with POST /calc/save. Expressions support arithmetic, comparison, IF, string, numeric and date functions, plus bin(value,width) for histogram grouping.

The timestamp functions — epoch(v[,fmt]), hour(v), minute(v), second(v) and span(v, interval) — are how a time bucket can be written by hand when the URL has no single dimension for span= to use.

Named groups and date patterns

A named group maps many raw values onto one label for a column, producing a virtual <col>_group column that charts and pivots can group by. A date pattern tells the application how a column's dates are written — %m/%d/%Y, %d/%m/%Y or %d-%b-%Y — and, with a time part, normalises the column to a timestamp that epoch(), span() and last_24_hours can read. Both are stored per dataset in models.json and managed from panels on the data pages. The group column appears in the table as well as in its charts and exports.

Stored lookups

A lookup adds columns to every row from a second file, matched on one key: a shop code in the orders gains the shop's city and region. It is stored per dataset and applied on every route, the export and a canvas, before calculated fields and filters, so both can read the new columns.

POST /lookups/save  l=table:orders&name=shops&file=lookups/shops.csv
                    &key=code&col=shop&fields=city,region&default=unknown

The file must be a data file under data_dir, of at most 8 MiB; a URL, a remote path and a symlink leading outside are refused. key is the lookup file's column and col the dataset's (the same name when left out). Keys are compared as text after trimming spaces, and case matters. The first row with a key wins, and the log says when a key repeats. A key that is not in the file gets default, or nothing. A field whose name the dataset already has is added as <name>_<field>, so a source column is never overwritten.

nolk=<name> skips a stored lookup for one request, and lk=<name> applies one stored for another dataset. A lookup is not a join: for more than one key, or a row that matches many rows, use Merge. The table page's Lookups panel saves, lists and deletes them, and its Skip in this view adds the nolk=.

Field extraction

A regular expression can pull values out of a column. For one value, use rx() in a calculated field: c=country:rx(ref,'^([A-Z]{2})-','1'). To make several columns from one pattern, store an extraction; each named group becomes a column:

POST /extract/save  l=table:invoices&name=parts&col=ref
                    &pattern=^(?<country>[A-Z]{2})-(?<year>\d{4})-

It applies everywhere a lookup does, after the lookups. A row the pattern does not match gets (unparseable), so it groups visibly instead of vanishing. A pattern over 200 characters, one that does not compile, and one using (?{, (??{, (?p or (* are refused. That is not a sandbox: regular expressions have no timeout, so a pathological pattern stalls its own request. Patterns are entered by whoever runs the server. The table page's Extract fields panel saves, lists and deletes them.

When a saved column cannot be worked out

A file can change under the definitions saved for it: a lookup file is deleted, or a column an extraction or a calculated field reads is dropped. The charts and exports of that dataset then refuse, with the reason. The table page is still drawn, without the dataset's saved calculated fields, lookups and extractions, and with a note that names the one at fault and opens the panel that corrects or deletes it. A mistake in the request's own c= is still a 400.

A refused request is answered with a 400 and the reason in plain text. When a browser asked for the page, the reason is shown on a page with the breadcrumb back to the table and a Back button instead; scripts and the JSON API still get plain text or JSON.

Cross-row columns

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

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

An unknown function or column, or a parameter where none is taken, is a 400.

Cross-row columns and calculated fields can build on each other. A calculated field can read the new column, as in c=half:ytd/2, and cs= can read a calculated field or a time bucket, as in span=1mon&tcol=at&order=at_span&cs=amount:running_total:ytd. Filters run before the pass, so a window is over the rows they leave. A filter that names a column the pass writes runs last instead: f=ytd:gt:50 selects rows without changing their totals.

Pivot and statistics

/pivot builds a crosstab from row dimensions, column dimensions and one or more measures:

GET /pivot?l=table:sales&rows=region,rep&cols=quarter \
           &m=amount:sum:total&m=amount:median:typical&totals=1

Measures

sum, avg, min, max, count, count_distinct, median, stdev, first, last, range, and p<N> from p1 to p100 for a percentile.

Totals and limits

totals=1 adds subtotals and a grand total; topn= with topn_col= keeps the top row groups; max_cols= collapses the tail of column combinations into "Other".

Exports

format=csv|sqlite|json|xlsx streams the table as a download.

/stats reports the same measures per column — count, nulls, distinct, first, last, and for numeric columns min, max, range, mean, median, a percentile and the sample standard deviation — computed by the same functions the pivot uses, because a median is not the sum of the medians of its parts.

Getting data out

GET  /export?...&format=csv        # RFC 4180, UTF-8, CRLF
GET  /export?...&format=sqlite     # one table, named "data"
POST /export  l=...&dir=/tmp&filename=out.csv

The export is the current logical view, after joins, filters and deduplication, not the raw file. The download's name comes from the left table's label. JSON is available from the pivot and from a canvas card, and XLSX from the pivot.

Limits worth knowing

• Read-only: cell editing and write-back are not implemented.
• Only a page of an unfiltered MariaDB table is read with LIMIT. A filter, Merge, Combine or an export reads the whole table into memory, so open a very large one through a view that narrows it.
• A MariaDB table or view named with anything but letters, digits and underscores cannot be opened. Its card shows a CREATE VIEW that gives it a usable name.
• Joins happen in memory, with a spill to a temporary SQLite file when the result is large; a join of files that do not fit in memory is not the use this is built for.
• A relative range compares the column as a string, like every other filter. A timestamp with an offset is read as far as its YYYY-MM-DD prefix, so last_24_hours is exact on an ISO YYYY-MM-DDTHH:MM:SS column.
• Upload staging is managed: subdirectories older than 24 hours are evicted at startup, and "Clear upload cache" empties it immediately.
• Only the first worksheet of an XLSX workbook is read.