Database::BI business intelligence for data files

Nine D3 chart types, a map and a cloud

Charts

Each chart is a page of its own, addressed by URL, drawing from the same pipeline as the table it came from. A chart page takes the view it was opened from — the source, joins, filters, calculated fields and time range — so the chart and the table behind it always agree.

The chart pages

Every page takes the pipeline parameters described under Common parameters.
RouteDrawsIts own parameters
/graph
/line		 Zoomable line chart with brush-to-zoom, reference lines for the minimum, average and maximum, and a multi-series legend. x, y, series, span, agg
/bar Bar chart, horizontal or vertical, sorted by value or label, with a top-N tail merged into one bar. cat, val, orient=h, sort, max
/pie A pie or donut; clicking a slice or legend entry opens the rows behind it. cat, val, donut=1
/scatter Scatter plot, with bubbles sized by a numeric column and coloured by a category. x, y, series, size, scheme
/heatmap Grid with two categorical axes; each cell is a sum or a row count, in a named colour scheme. x, y, val, scheme, show_val=1
/stacked Stacked bars: one bar per category, split by a series column. cat, series, val, scheme
/combo Bars and a line on one pair of axes, each with its own measure column; a scheme gives the bars its first colour and the line its second. cat, bar_val, line_val, scheme
/treemap Tiles sized by a measure, grouped by a label column, with a top-N tail merged. Tiles can instead be shaded by a second measure — size for sales, colour for margin — with a legend for the scale. group, size, topn, scheme, color, color_agg (default avg), color_scheme
/histogram Histogram with server-side binning, so the counts are the data's counts rather than a client's sample. col, bins (1–200, default 10), scheme (bar colour)
/wordcloud Word cloud of a text column. Each row contributes its words once, so a word's size is the number of rows using it, not the times one row repeats it. col, topn, minlen, stop
/map OpenStreetMap map with clustered markers, an optional heat layer, bubbles and colour rules. see Maps
/pivot Crosstab of rows against columns with one or more measures, subtotals and a grand total. see Pivot
/stats One row per column: count, nulls, distinct values, first, last and, for numeric columns, min, max, range, mean, median, a chosen percentile and the sample standard deviation. l, f, p
/insights Automatic observations: outliers by the Tukey fences, period-over-period deltas, top movers and seasonality. No model and no network call. l, f

Common parameters

Every data page and chart takes the same selection of the pipeline:

l=<spec>
The left source: table:name from the data directory, path:/absolute/file.csv, a remote path /../host/dir/file.csv, url:, redis:tablename|redis://host:port, or mariadb:server/table.
j=<spec>|<leftkey>|<rightkey>
A left join, repeatable and chainable.
c=name:expression
A calculated field, added as a virtual column before any aggregation.
f=<col>:<op>:<value>
A filter, repeatable, applied after joins and before the chart aggregates.
span=1h&tcol=at
Bucket the dimension into time buckets before drawing.
range=last_30_days&rcol=date
A window resolved when the request arrives, so a link keeps its meaning tomorrow. See relative time ranges.
cs=, by=, order=
A cross-row plan: a running total, rank, z-score and so on, written beside each row. See cross-row columns.
back=, back2=
Where the breadcrumb goes. Only a same-site path or an http(s):// URL is followed.

Maps

GET /map plots the filtered view with HTML::OSM and Leaflet. A row's position comes from a place column that is geocoded (loc=, one lookup per distinct value, in the local data first and then on OpenStreetMap's Nominatim, remembered in the model store and shared by every dataset that names it) or from coordinate columns (lat= and lon=). Rows sharing a position become one marker. The map opens on every marker, unless zoom= says otherwise, and has a scale bar. A map coloured by series=, color= or size= draws its key on the map, listing only the colours of the markers in view, so it changes as the map is zoomed and moved. When some rows cannot be plotted, the note that says so counts them by reason, links to all of them in the table, and opens onto a list of their places: how many rows each one stands for, why it was not plotted, and a link to those rows.

Places that are not in the cache yet are looked up after the page is shown. The map draws what the cache knows, and a progress bar names each place as it is looked up, such as Looking up 5 of 10: 10 Downing St, London. Each answer is saved at once, and the map is drawn again when the last place is in. Cancel stops the lookups and draws the map with the places found so far. The rest are listed as not looked up yet, with a link to look them up. geocode=wait looks every place up before the page is sent, which is also what a client that is not a browser gets, and geocode=cached looks nothing up.

loc=<col>
A column of places to geocode. Repeat it for a place written across columns: loc=Address&loc=Country looks up each row's address and country joined with a comma, leaving out an empty cell. The map panel's Followed by field does the same.
popup=<col>
A column shown in the marker popup; repeatable. By default the popup shows every column but the place. A marker stands for every row at its place, and its popup lists them: a value they all share once, the rest as a small table. Past ten rows (map_popup_rows) the table scrolls no further; the rest are counted, with a link to them in the table.
max=N, zoom=N
How many markers to draw (default 2,000) and the initial zoom; by default the map fits its markers.
tiles=name
A named tile source from the tile_sources configuration key, for a tile server on your own network.
heat=1, weight=<col>
A heat layer over the markers, optionally weighted by a numeric column.
size=<col>
Markers become circles with a radius from a numeric column.
color=<col> with color_rule=op:value:colour
Colour markers by thresholds, tried in order, first match wins; the operators are gt, ge, lt, le, eq and ne. Unmatched values take color_default=, shown in the legend as "Other".
series=<col> with scheme=
Colour by category from a named palette instead of by rules.

Markers are clustered, always, so a dense map draws a count at low zoom and splits it as it is zoomed in. The Nominatim search box is switched off on every map page, so nothing typed on a map is sent anywhere. An unknown tiles=, an unknown scheme=, an unknown column, color= together with series=, or a transposed latitude and longitude pair is a 400 rather than a silently different map.

Resizing and exporting

Every chart can be resized by dragging its bottom-right grip, or from the keyboard: the grip takes focus, the arrow keys resize the chart, Shift takes bigger steps, Home and End go to the narrowest and widest it can be, and the size is announced as it changes.

Each chart page carries Copy link and Export SVG, Export PNG and Export PDF. The PDF is a one-page document the size of the chart, holding a high-resolution picture, so it needs no PDF library on the server. Saved files carry "Database::BI" at the bottom right, and use the light colours even when the reader has chosen the dark theme.

A chart is drawn in the browser, so what an export contains can only be seen in a browser. That is also why a chart on a canvas can be filtered again: it is a live snippet rather than a picture.

Time buckets

The aggregating routes bucket the dimension they already take, so a daily series is a request rather than a calculated field: 

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

span= takes a count and s, m, h, d, w, mon, q or y. Labels are ISO at the bucket's own resolution, so they sort chronologically as strings. A value that cannot be read as a time lands in one visible "(unparseable)" bucket rather than disappearing.