Skip to main content

Adding source tables to workbooks

This feature is in beta

The source tables & CSV/XLSX upload feature is currently in beta. To have this feature enabled, reach out to Omni support: support@omni.co.

Some times you may want to utilize data in an analysis that isn't in one of your database connections, whether it's for data enrichment or an initial exploration. Creating a source table allows you to add data to Omni, whether by manually entering data or uploading a CSV or XLSX, without needing to touch your data pipelines.

Requirements​

To add source tables, you'll need:

Limitations​

Currently, source tables:

  • Must be added in a workbook
  • Are limited to 500,000 rows
  • Can't be used in joins unless the table is pushed to a database. Setting the connection's Schema for table upload property enables this functionality. Refer to the setup guide for your database for more information.

Adding a source table to a workbook​

Source tables can be added in the workbook of any document. In a workbook, navigate to Edit > New blank table. A new tab with a blank source table will display.

To get data into the table, you can manually enter it or upload a CSV or XLSX:

Manually enter data
  1. In the first row, add the names of the columns. The first row must be a header row to allow Omni to generate a schema for the table.
  2. Enter data into the cells. To add line breaks, use Ctrl + Enter.
  3. To add rows or columns, use the Add Row and Add Column options.
  4. When you're finished editing the table, click Save CSV near the top right corner of the page.
  5. Enter a name for the table and click Save.
Upload a CSV or XLSX
CSV and XLSX requirements

To upload a CSV or XLSX:

  • The first row must be a header row
  • The file can't exceed 500,000 rows

Additionally, if the XLSX file contains multiple tabs, only the first sheet will be uploaded.

  1. Click the Upload CSV option near the top right corner of the page.
  2. Browse for or drag the file you want to upload into the upload dialog.
  3. If you have Querier or Connection Admin permissions, you'll see a Create topic checkbox. Checking this box will create a topic from the uploaded file. If you're a Restricted Querier, a topic will automatically be created from the file.
  4. Click Upload to upload the file.

Once there's data in the table, click Query CSV to create a query using the data. The table will also become available in the field picker in other queries.

Querying & joining source table data​

Source tables can be queried like any other table, including using the field picker, SQL editor, or working with calculations and formulas.

Note: To make the data available outside of the workbook, you'll need to promote it to the shared model.

How you join data from a source table to other data depends on whether table uploads are enabled for the database connection used by the workbook:

Traditional joins (Requires database uploads)

If database uploads are enabled for the connection, you can use traditional joins - whether via the UI or in SQL - to create relationships between source tables and other data.

Workbook tab joins with XLOOKUP

If database uploads aren't enabled for the connection, you won't be able to use traditional joins with source tables. You can, however, use XLOOKUP to perform cross-tab analysis in a workbook:

=XLOOKUP(lookup_value, <query_tab_name>!<lookup_range>, <query_tab_name>!<return_range>)

For example, you have a workbook with two tabs: Sales, which is backed by a topic, and Products Upload, which is a query on a source table. You want to include the names of products purchased in an order alongside other sales data. To join the data in the Products Upload tab to your sales, you could use an XLOOKUP formula like the following to create a new calculation in the Sales tab:

=XLOOKUP(A1,'Products Upload'!B:B, 'Products Upload'!C:C)

Modeling & promoting source tables​

When a source table is added to a workbook, a few things will happen in the workbook model. To make the changes available outside of the workbook, they must be promoted to the shared model.

  • A view representing the source table will be created. These views can be identified by the presence of an uploaded_table_name parameter:

    products.view
    # Reference this view as products
    uploaded_table_name: products.csv::1a3497f5-cba8-47c0-bab9-b2df84d1b141

  • A topic based on the view representing the source table will be created if:

    • Create topic was selected while uploading a CSV or XLSX, or
    • A Restricted Querier performed the upload

Views and topics created from source tables can be modeled like any other view or topic. However, if the source table hasn't been pushed to the connection's database, attempting to use the source table in a join in the IDE will result in the following content validation error:

View "<view_name>" references an uploaded table. Uploaded tables cannot be used in joins.

Source table data that hasn't been pushed to a database can only be joined to other tabs in a workbook by using XLOOKUP. Refer to the Querying & joining data section for more information and examples.

Managing source tables​

Updating source table data​

To update a source table's data, locate the table in a query tab. Click the options menu next to the table's name, then Edit Table:

The source table will open in a new tab, where you can edit the data as needed. Note: Uploading a new CSV or XLSX will overwrite the data currently in the table.

Click Save CSV when finished.

Previous uploads are retained in Settings > Uploads. If you create a new analysis from this page and promote the changes from the workbook, it will overwrite the source table data currently in the shared model.

Deleting source tables​

Heads up!

Before deleting an upload, note that:

  • This action will permanently delete the source table from your instance and can't be undone
  • Querying views and topics built from the upload will result in an error after the deletion
  • Views and topics built from uploads must be manually removed from models after the deletion

To delete an upload, navigate to Settings > Uploads. Locate the file you want to remove and click Delete.