Build a data model from a workspace file#

Early release. This feature is in early release, so its screens and behavior may change. Tell us what you think with Send feedback. See Early release features and Send feedback.

A data model does not have to bind to a connection in Settings > Integrations. When the file it needs to read, an Excel workbook or a SQLite database, already lives in the same workspace as the model, point the model straight at that file. No connection is added to Integrations, so dropping many workbooks or databases into a workspace folder never clutters the datasource list.

A workspace file is one source among possibly several: a model can read any number of workspace files and Integrations connections side by side, and each view draws from one of them. This is the recommended way to model a workbook or database that belongs to one workspace and one purpose. If instead the file should be shared as a connection other workspaces or other models can also use, see Connect Excel and Connect SQLite for Configure as data source, the admin path that creates a real Integrations connection from the same file.

Before you begin#

• The file is an Excel workbook (.xlsx, .xlsm) or a SQLite database (.db, .sqlite, .sqlite3), uploaded into the same workspace where you are building the data model.
• SQLite databases must be an allowed upload type in this workspace. On an existing install, an administrator may need to add the Databases file-type category to the workspace's upload allow-list first, see Notes and limits.
• You have permission to create a data model in the workspace (the same permission New > Data model requires).
• The workbook or database follows the same layout rules as any Excel or SQLite source: a header row of column names for a worksheet table, or ordinary tables in the database. See Connect Excel and Connect SQLite for the query modes and what each driver exposes.

Two ways to start#

From the file, in the workspace browser#

1. In the workspace's file tree, find the Excel workbook or SQLite database.
2. Open its ⋮ (more actions) menu and choose Create data model.
3. Reportworq creates a data model in the same folder, named after the file, already bound to it, and opens it in the editor.

From an existing data model's Sources tab#

1. Open the data model (new or existing), select Source in the rail, then the Sources tab.
2. Select Add workspace file… (on a model with no source yet, this is also how you start).
3. Pick an Excel workbook or SQLite database from this workspace's files. The picker lists only files you can see, filtered to Excel and SQLite.

The file appears in the list of sources, named by its path, with how many views read it. Change… on its row picks a different file, and Remove takes it out (the confirmation names the views that read it). To start a view on the file, use + Add on the rail's Source node and choose the file.

Look inside the file first#

To see what a workbook holds before you model it, select its name in the workspace. It opens full screen in a read-only spreadsheet preview, with its sheets as tabs along the bottom; Close or the Esc key returns you to the folder. The preview shows the values stored in the file. See Workspace files.

Build the views#

Once the file is picked, start a view with + Add in the rail and choose the file. The rest of the data model editor works exactly as it does for a connection in Integrations:

• Start with a table on the Design canvas lists the workbook's worksheets (and named ranges), or the database's tables. See Build a view with the designer.

• Write SQL instead works the same way it does against any relational source, for example:

  SELECT Region, Product, Sales FROM Sales
  

• Fields, Preview, filters, and variables all behave as documented in Data models.

Portability#

The data model stores the file's path inside its own workspace, never a workspace id. That is what makes the model portable: when you export the workspace and import it elsewhere, the model resolves the same path in the new workspace and keeps working, with nothing to reconfigure.

The trade-off is the same one any relative path carries: if the file is later renamed or moved to a different folder, the model's reference breaks. Open the model's Sources tab and use Change… on the file's row to pick its new location.

How your data stays secure#

• No datasource connection is created or stored for a workspace-file model. The connection is built in memory each time the model is used, previewed, imported, run in a job, or bound to a report, and discarded afterward.
• A file source is treated like a connection: if you can see the data model, you can report on it. The picker lists only the files you can see, so you need to see a file to pick it. After that, anyone who can see the data model can preview it, report on it and run jobs from it, without access to the file itself, just as they would for a model on an Integrations connection. The model can only ever read files in its own workspace, never another workspace's file, even if you know its path.
• Scheduled jobs and campaigns do not depend on any one person's access. A distribution job or contribution campaign that runs on a schedule, or through the background job runner, reads the file the same way, so it keeps running even if the author who built the model later loses visibility into the file.
• A view's SQL can name only the file's own sheets or tables. A SQL view over an Excel workbook reads that workbook's sheets, and a view over a SQLite database reads that database's tables. A query that names another file (for example a sheet qualified by a file path, such as [C:/Finance/Salaries.xlsx].[Sheet1]), reads a driver system table (sys_...), attaches another SQLite database, or does anything other than read with SELECT is refused before it runs, with the message "This query names another file (...). A data model reads only its own workspace file." Sheet names with spaces, column headers with / or :, cell ranges such as [Data#A1:C20], and comments are unaffected. One limit: in SQL you write yourself, a sheet name or alias that contains a dot can't be used in front of a column (as in [my.sheet].[Region]), because it reads like a file name; give it an alias without a dot.
• The connection is always read-only. Reportworq opens the file for reading only, whichever driver is used underneath. A write-back operation against it, such as a SQL export or RWSQLUPDATE targeting the file, fails instead of silently doing nothing. To change the data, edit the file itself and upload the new version into the workspace.
• The server keeps a private, working copy of the file for the drivers to read from. That copy refreshes automatically the next time the model is used after you upload a new version of the file, and it is removed when the file or the workspace is deleted.

If the source breaks#

The editor never fails to open just because a file source is broken. If the file was renamed, moved or deleted, that source's row on the Sources tab shows a problem, and the rest of the editor still opens normally. Use Change… on the row to pick the file again, either the same file at its new location, or a replacement.

What happens on import#

When you import a workspace (or a data model) that was exported from elsewhere:

• The model keeps its source pointer, whether or not you can see the file. A model is not stopped from importing because of what the importer can see in the target workspace.
• If the source file hasn't been uploaded yet, the model starts working as soon as you upload the file to the same path. Until then the model's Sources tab shows the problem.
• Either way, the imported model is treated like a fresh copy: it gets its own new internal identifier, so it never accidentally shares its source connection with the model it was exported from.

Notes and limits#

• SQLite uploads may need an admin step first. A fresh Reportworq install permits .db / .sqlite / .sqlite3 uploads out of the box. An install upgraded from an earlier version keeps its existing upload allow-list, so a .db upload may be refused with "These file types are not permitted…" until an administrator adds the Databases category. That setting lives in Settings > Security > Workspaces > the workspace > Workspace Files, under Allowed File Types, use the + Databases quick-add chip.
• Preview needs a saved model, under one specific setting. If Settings > Performance is configured to run reporting queries out of a separate process, previewing a file you just added but have not yet saved will not find it, the out-of-process worker only sees a model's saved source. Save the model once after adding the file, then preview.
• Excel workbook and SQLite rules otherwise match the connector pages: see Connect Excel and Connect SQLite for worksheet/table layout, query modes, and driver behavior.

Related pages#

• Data models for curating fields, filters, variables and hierarchies, and for choosing who sees a view (Public, Hidden or Disabled), once the source is added.
• Connect Excel and Connect SQLite for the same files used as a shared Integrations connection instead (Configure as data source), and for the query modes both paths share.
• Connect a data source for the general Integrations add-and-test flow.