Join a table or view to another data source
About joins
A join combines columns from one or more sources in your data by using matching values. By defining relationships between your sources, you create a new, richer set of data that you can use to answer your business questions. 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 source table or view, and the target table or view on the column you specify.
| You cannot create range joins or primary key/foreign key relationships in the UI. You must use TML to create range joins. Create primary key/foreign key relationships in your cloud data warehouse. |
Join types
ThoughtSpot supports the following join types: Inner, Left Outer, Right Outer, and Full Outer. You can choose a join type when creating or editing a join through the ThoughtSpot web interface.
ThoughtSpot defaults to the inner join type, which returns results for data with matching values in both the origin table (Table 1) and the target table (Table 2).
Left outer joins return results for all values from Table 1, and any matching values from Table 2.
Right outer joins return results for all values from Table 2, and any matching values from Table 1.
Full outer joins return results for all values from either Table 1 or Table 2.
Join cardinality
When creating the join, you must also identify its cardinality: Many:1, 1:Many, or 1:1.
A Many:1 cardinality defines a join where multiple values in the origin table (Table 1) correspond to one value within the target table (Table 2). A join between a product table and a product category table shows multiple products that match each category.
A 1:Many cardinality defines a join where one value in the origin table corresponds to multiple values within the target table.
A 1:1 cardinality defines a join where one value in the origin table corresponds to a single value within the target table (for example, employee name and employee ID).
You must create a join between columns in two data sources that contain 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 or view.
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.
| If you create joins at the table level, and then create a Worksheet that uses the columns from the table, the settings are inherited from the table at the point in time that the Worksheet is created. If you then go back and change the settings at the table level, your changes will not be reflected in the Worksheet. If you want the Worksheet to have the changes you made at the table level, you must drop those columns from the Worksheet and re-add them. |
Creating a join from a table
To create a table join through the web interface:
-
Select Data in the top menu, and choose Tables.
-
Find your table through browsing, Search, or selecting the appropriate Tag(s).
-
To select the table for adding joins, select its name in the list. You will see the Columns view of the table.
-
Select the Joins tab. The list of existing joins from the table appears.
-
Select + Add join.
The Create Join page appears.
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.
-
Under Table 1, choose the table you want to create a join from (origin table).
-
Under Table 2, choose the destination table or view for the other end of the join.
-
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.
-
Specify the join type; see Join types.
-
Specify the join cardinality; see Cardinality.
-
Select Create join.
Creating a join from a view
To create a view join through the web interface:
-
Select Data in the top menu, and choose Views.
-
Find your view through browsing, Search, or selecting the appropriate Tag(s).
-
To select the view for adding joins, select its name in the list. You will see the Columns view of the view.
-
Select the Joins tab. The list of existing joins from the view appears.
-
Select + Add join.
The Create Join page appears.
Legend Action 1.
Choose your connection type, either Embrace (Connections) or Falcon. 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.
-
Under Table 1, choose the view you want to create a join from (origin view).
-
Under Table 2, choose the destination table or view for the other end of the join.
-
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.
-
Specify the join type; see Join types.
-
Specify the join cardinality; see Cardinality.
-
Select Create join.
Modifying joins
ThoughtSpot allows you to edit the name, join type, and cardinality through the web interface. To change the columns that define a join, you must delete the join and create a new one.
Editing a join from a table
To edit a join between tables:
-
Select Data in the top menu, and choose Tables.
-
Find your table through browsing, Search, or selecting the appropriate Tag(s).
-
To select the table for adding joins, select its name in the list. You will see the Columns view of the table.
-
Select the Joins tab. The list of existing joins from the table appears.
-
Select the edit icon to the right of the name of the join you want to modify.
The Edit join page appears.
-
Make the desired changes to the name, type, or cardinality of the join.
-
Select Save.
Editing a join from a view
To edit a join from a view:
-
Select Data in the top menu, and choose Views.
-
Find your view through browsing, Search, or selecting the appropriate Tag(s).
-
To select the view, select its name in the list. You will see the Columns view.
-
Select the Joins tab. The list of existing joins within the view appears.
-
To view the joins between the view and other data sources, select Joins within this View, and choose Joins from this View.
-
Select the edit icon to the right of the name of the join you want to modify. The Edit join window appears.
-
Make the desired changes to the join name, join type, and cardinality.
-
Select Save.
Deleting a join
To delete a join:
-
Select Data in the top menu.
-
Find the origin table, Worksheet or view of the join you want to delete through browsing, Search, or selecting the appropriate Tag(s).
-
Select the name of your origin table, Worksheet or view in the list.
-
Select the Joins tab. The list of existing joins from the table, Worksheet, or view appears.
If you want to delete an external join from a Worksheet, you must click Joins within this Worksheet under the Joins tab and select Joins from this Worksheet. 
-
Select the delete icon to the right of the join name. The Confirm delete window appears.
If the join has dependents, a different window appears, warning you that deleting this join makes the dependent objects stop working until you recreate the join later. However, ThoughtSpot does not delete the dependent objects. You can use this functionality to reverse the join cardinality or otherwise edit a join without deleting its dependents.
-
Select Delete.
