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. Nor are rows lost when a column is mostly empty: a bank export whose cheque-number column is filled in only now and then still shows every transaction, and a filter such as is not empty on that column picks out the cheques. A UTF-8 byte-order mark at the start of a file, which Excel's CSV UTF-8 writes, is read past, so it never becomes part of the first column's name.

A JSON file is a table when it is a list of objects, an object of objects (each key a row, in a column id), or an object holding lists of objects, as a scan report or an API reply does: {"tool": "scan", "findings": [...], "hosts": [...]}. Each list is a table, so a file with more than one opens on the Tables page with a card for each, as a SQLite file with several tables does. Values beside the lists, such as a date or a count, are left out. A cell holding an object or a list shows as its JSON text, and the columns keep the order the file gives them.

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. Year:between:1880:1900 keeps the rows from 1880 to 1900, both included, whichever bound is written first; the filter row offers between for number columns as well as dates.
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, between, empty, notempty, and in, any value of a JSON list (f=place:in:["Paris","Rome"]), which a map uses to link to the rows it could not plot. 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. between takes lo:hi in either order: two numbers compare as numbers, two dates or timestamps as times, anything else as text.

Mistakes

An operator that is not one of these (they are lower case), and a filter on a column the view does not have, are a 400 whose message lists the operators or the columns; they used to keep every row, or none, as if the filter had worked. A canvas card's own saved filter on a column its dataset no longer has is ignored instead.

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. The filter bar offers Time range only when the table has a column of dates or dates and times, and its column menu lists only those columns.

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.

Print, to the right of Refresh on a table, prints the same rows: the header row and every row of the view, not only the page on screen, with the columns in the order the table shows and sorted as it is sorted. Nothing else on the page is printed, and the header row repeats on each printed page. A row deleted from the screen with Delete selected is still printed; a deleted column is not. A view of more than 10,000 rows asks before it is laid out.

POST /export saves into a folder on the server, so it keeps to file_roots and export_dirs when they are set (and export_dirs must be set with a password), never into a dot-folder, and never over a link or a folder of that name. See Which folders it may use.

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.
• An ISO timestamp is read as written: a fraction of a second, a trailing Z or an offset such as +01:00 is accepted, and the zone is not applied. A non-ISO timestamp is read as far as its YYYY-MM-DD prefix, so last_24_hours is exact on ISO timestamps in the server's zone.
• 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.