Join a table or view to another data source

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 (for example, 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.

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.

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.

Possible joins

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.

You can join any data object to another object, with the following 2 restrictions:

  • You can’t join a Worksheet to any other object.

  • You cannot join objects across connections. This means that you cannot join an object that is part of one connection to an object in a different connection.

Create a join

To create a relationship through the web interface:

  1. Select Data in the top menu, and choose Tables or Views.

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

  3. To select the table or view for adding joins, click its name in the list. You will see the Columns view of the data source.

  4. Select the Joins tab. The list of existing joins appears.

  5. Select +Add Join.

    Click + Add Join in the upper right corner

    The Create Join page appears.

    Create join page. There is a 1 next to the "Connection" dropdown. There is a 2 next to the join name.
    Legend Action

    1.

    Choose your connection from the dropdown Connection menu. You can only create joins between data sources uploaded through the same connection.

    2.

    [Optional] Select 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.

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

  7. Under Table 2, choose the destination table or view for the other end of the join.

  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, select +Add columns.

  9. Specify the join type; see Join types.

  10. Specify the join cardinality; see Cardinality.

  11. Select Create join.

  12. Repeat these steps until all the joins you want to make have been created.

Edit a join

After creating the join, you may change its name, type, or cardinality by selecting the edit icon edit icon. This allows you to change a many-to-one join to one-to-one. To reverse the join cardinality, you must delete the join and create it again with the opposite cardinality.

If you want to change the data source or column being joined, you must delete the join and create a new one.

If the join has dependents, and you want to change the cardinality, you must delete the join, and create it again with the opposite cardinality. Deleting the join does not delete the dependent objects, but it does make them unusable until you create another join. Refer to Delete a relationship.

Related information

Was this page helpful?
×