Join a table or view to another data source
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 (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.
If you want to create a primary key/foreign key relationship, you need to use TQL rather than the web interface.
| 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. |
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 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 uploaded through Embrace
-
Must have Can edit permission on the source table, and Can manage data permission. Can create joins with:
- Table uploaded through Embrace
-
The two tables must be from the same connection.
- View on top of table uploaded through Embrace
-
The View and the table must be from the same connection.
- Views on top of table uploaded through Embrace
-
Must have Can edit permission on the source Views. Can create joins with:
- Table uploaded through Embrace
-
The View and the table must be from the same connection.
- View on top of table uploaded through Embrace
-
The two Views must be 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:
-
To find your table or view, click Data in the top menu, and choose Tables or Views.
-
Click the name of your table or view.
-
Click Joins.
You will see the list that shows existing joins.
-
Click the + Add Join button on the upper right side of the screen.
-
Use the Source to destination dialog to choose the destination table, view, or worksheet you want to join to.
-
Choose the columns you want to join on from the table or view (source) and destination table, view, or worksheet.
Click Next.
-
Give your join a name and description, and click ADD JOIN.
-
Repeat these steps until all the joins you want to make have been created.
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.
Related information
