/ 
Database-BI-0.018.0
/ Database::BI::Insights

NAME

Database::BI::Insights - Automatic statistical insights for Database::BI (Phase 5.2)

SYNOPSIS

use Database::BI::Insights;

my $insights = Database::BI::Insights::compute(
    records  => \@rows,
    columns  => \@cols,
    spec_key => 'table:sales',
    base_url => '/insights?l=table:sales',
);

DESCRIPTION

Pure-arithmetic insight detection. No LLM, no external service calls. Detects four classes of insight:

outlier

A value beyond the Tukey IQR fence (Q1 - 1.5*IQR or Q3 + 1.5*IQR).

period_delta

Overall period-over-period delta between the most recent two month-periods visible in a date column.

top_mover

The dimension member with the largest period-over-period absolute change. Requires at least one categorical column and one date column.

seasonality

Detected via lag-1 autocorrelation on monthly totals. A coefficient >= 0.6 is flagged. Requires at least 8 distinct month-periods.

Every insight includes a view_url that links to the chart or stats view showing the underlying data. An insight without a clickable view_url is a bug.

FUNCTIONS

compute(%args)

my $insights = Database::BI::Insights::compute(
    records  => \@rows,     # arrayref of row hashrefs
    columns  => \@cols,     # arrayref of column names
    spec_key => $l,         # l= spec string
    base_url => $url,       # request URL, used only as a fallback
);

Returns an arrayref of insight hashrefs. Each hashref has:

kind      -- 'outlier' | 'top_mover' | 'period_delta' | 'seasonality'
headline  -- one short sentence
detail    -- numbers that substantiate the headline
view_url  -- URL to the view showing the data
method    -- key for method_note()

API SPECIFICATION

INPUT

{
    records  => 'arrayref',  # row hashrefs, already filtered and with calculated fields
    columns  => 'arrayref',  # column names, in order
    spec_key => 'string',    # optional; the l= spec (default '')
    base_url => 'string',    # optional; default "/insights?l=<spec_key>"
}

OUTPUT

[ { kind => 'string', headline => 'string', detail => 'string',
    view_url => 'string', method => 'string' }, ... ]

No rows or no columns returns []. Outliers need a numeric column. Period-over-period, top movers and seasonality also need an ISO YYYY-MM-DD date column; only the first one found is used. Every insight has a view_url. The function is pure arithmetic: it makes no network call and does not croak.

method_note($key)

Returns a one-sentence description of the algorithm behind $key.

API SPECIFICATION

INPUT

$key   # 'iqr_fence' | 'pop_delta' | 'top_mover' | 'autocorr'

OUTPUT

'string'   # the note; an unknown key is returned unchanged

ALGORITHM NOTES

Outliers

Tukey IQR fence: Q1 - 1.5*IQR and Q3 + 1.5*IQR. Requires at least 4 values per group. Results are sorted by distance from the nearest fence; up to 5 outliers are reported per column.

Period-over-period

Year-month (YYYY-MM) buckets from ISO-8601 date columns. Requires at least 2 distinct periods.

Seasonality

Lag-1 autocorrelation r(1) on the sequence of period sums. Formula: r(1) = cov(y[t], y[t+1]) / var(y[t]). Threshold: r >= 0.6. Requires at least 8 distinct month-periods.

AUTHOR

Nigel Horne <njh@nigelhorne.com>

LICENCE AND COPYRIGHT

Copyright 2026 Nigel Horne.

Usage is subject to the GPL2 licence terms.