Skip to main content

Datasets

A dataset is the unit of "load this data into one table": its rows are stored in Cloud Connector under <schema>.<dataset> and exported to the warehouse under the same name.

The Datasets page and the dataset rows​

Datasets in the sidebar lists every dataset of the environment with Dataset (a from <parent> chip marks a nested dataset), Source System, Schema, Status (the last load: queued, running, success, warning, error, cancelled; the tooltip has the message), Last Loaded At, Records (the number of non-deleted rows in storage; Many when the collection is too large to count quickly), Enabled and actions: Refresh now (a Stop icon while a load is in flight), Lineage, View log and Preview loaded data. View log in the header opens the Dataset log, and New Dataset leads to the Source Systems page, where datasets are created.

The status is the worst of each instance's newest load, so one failed instance keeps the dataset red until that instance loads again. Warning means the rows are in storage but something else went wrong: an export failed, a full load met duplicate business keys, a nested dataset did not load cleanly, or a Gateway load had failed tables.

The Datasets section of an expanded source system lists the same datasets with Name, Business Key, Unpack, Export (an Export on / off / inherited chip), Status (here the enabled switch) and actions: Refresh now, Copy API trigger / webhook URL, View log and Edit. Editing, deleting and dropping a collection happen here.

Refresh a dataset​

Refresh now opens the Load Data dialog:

  • Instance — all instances, or one.
  • Full or Incremental — only the kinds the dataset has endpoints for; a Gateway-fed or nested dataset offers both.
  • Sync to — one tick box per export destination. Untick one to leave that destination untouched on this run only, for example while reworking a dataset.
  • Recent Jobs — the last ten loads with their type (Full, Incremental, Field rename, Instance rename, Export, AI import), rows, duration and error, and for each queued or running one a Remove from queue or Stop this refresh control.

Queue Load queues the load; the dialog refuses when a load for the same dataset and instance is already queued or running. A refresh started by a person passes a disabled source system, instance or dataset; jobs, schedules and trigger URLs do not.

Stopping a load. A queued load is removed at once. A running load stops at the next safe point, after the page or export chunk in flight: records already loaded are kept, deleted-at-source detection and the export are skipped, and the next incremental refresh still starts from the last completed one, so nothing is missed once you run it again.

Create or edit a dataset​

Datasets are normally created from the Data catalogue (see Data catalogue), which fills in the unpack path and business key from a preview. Add Dataset in a source system's Datasets section creates one by hand, and Edit on a row opens Edit Dataset with these tabs: Edit dataset, Calculated fields, Field names, Pre-load delete, Export data, Indexes and, when the loaded rows hold an object or a list, Nested datasets.

Edit dataset tab​

  • Name — at most 50 characters, unique within the source system and within the schema. On edit the field is locked: unlocking and renaming moves the loaded collection and the exported tables, and can break references to the dataset.
  • Description.
  • Unpack — the JSON path where the records are in the response, for example value or data&&items. For an Excel dataset, the sheet name.
  • Business Key — the field(s) that identify a record, && between parts. Rows with the same key are updated, not duplicated. The instance is factored into the record id automatically, so it is not part of the key.
  • Add request URL on each row (_url) — store the URL each row came from.

