Learn how to define joins between a table or View and another table, View, or Worksheet

Joining a table or view to another table, view, or worksheet creates a relationship that allows them to be searched together. Choose a column to join on that both tables contain (e.g. employee ID or product key). This process creates a generic join between the table or view and the other table, view, or worksheet on the column you specify.

If you want to create a primary key/foreign key relationship, you need to use TQL rather than the web interface.

You must have either the Can administer ThoughtSpot 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.

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

Possible joins

Origin: Worksheet
Destination: Tables uploaded through the UI
Necessary permissions: Can edit permission on source Worksheet
Origin: View
Destination: other Views, Materialized Views, tables imported through the UI, tables uploaded from backend (tsload) or through DataFlow
Necessary permissions: Can edit permission on source View
Origin: Materialized View
Destination: Views, other Materialized Views, tables imported through the UI, and tables uploaded from backend (tsload) or through DataFlow
Necessary permissions: Can edit permission on source Materialized View
Note: For tables loaded from backend, it is best practice to create the join through ThoughtSpot UI, rather than using TQL.
Origin: Imported table (UI)
Destination: Worksheets, Views, Materialized Views, tables imported through the UI, tables uploaded from backend (tsload) or through DataFlow
Necessary permissions: Can edit permission on source table
Origin: Table uploaded from backend (tsload) or through DataFlow
Destination: Views, Materialized Views, tables imported through the UI, tables uploaded from backend (tsload) or through DataFlow
Necessary permissions: Can edit permission on source table
Note: For joins from tables uploaded through tsloard to Materialized Views and other tables uploaded through tsload, it is best practice to create the join through ThoughtSpot UI, rather than using TQL.
Origin: Table uploaded through Embrace
Destination: other tables uploaded through Embrace, Views on top of tables uploaded through Embrace
Necessary permissions: Can edit permission on source table, and can manage data permission
Note: The join must be created between two tables or a table and a View from the same connection.
Origin: View on top of a table uploaded through Embrace
Destination: Tables uploaded through Embrace, other Views on top of tables uploaded through Embrace
Necessary permissions: Can edit permission on source View
Note: The join must be created between two Views or a table and a View from the same connection.

When creating a join between the columns in two data sources, the linked columns must have the same data type, with the same meaning. That is, they must represent the same data. Normally, you can make this kind of link from a fact table column to a column in a dimension table that uniquely identifies a logical entity in your data such as Employee ID for a person, Product ID for a product, or Date Key for a specific date in a date lookup table.

To create a relationship through the Web interface:

  1. To find your table or View, click Data in the top menu, and choose Tables or Views.

  2. Click the name of your table or View.

  3. Click Joins. You will see the list showing existing joins.

  4. Click + Add Join on the upper right side of the screen. The Create Join page appears.

    Legend Action
    1. Select the data source of your table, either Embrace, or Falcon.
    2. Choose your connection from the dropdown Connection menu. You can only create joins between data sources uploaded through the same connection.
    3. [Optional] Click Enter join name to name your join. Note that ThoughtSpot automatically names joins using the following syntax: [OriginDataSourceName]_to_[DestinationDataSourceName]. You can always enter a more meaningful join name, either when creating, or when editing the join.

  5. Under Table 1, choose the table or View you want to create a join from (origin table).

  6. Under Table 2, choose the destination table or View for the other end of the join.

  7. Choose the matching columns under each data source. 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.

  8. Specify the join type; see Join types.

  9. Specify the join cardinality; see Cardinality.

  10. Click Create join.

After creating the join, you may change its name, type, cardinality 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.