Skip to main content

Datasets

Overview​

A Dataset is a stored copy of query results. Instead of querying your database every time a report runs, analyses built on a Dataset read from data that FlexIt has already loaded, which makes them much faster. You decide when the data is refreshed, either by hand or on a schedule.

A Dataset is defined like an analysis: you pick a data model, then add the fields and filters you want. When the Dataset is loaded, FlexIt runs that query and stores the results.

Creating and managing Datasets requires the Admin role.

Create a Dataset​

There are a few ways to create a Dataset:

  • New Dataset – click the New content button in the portal, select New Dataset, and choose a data model. Add fields and filters just as you would in a new analysis, then save it.
  • Create Dataset from Analysis – from the Dataset menu of an existing analysis, opens a new, unsaved Dataset with the analysis's fields and filters
  • Convert Analysis to Dataset – from the Dataset menu, changes the saved analysis itself into a Dataset
  • Use Dataset – from More Options on an analysis's Report Menu, click Create New Dataset to load the analysis's own data into a Dataset, or pick an existing Dataset under or Use Existing

You must save an analysis before you can create a Dataset from it.

What gets stored​

When a Dataset is loaded, FlexIt stores:

  • Every field on the report, plus the fields used for sorting
  • The fields used by filters, so that analyses built on the Dataset can still filter on them. Locked filters are applied to the data before it is stored.
  • The fields used in filter formulas

Calculations that the report computes in the browser are not stored, and date values are stored without a time of day.

Load and Refresh​

Open the Dataset and use the Dataset menu in the toolbar:

  • Load to Dataset – runs the query and stores the results. If the Dataset already has data, you'll be asked to confirm before it is overwritten. You'll get a notification when loading is done.
  • Schedule Refresh – reload the Dataset on a schedule. You can also choose Schedule on the Dataset in the portal.
  • Dataset Info – shows the number of rows, when the data was last updated, its size on disk, and its source
  • Delete Dataset – deletes the stored data. The report goes back to querying live data.

Analyze a Dataset​

To build an analysis on a Dataset, choose Create Analysis from Dataset from the Dataset's Dataset menu, or from its actions in the portal. The new analysis queries the Dataset instead of the database.

Analyses that use a Dataset have these Dataset menu items:

  • Switch to Live Data / Switch to Dataset – toggle between querying the database directly and querying the Dataset
  • Change Dataset Source – point the analysis at a different Dataset
  • Open Dataset Source – open the Dataset that the analysis reads from
  • Dataset Info – details about the Dataset, as described above

If a Dataset hasn't been loaded yet, or a query against it fails, FlexIt queries live data instead and shows a message.

Datasets can also be added to dashboards from Existing Content.

Data Models on Datasets​

To build a data model on top of your Datasets, choose Datasets as the data source in the Data Model editor. Each loaded Dataset appears as a table named dataset_ followed by the Dataset's ID.

Storage​

Datasets are stored on the FlexIt server as compressed Parquet files in the datasets directory of the FlexIt installation, one file per Dataset, and are queried with DuckDB. In Docker deployments, this directory is kept in its own named volume.