Designing the cube

The query behind a dataset is not a query you run. It is a query that runs itself, on a schedule, with nobody watching, for as long as the dashboard exists. That changes what a good one looks like.

Measure correctness covers whether the numbers mean what you think. This page covers the shape around them.

Grain

The grain is what one row of the result represents, and it decides everything the dashboard can do.

The rule with no exceptions: anything you want to filter on, chart on, or break down by has to be a column the statement outputs. A field that is not in the output does not exist as far as the dashboard is concerned, and nothing later puts it back. You cannot filter by plan on a dataset whose statement never outputs plan.

How it gets into the output is the half that forks.

Against a warehouse connection or the uploaded-file source, it is a plain column of the records you return. Return one row per underlying record and declare each number in the spec rather than working it out in the SQL, because Dashies holds those rows and works the numbers out per request. Grouping to what the dashboard reports at hands it a summary to summarize.

Grouping to the thing each row is about is a different move and is fine: one row per order, carrying count(line_items.id) as lines for that order. Declare lines as a total rather than as a count, because your SQL has already done the counting and from Dashies' side that column is now just a number to add up. Measured on six line items across three orders, the same column comes back as 6 declared as a total and as 3 declared as a count.

If you are porting a dashboard that already groups, the test is: does the calculation give the same answer when it is done twice? A total does. A smallest does. A largest does. A count does not, and it is the one to watch, because it is the number most likely to have been worked out already. Measured on those same six line items, a total returns 6 at either grain while a count returns 3 grouped by order and 2 grouped by region.

Against the built-in self connection, it is set by your GROUP BY. The published page carries its numbers rather than going back for them, so the grain the statement returns is the grain the dashboard reports at, and a column that was aggregated away is gone.

The inverse is also true and easier to get wrong: every column the query outputs is carried. On a cube, lattice or hybrid dataset an undeclared column is refused at publish rather than quietly included, because it would be bytes every viewer of the dashboard can read and nothing renders. Those three are in-file materializations; see Datasets and the four modes.

Where the statement is row-shaped this is a warning, not a refusal, and the publish proceeds. That is the case to watch, and a held-shape dataset is always in it, because its statement returns records; on the in-file shape it is a rows slice and a hybrid's row-level half. So a clean publish is not evidence that no stray column is being carried: read the warnings. On the in-file shape those bytes are row-level and readable by every viewer, which is the more exposed half of the rule.

A dataset needs at least one dimension. For a single all-time KPI, bucket by date anyway: it keeps the total exact and gives you a trend for free.

The two shapes can also disagree for reasons that are not double-aggregation. Connect SQL Server measures one: under a case-insensitive collation your server folds acme and ACME into one group, and Dashies, which compares text by its bytes, keeps them as two.

Keep dimensions low-cardinality

Every filter renders as a menu of a dimension's distinct values, on either shape. A dimension with 5,000 values gives you an unusable dropdown, and on a lattice it multiplies the cell count until the dataset is refused outright.

Row count is where the two shapes part. On the in-file shape, aim for a few hundred to a few thousand rows in a dataset, not tens of thousands: those rows ship inside the file and share one budget with every other inline dataset. On the held shape the records themselves are what you return, so the row count is whatever the records are, bounded by what the extract may carry between refreshes rather than by anything at publish. The ceilings are in Limits. Do not coarsen that grain to hit an inline number, because that is the pre-aggregation this page opens by warning about.

For a genuinely high-cardinality category, the standard move is top-N plus other: keep the values that carry the volume, fold the rest into a single Other bucket in SQL, and drop the long tail from the grain. That is a design decision to make before you write the query, not an edit afterwards, because the remedy for a dataset that turns out too wide is usually to split it across several narrow datasets rather than to trim one. It works on either shape, because it rewrites a column's values at the grain you already have rather than collapsing rows.

Bucket dates in the business timezone

This section is the same on either shape. Bucket timestamps to the period the dashboard reports on: day, week, or month. Never ship a raw timestamp as a dimension.

Do the bucketing in the SQL, in the business timezone. A refresh runs with no session timezone, so a bare date_trunc('month', ts) buckets in UTC, which shifts every month and quarter boundary, and shifts by an hour across a daylight-saving change.

In PostgreSQL there is an operand trap worth knowing, because the wrong form is silently wrong rather than an error:

-- A `timestamp with time zone` column: the single form is correct.
select date_trunc('month', created_at at time zone 'America/Los_Angeles')::date as month,
       count(*) as signups
from users
group by 1
order by 1

-- A naive `timestamp` column storing UTC: label it UTC first, then convert.
select date_trunc('month', created_at at time zone 'UTC' at time zone 'America/Los_Angeles')::date as month,
       count(*) as signups
from users
group by 1
order by 1

Both examples group, because bucketing a timestamp is what they are about. On the held shape the same conversion goes in the SELECT list as a column of the records, with no GROUP BY under it.

The single form applied to a naive timestamp mis-buckets without complaining. Check the column's type before choosing; introspection reports it.

Other engines have their own spellings: BigQuery takes the zone as a third argument to timestamp_trunc, Redshift and Snowflake use convert_timezone, Databricks uses from_utc_timestamp, and SQL Server needs the double AT TIME ZONE cast back to datetime2.

