Datasets and cubes

The model layer of Xeplr Analytics. It covers datasets, nested datasets, filters and parameters, cubes built on a local DuckDB replica, and how compiled SQL is kept safe.

A dataset

A dataset is a graph of tables drawn on a canvas. It has tables, the joins between them, columns with optional aggregates, formula columns, ranking columns, filters and parameters. You define it once, and reports and dashboard widgets read from it. The number is computed in one place, so two dashboards cannot disagree about it.

A dataset reads from a saved connection and database, or from a cube (sourceCubeId). The canvas preview runs the SQL capped at 50 rows. It checks the shape of the result and is not an export.

One compiler, two places

@xeplr-bi/query-engine runs in the browser to show a query and on the server to build the only query that is ever executed. The API takes a saved report or dashboard id plus the reader’s filter values. It never takes SQL text for a saved document, and it discards filter values for filters the document does not declare. A preview cannot disagree with a real run, because only one compiler exists.

Datasets are wrapped, never edited

Datasets are shared, so a report never changes one. Each stage adds a subquery around the previous one:

1. dataset SQL (buildSql)          never edited
2. + DB formulas     wrapped       SELECT *, <expr> AS "alias" FROM (…) AS report_computed
3. + pushed filters  wrapped       only filters provably equivalent to the in-memory ones
4. + named params    bound         :month, :start_date, :<filter tag> …
5. + DB filters      wrapped       SELECT * FROM (…) AS report_dbfiltered WHERE …

Placeholder numbering carries across every stage. Without that, $1 or @p0 emitted twice would bind the wrong value, and MySQL’s positional ? would hide the bug.

Nested datasets

A dataset can use another dataset as a source. Nested datasets compile bottom up.

• A cycle is refused when you save, before the save runs. Otherwise it would show up later as a stack overflow at compile time.
• Depth is capped (10 by default), and a too-deep chain gets a clear message instead of a database timeout.
• A nested dataset must use the root’s source. A dataset on another connection is reported as a cross-connection problem. A cube counts as its own source (cube:<id>), so two cube datasets with no connection id do not compare as the same source.

Filters

@xeplr-bi/engine defines what a filter is, once, for reports, dashboards and cube builds.

• Filter logic combines filters by tag: date and (region or channel). An unknown tag is reported as a typo. A broken expression returns an error and compiles to no WHERE at all. It never falls back to joining every filter with AND, because that would be a different report.
• An incomplete filter is skipped, never guessed at. A tag with no usable value is dropped, so an unset filter inside OR cannot match the whole table.
• The date filter always exists and cannot be cleared. Its value resolves from the session, then the report default, then the workspace default, then the current month. Without a date, a query would scan all history. It always compiles to a range, and several bound date columns are joined with AND.
• A reader’s filter choice is never saved into the report. Only defaults are stored. Dashboards remember a reader’s choices per board in that browser.
• LIKE text matches literally. User % and _ are escaped before the operator’s own wildcards are added, so “50%” means “50%”.

Report row filters (eq, contains, in, empty and the rest) run in the report engine after the query. The engine pushes a filter into SQL only when the SQL result can keep more rows than the in-memory filter, never fewer. ne, notContains and notEmpty are never pushed, because SQL <> drops NULLs and the in-memory filter keeps them.

Parameters

Parameters are written into dataset SQL as :name and bound as placeholders.

• The financial year starts in April by default. The start month is an explicit input, because hardcoding it gets the year wrong for most of the world.
• An unresolved :param stays in the SQL text and is reported, so the database errors instead of running a different query. An unresolved parameter inside a per-column filter value drops that filter, because a filter that matches nothing looks like a successful empty report.
• An unanswered prompt parameter is returned as missing so the caller can ask for it. It is never silently ignored.
• A quoted ':name' stays as text and is reported, because it is almost never intended.

Dialects

Reports and dashboards

Grouping, pivots, subtotals, top-N, “show as” and streaks are computed by @xeplr-bi/report-engine, the only implementation. It runs in the browser in design mode and on the server over a full database stream. No SQL version exists, because dialects disagree (an MSSQL AVG over an integer truncates) and a second implementation would let design mode and production show different numbers.

• Totals are accumulated from raw rows, never summed from displayed rows, because an average of averages is not the average.
• Memory grows with distinct cells, not rows. The server stops at 50 MB of estimated memory and answers 413.
• Long runs return an id at once. Progress arrives over server-sent events.

A dashboard model joins several datasets. Its columns are always qualified by model dataset (d1__amount), and only the smallest connecting set of datasets is joined. A dataset that cannot be reached is reported, because joining it would produce a cartesian product.

Cubes

A cube is a pre-aggregated copy of a table at a grain you choose. Use one when a source table is too large to query live.

Two stages

1. Replicate. Each source table is copied as-is into the workspace’s local DuckDB warehouse. This step is expensive, network-bound and touches production.
2. Build. The cube is built from the local copies. This step touches nobody and takes seconds.

The split keeps the cube builder interactive. Changing a dimension rebuilds from local data instead of re-scanning production.

Build is the only action a cube needs. It brings the cube’s tables up to date and then rebuilds. A finished copy triggers a rebuild of every cube that reads that table, so jobs schedule the copy, never the build. A cube never rebuilds while a table it reads is still being copied.

The spec

A filter column costs what a dimension costs. To filter a cube by region, region has to be in the grain. The one exception is a build-time filter.

Rollable and stored measures

A cube stores additive slots, not the measures you asked for.

A customer active in two regions is one distinct customer by month and two by month and region. A distinct count is exact at the grain it was built for and cannot be added up. The builder lets you choose it and shows why. A widget that needs it at a coarser grain is not planned onto the cube and is not silently dropped.

Sizing a cube

Cardinality is the lever, not column count. Five low-cardinality dimensions turn 10M rows into 20k. Add one order_id and the cube is the size of the table.

The builder profiles every column without scanning production:

A sampled figure is a floor. Treat it as a floor, because calling it “about” is how a cube gets built far larger than its estimate.

The size verdict waits for an exact count on Review. The estimate multiplies cardinalities as if the columns were independent, and real columns are not. On a real five-table canvas it read 5,006,421 rows (“not worth building”) for a cube whose true size was 3,156,890. So the design steps show no size figure. Review shows the estimate bar, and gives a verdict only after Count it again runs the exact GROUP BY. That count is affordable because it reads the local replica: five million rows took 731ms. The verdict is not worth building under 2x compression, ok under 20x, and good above that.

Physical order is the partitioning

DuckDB has no table partitions. It has row groups with per-column min/max, so a range filter skips groups it cannot match. That means you arrange physical order yourself. Measured on 20 million rows, filtering one month out of 36:

• Sort locally, never at the source. An ORDER BY in the query that reads production forces the sort onto the production box.
• Do not add an index for this. An index serves point lookups, not range aggregates over a columnar table.
• Send high-cardinality drill-down to the replica, not a cube. A cube keyed on a 5,000-value employee id came out at 19.1M rows from a 20M source. The same filter on the replica plus a date range took 0.6ms.

SQL safety

The server compiles a dataset in strict mode, with the connection’s dialect:

A filter operator that is not on the list throws (Unsupported filter operator) instead of being skipped. Operators are written into SQL and saved configuration arrives through an API, so = 1 OR 1=1 -- would otherwise be stored SQL injection. A skipped filter would return more rows than were asked for.

Queries on source databases run over read-only connections: previews, samples, the report grid and report and dashboard aggregation. PostgreSQL and MySQL enforce this in the driver. SQL Server does not, so give Xeplr Analytics a login with only db_datareader on source databases. See Security.

Last updated 16 September 2026