Join a worksheet to another data source

Joining a Worksheet to a table or View creates a relationship that allows them to be searched together. Choose a column to join on that both data sources contain (for example, employee ID or product key). This process creates a generic join between the Worksheet and the table or View on the column you specify.

Defining a generic relationship in the UI rather than using a primary key/ foreign key join through TQL has no impact on performance. However, when creating relationships in the UI, you must ensure that you create it in the right direction: many to one. To create many-to-many joins, or to create joins using >, <, >=, or <=, use TQL.

See this matrix for information about which joins you can create, and what permissions these joins require.

These are the allowable joins and permissions within ThoughtSpots:

Worksheets

Must have Can edit permission on the source Worksheet. Can create joins with:

Imported tables (UI)

Must have Can manage data permission to load the table.

Views

Must have Can edit permission on the source View. Can create joins with:

Views
Materialized views
Imported tables (UI)

Must have Can manage data permission to load the table.

Tables uploaded from backend (tsload) or through DataFlow

Must have Admin privileges to access tsload.

Materialized views

Must have Can edit permission on the source Materialized view. Can create joins with:

Views
Materialized views
Imported tables (UI)

Must have Can manage data permission to load the table.

Tables uploaded from backend (tsload) or through DataFlow

Must have Admin privileges to access tsload. ThoughtSpot recommends creating this join through the UI, instead of using TQL.

Imported tables (UI)

Must have Can edit permission on the source table. Can create joins with:

Worksheets
Views
Materialized views
Imported tables (UI)

Must have Can manage data permission to load the table.

Tables uploaded from backend (tsload) or through DataFlow

Must have Admin privileges to access tsload.

Tables uploaded from backend (tsload) or through DataFlow

Must have Can edit permission on the source table. Can create joins with:

Views
Materialized views

ThoughtSpot recommends creating this join through the UI instead of using TQL.

Imported tables (UI)

Must have Can manage data permission to load the table.

Table uploaded from backend (tsload) or through DataFlow

Must have Admin privileges to access tsload. This join is often used to create Worksheets.

Tables linked through connections

Must have Can edit permission on the source table, and Can manage data permission. Can create joins with:

Table linked through connections

The two tables must be from the same connection.

View on top of table linked through connections

The View and the table must be from the same connection.

Views on top of table linked through connections

Must have Can edit permission on the source Views. Can create joins with:

Table linked through connections

The View and the table must be from the same connection.

View on top of table linked through connections

The two Views must be from the same connection.

You must have either the Can administer ThoughtSpot privilege or the Can manage data privilege to create a join relationship. If you’re not an administrator, you also need edit permissions on the table, View, or Worksheet.

When creating a join between the columns in two data sources, the columns being linked must have the same data type, with the same meaning. That is, they must represent the same data.

Creating a join from a Worksheet

To create a join from a worksheet:

  1. To find your worksheet, click Data on the top menu, and choose Worksheets.

  2. Find your Worksheet through browsing, Search, or selecting the appropriate Tag(s).

  3. To select the Worksheet for adding joins, click its name in the list.

  4. Click the Joins tab. The list of existing joins within the worksheet appears.

    Joins within worksheet

  5. To view the joins between this Worksheet and other data sources, click Joins within this worksheet, and choose Joins from this worksheet.

  6. To start creating a join, click + Add Join on the upper right side of the screen.

    Add join to worksheet

  7. In the Add Join dialog, choose the destination table or View for the other end of the join.

    Worksheet join dialog

  8. Choose the matching columns under each table. These columns must use the same data type. [Optional] You can select multiple columns for the same join. To add another pair of matching columns to the join definition, click + Add columns.

  9. Specify the join type; see Join types.

  10. Specify the join cardinality; see Join cardinality.

  11. Click Create join.

  12. Repeat these steps to create all joins.

After creating the join, you may change its name and description by clicking the edit icon. If you want to change the data source or column being joined, you must delete the join and create a new one.