Dashies checks what it can see and warns without blocking: a single AT TIME ZONE is reported as ambiguous, since it is right for one column type and wrong for the other, and a dataset that declares a timezone its SQL never names is reported as bucketing in some other zone. A column that is already a DATE needs no conversion at all, and publishing does not warn about one: Dashies asks the warehouse for the type of every value a bucket reads, and stays quiet when each is a DATE taken from a DATE column, including through an aggregate or a CTE. Where it cannot establish that, such as a bucket over a date cast from a timestamp, the warning stays. A day bucket written as a cast of a timestamp, such as date(ts), is not checked at all, so convert the timestamp to the business timezone before you cast it.

ORDER BY on every in-file dataset

This section is about the in-file shape, where the order the rows arrive in is the order tiles draw in, so the ORDER BY in your statement is the one that decides it. The held shape is at the end of the section, and the short version is that this does not apply there.

Write an explicit ORDER BY on every in-file dataset, in every mode.

This one deserves emphasis because nothing warns you. There is no publish error for a missing ORDER BY, so a wrong order looks deliberate.

Most tiles draw a dimension's members in the order the rows arrive. Without an explicit ordering, the axis order, a pie's slice order, a filter menu's order, and which members survive a limit on a matrix or heatmap are all whatever the engine happened to produce, and they can move between refreshes with nothing in the spec changing.

Two assumptions that would let you skip it are both wrong. A date dimension is not always sorted for you; some tiles treat it exactly like a category, a month filter menu among them. And changing the dataset mode does not excuse you either: on a lattice or hybrid, only the filter menu moves off the SQL order onto the declared value list.

There is one case where it is enforced rather than advised. A windowed row slice must end in a top-level ORDER BY, and it is rejected at publish without one, because the window means "which rows" and there is no such thing without an order. Give it a unique tiebreak, so two rows with the same timestamp cannot swap between refreshes:

select created_at, account_id, amount
from transactions
where created_at >= current_date - interval '90 days'
order by created_at desc, id desc

The ordering has to be the result order. An ORDER BY inside a subquery, a CTE body, or a window function does not count. A windowed row slice is an in-file shape too: a held-shape dataset is refused a row window rather than bounded by one.

On the held shape, your statement's ORDER BY is not what a tile draws. Dashies queries the rows it holds once per request and orders that answer itself, so ordering is decided there rather than in your SQL. Bound the row set with a WHERE window instead; that is what carries over.

Write it to survive a year

• One read-only SELECT per dataset. Not multiple statements, no DML, no DDL. This is enforced by the executor, not merely requested, on either shape.
• Relative time windows, never hardcoded dates. A hardcoded window is correct on the day it is published and progressively more wrong afterwards, and nothing will tell you. What the window is relative TO forks. On the held shape, anchor it to the data's own newest complete period rather than to the wall clock: take the anchor from (select max(<date>) from <source>) and measure the window back from there. Excluding the partial newest period is a second predicate, not something the lower bound does for you. A mart's data ends at its last complete period, so a wall-clock window is short by that gap every day, and on a source that has stopped being rebuilt it drains to nothing while every number left on the page stays plausible. The wall clock is only the right anchor where the source is genuinely live, which self, Dashies' own metrics view, is: current_date - interval '12 months'.
• Bound the result. On the in-file shape the executor refuses an oversized result rather than truncating it, so a query that grows past its ceiling starts failing rather than starting to lie. That is the right behaviour, and it is still a broken dashboard, so leave headroom. On the held shape nothing at publish bounds it for you: the seed is deliberately limited there and a short read is the intended outcome rather than a truncation, so what binds is the extract's own ceilings. Both sets are in Limits.
• Leave anything sensitive out of the statement's output. That is the rule on either shape and only the mechanism differs. On the in-file shape the data is embedded verbatim in the file, so every viewer gets all of it whatever the tiles show or the filters hide, and that is stricter rather than looser on a row-level dataset, where every column you select is shipped as-is. On the held shape the rows stay with Dashies, and every column the dataset declares is one the page can ask Dashies for, so every reader can still reach it. Its viewers are you, or your workspace's members - never the public - but that is an audience, not a filter: no personal data, no raw rows you would not hand that audience, and no cells small enough to re-identify someone.
• Row-level security does not change that bullet. On an Enterprise workspace a held-shape dataset can filter its rows per viewer by one declared column, which never hides a column from somebody entitled to any row at all. It decides who gets which rows; it is not a way to carry a field nobody should see. See Row-level security.
• Aggregating the sensitive thing away is an in-file remedy. On the held shape it is the pre-aggregation this page opens by warning about, so drop the column, or coarsen its values at record grain, rather than collapsing the statement to a summary.

Designing a lattice specifically

A lattice is an in-file materialization, so a held-shape dataset cannot carry one and this section is about the in-file shape alone. It adds two shape rules on top of everything above:

• The CUBE arguments must be plain columns, named exactly as the dimension keys. Bucket or derive a dimension in an inner query first, then group by the resulting column. This keeps the powerset portable across engines and sidesteps their disagreements about grouping by an alias.
• Every dimension must declare its bound: a value list for a category, a bucket count for a date. That declaration is what makes the size predictable before any SQL runs.

Keep the grand-total cell. If a HAVING clause or a filtered source strips the row where everything is rolled up, the unfiltered dashboard boots blank, and the publish will tell you so.

Next

Connections and scope is where the query runs.