Build a dataset in the Dataset Builder

A dataset is an analysis-ready table: named dimensions and measures defined over a data model, compiled into SQL and materialized as a physical table in the analytics warehouse, then rebuilt on a refresh schedule. Build a dataset when the question is analytical — aggregation over history, at warehouse scale, feeding many consumers such as dashboard widgets. For a plain operational listing of live records, an ordinary report is the better tool; the dataset earns its keep when one governed definition should serve everything downstream.

Prerequisites

• The Dataset Builder page in reach. It is an application page, not a Setup screen: it ships in the standard Analytics app's navigation (after Reports and Dashboards) and an administrator can add its route, /analytics/datasets, to any other app as a Feature item.
• A built, published, active data model to bind to — the dialog offers only models that are published and active. A dataset can instead name a warehouse table directly as a fallback source, but a bound dataset gets root-source guidance, column pick lists and schema-aware validation, so bind when you can.
• A reachable analytics warehouse. Compile refuses to run when the warehouse connection is unavailable, so an organization without a provisioned warehouse can author datasets but not materialize them.

Procedure

Step 1 — Create the dataset shell

Open Dataset Builder and choose New Dataset in the page header.

123456 Expand

1. 1

   Name is the permanent internal identifier (letters, digits, underscore) and seeds the default warehouse table name; Label is what pickers show.

2. 2

   Published Data Model lists only published, active models and prefills the fallback table and grain; Fallback Source Table is the escape hatch when no model exists.

3. 3

   Grain states what one output row represents — every dimension and measure is judged against it.

4. 4

   Refresh Type and Visibility are required but currently descriptive: every refresh is a full rebuild and nothing filters on visibility yet.

5. 5

   Refresh Schedule is a five-field cron evaluated in UTC; blank means manual-only and a malformed expression is skipped silently.

6. 6

   Warehouse Table names the materialized output; blank derives Dataset_<Name> in the platinum schema.

Choose Create. The dataset appears in the table with its Binding state (Bound when a model was chosen, otherwise Unmapped) and Last refresh Never run. Clicking the name reopens this dialog for editing; the definition itself is authored in the workspace.

Step 2 — Open the workspace

From the row, choose Open Workspace.

12345 Expand

1. 1

   Compile validates, generates SQL and rebuilds the warehouse table; Validate and Preview do the same checks without touching the warehouse.

2. 2

   Guardrail banner: this dataset uses a fallback table, so authoring is free text — bind a published data model to get root-source and column pick lists.

3. 3

   Sources, Dimensions, Measures, Filters and Security each hold one collection with its count; Refresh History lists the latest 50 runs.

4. 4

   Overview echoes the shell: model or Source Fact, Grain, Refresh Type and Schedule, Warehouse Table, Active — the first place to check when a refresh never runs.

5. 5

   Edit reopens the shell dialog; Delete removes the definition (the warehouse table is not dropped).

The workspace header carries Edit, Delete, Compile, Validate and Preview; the tabs are Overview, Sources, Dimensions, Measures, Filters, Security and Refresh History, each showing its count. A guardrail banner above the tabs tells you what kind of authoring help you have:

• Data Model guidance enabled — the dataset is bound: the model's root object is offered as your root source and its published columns appear as pick lists. A second warning appears if your root source table does not match the model's root.
• Dataset is not bound to a Data Model — "authoring remains mostly free text": no pick lists, no schema-aware validation; compile-time SQL errors become your first line of defence.
• Data Model schema is unavailable — the model is bound but has not been built yet, or its schema could not be loaded. Build the model first.

Step 3 — Define the sources

On Sources, add the tables the dataset reads. The first source is the root — it has no parent alias — and exactly one root is allowed (dataset.sources.root.multiple). For a bound dataset, Use Bound Root Source fills the table name and the alias fact from the model. Each further source declares Table Name, Alias, Join Type (Inner, Left, Right, Full), the Parent Alias it joins to, a Join Condition in warehouse SQL and a Display Order; a non-root source without a join condition fails validation (dataset.sources.join.required).

Step 4 — Define dimensions

On Dimensions, add the columns to group by. Each dimension has a Column Name (or an Expression instead), a Display Name, a Group By Order, a Time Dimension flag and Visible. On a bound dataset, Use Data Model Column prefills the column from the model's published schema — use it: column names are validated against the model's output columns, not the operational entity's field labels (dataset.expression.column.missing). Dimensions become the grain of the output: every distinct combination of dimension values is one row of the compiled table.

Step 5 — Define measures