Export data, Indexes, Nested datasets and the other tabs are described below. The footer has Delete, Drop collection (the loaded data only; it also clears the dataset's load history, so the next incremental load starts from the beginning), and Save / Close.

Delete asks whether to drop the loaded collection too (ticked by default), then removes the dataset with its endpoints, headers and parameters, unbinds its on-prem tables and takes it out of relationships and Data access roles. A dataset with nested datasets cannot be deleted until they are. Exported tables stay in the warehouse.

Field names​

Store a field under a different name than the source uses: a mapping of Source field → Stored as, applied before calculated fields and the business key, so those reference the new name. The source-field dropdown offers the fields of rows already loaded; a field can be mapped once, and names starting with _ are reserved.

While nothing is loaded, a change only affects future loads. On a dataset with loaded data, saving a change also renames the stored rows, rebuilds indexes on the field and re-exports; loads are paused and the dataset is locked until that finishes. Business Central custom endpoints use this tab to store col1…col50 under their real names.

Calculated fields​

Derive extra fields on every loaded row from a text expression over other fields: LEFT(field, n), RIGHT(field, n), a "literal", another field, joined with +. Example: LEFT(No, 4) + "-" + Currency. Reference fields by their plain name (spaces are fine), with && for nested fields; a nested dataset can also use the parent row's fields and _parentId. The eye icon inserts a field from a sample record, and Preview evaluates the expression against a few stored rows. The result is stored like any field and can be used in the business key, in data-reference variables and in the export. Anything that cannot be resolved becomes blank and never fails the load.

How a load works​

Full load. Every full endpoint is read, page by page, and every row is written by business key. When the fetch has finished successfully, rows of the loaded instance that the load did not see are marked deleted. A failed or stopped full load leaves the stored data as it was. A full load that meets the same business key twice ends as warning, because the later row overwrote the earlier one; the key is probably incomplete.

Incremental load. The pre-load delete window runs first if configured, then the delete endpoints, then the incremental endpoints. Nothing is swept. The endpoint's incremental filter decides what is read; the usual filter is {{system:lastLoadedAt}}, the start of this instance's newest finished load, so a record changed at the source while that load ran is read again rather than lost.

When a dataset has only one kind of endpoint, the load runs as the kind it has: a full load of a dataset with only incremental endpoints runs incrementally (so nothing is swept), and an incremental load of a dataset with only full endpoints runs as a full load. The log says which ran. A cloud dataset with no endpoints ends as success with no rows, which is the normal state of a webhook-only dataset.

Progressive loads (set on the source system or dataset, see Create or edit a source system) write rows to the first SQL destination while the load fetches them, instead of the export reading them back afterwards; a failed load keeps what it wrote, and the Dataset log shows a partial export row with the rows written so far.

What a stored row carries. Besides the source's fields: _instance (the instance name), _updatedAt, _url when enabled, _parentId and _index on nested datasets, and an _id built as <instance>-<key part>-<key part> (the bare key on a single-instance source system). A dataset with no business key gets a random id per row on every load, so an incremental load duplicates rows and a full load marks all previous rows deleted: give every dataset a key.

Deleted rows. A delete endpoint, a Gateway reconcile, a webhook delete or a full-load sweep does not remove the row; it marks it deleted and keeps the values, so the deletion can flow to the export (see Deleted records). Every read in the UI and the Data API hides deleted rows. A daily job permanently removes rows that have been deleted for longer than Setup → Runtime → Deleted-record retention (default 90 days; 0 keeps them forever).

Pre-load delete​

Before each incremental load, delete the rows of the instance where a date field is on or after a cutoff, so the fetch repopulates that window. Use it when the source retroactively updates recent records. Field is a date or datetime field on the row; Cutoff is today-N (past offsets only). Overlap it with the endpoint's filter: if the fetch reaches back less far than the delete, you get a hole in the data. The rows are physically removed, not marked deleted, so a row deleted at the source inside the window leaves no marker for the export.

Indexes​

Indexes speed up filters and sorts on the loaded data, which matters for data-reference variables and the Data API. System indexes (_id and the writer's _meta, _updatedAt and _instance indexes) are read-only. Add index picks columns (ascending or descending), optionally Unique, and builds in the background; a building index shows Being created with a Cancel, and a failed build shows the error with Retry. Indexes you add are named cc_idx_… and can be edited or deleted once ready. A unique build fails when the loaded data already has duplicates. The Variable preview suggests an index when a MIN/MAX aggregation would need one.

Preview loaded data​

Preview loaded data on the Datasets page shows the first 200 stored rows (deleted rows hidden) and the total, with Export to Excel (all records or the top 1,000) and, when the Data API is on, the dataset's Data API URL for Power BI and other tools.