On Measures, add the numbers. Each measure has a Measure Name, an Aggregation — Sum, Count, Average, Min, Max, Count Distinct, or Expression for a fully calculated measure — an Expression (a column or warehouse SQL over the model's output columns), a display Format, Display Order, and the Calculated and Visible flags. A dataset with no dimensions and no measures compiles to nothing useful and validation warns you (dataset.outputs.empty).

Step 6 — Add filters, and security rules if needed

Filters are SQL predicates ANDed into the build — rows they exclude are genuinely absent from the compiled table, which makes filters the hard scoping tool. Each has a Filter Expression, Display Order and Active flag.

Security rules declare per-role row scoping: a Role Id, a Row Filter Expression such as SalesPersonId = @userId, order and active flag (dataset.security.role.required if the role is missing). They appear in the Preview's Security tab and travel with the definition, but they are not injected into the compiled table's SQL — the materialized table contains every row the filters allow — and no dashboard or report applies them when reading the table today. Their intended use and the consequences are covered in Row-level security on datasets.

Step 7 — Validate and preview

Choose Validate. The Dataset Validation dialog reports Validation passed or Validation found blocking issues with an error and warning count and each finding's code and message. Errors block compilation; warnings never do, so read them as review items. The label is required (dataset.label.required); a bound model must still be published and active (dataset.data-model.unpublished / inactive); a missing warehouse target is only a warning (dataset.warehouse-target.missing) because the name is derived.

Then choose Preview. The Dataset Preview dialog has four tabs — Columns, Filters, Security and SQL Preview — showing the projected output, the active predicates, the declared security rules and the exact SQL that compile will run. Preview does not execute anything against the warehouse; reconcile the SQL with the written grain and confirm no join can duplicate rows before compiling.

Step 8 — Compile

Choose Compile. The dataset is validated again — any error stops the compile with the findings and no history row is written — then the SQL is generated and the warehouse table is dropped and recreated from it. The toast Dataset compile triggered confirms the request was accepted; the outcome lands in Refresh History as Success with a row count and duration, or Failed with the error message (an unreachable warehouse fails the run with a connectivity message).

Step 9 — Confirm the refresh cycle

Open Refresh History (columns Started, Completed, Status, Rows, Duration, Error; latest 50 runs) and verify the newest run is Success and its row count matches an independent grain-level expectation. If you set a Refresh Schedule, convert the UTC cron to the business timezone and confirm that the first scheduled rebuild appends a new successful entry rather than trusting the saved expression — an invalid one never fires and never complains. The scheduling rules and statuses are in Refresh scheduling and monitoring.

Expected result

Validation has no errors, the previewed SQL matches the declared grain, and Compile creates the intended warehouse table with plausible dimensions, measures and row count. Refresh History records the manual build and, if scheduled, a later scheduled full rebuild. The dataset is offered in the dashboard designer's dataset source picker and in Dashboards → From Dataset.

Common problems

Validation keeps failing on column references. Expressions are checked against the bound data model's output columns — the names the model publishes, not the operational entity's field labels. Open the model's Columns tab and use its output column names verbatim; the pick lists in the dimension and measure dialogs exist precisely to prevent this mismatch.

Compile is refused and no run appears in history. Validation failed — a compile only starts, and only writes a history entry, once validation passes. Run Validate, fix the errors it lists, and compile again.

Compile fails with a connectivity error. The analytics warehouse was unreachable; nothing was built. The failed run stays in history with the message; retry later.

The dataset never refreshes on its own. The three silent causes: the dataset is inactive, the schedule is blank (manual-only is valid), or the cron expression is invalid — an unparseable schedule is skipped without an error anywhere. Fix the expression and the next due occurrence runs. The dataset's creator receives an in-app and email notification on the first failed scheduled refresh, but a refresh that never starts sends nothing.

Binding shows Unmapped or Ambiguous on the list page. The Dataset binding status panel is for datasets created before models existed, or whose model link was lost. Refresh Binding Check re-evaluates every dataset's fallback source table against published, active models; Apply Matched Bindings writes the model link only for rows whose status is matched and that have no model yet. Ambiguous means more than one model claims the same table — bind that dataset by hand through Edit.

Common questions

When should I build a dataset instead of a report?

Build a dataset when the workload is analytical: pre-aggregated numbers over large or archived history, refreshed on a schedule, consumed by several dashboards at once. Use a plain report for operational listing and grouping of live records — it is immediate, needs no modelling, and reflects this minute's data. The dividing question: should this definition be computed once and shared, or asked fresh each time?

What is the difference between Full and Incremental refresh?

In current releases, none in behaviour — every refresh is a full rebuild: the table is dropped and recreated from the model's data each run, which is what guarantees the dataset exactly matches its definition. The refresh type is stored with the dataset and shown in the list, but it does not change how a refresh executes, so size your schedules assuming full rebuilds.

What does Visibility do?

Today it is descriptive. Private, App and Shared are stored and shown in the list and the Overview, but no surface filters on them: the dashboard designer's dataset picker and From Dataset list every active dataset. Use the setting to record intent, and use the Active flag when a dataset must actually disappear from pickers.

Can a dataset skip the data model and read a table directly?

Yes — the Fallback Source Table exists for exactly that, and the workspace banner will tell you authoring is "mostly free text" in that mode: no root-source guidance, no column pick lists, no schema-aware validation. Treat it as the escape hatch, not the pattern; the governed path is a published data model, for the reasons in Data models and materialization.

Related topics

• Data models and materialization
• Row-level security on datasets
• Refresh scheduling and monitoring
• Troubleshooting Data Studio
• Dashboards in xMatix