ThoughtSpot offers a schema viewer that lets you see your database schema in the web browser. The Schema Viewer is interactive, so you can configure it to show just what you want to see.
You need Admin privileges to use the Schema Viewer.
Bringing up the Schema Viewer
You can bring up the Schema Viewer from the Data screen. Click the ellipses icon , and select View Schema.
When viewing the schema, you can filter the tables shown similarly to how you filter data sources. The list of tables, worksheets, and imported data on the left includes only those objects you want to see. Clicking on one of the objects brings it to the middle of the viewer and highlights it. You can drag the objects around in the viewer.
Why to use the Schema Viewer
You can use the Schema Viewer to find out information like:
- What is the relationship between two tables? What tables make up this
- worksheet, and how are they joined?
The schema viewer shows joins between tables, join directionality, and join type (whether they are Foreign Key to Primary Key, relationship joins, or joins defined by users through the web interface). Use the Table list to find a specific table or worksheet.
How the Schema Viewer shows joins
You can use the Schema Viewer to review your schema and ensure that it was modeled using best practices. For example, joins are shown in separate colors that indicate their type:
- Red is used for generic relationships
- Green is used for primary key/foreign key joins
When viewing a worksheet, you’ll also be able to see whether an inner, left outer, right outer, or full outer join was used between each of the joined tables.
A good rule to follow is “Keep it Green”. This means that you’ll get better results from PK/FK joins rather than from using generic relationships. You should only use generic relationships when the tables being joined have a many-to-many rather than a PK/FK structure. If you find tables that have been joined using a generic relationship, but could have used a PK/FK join, you should drop the relationship and create a PK/FK join instead. To do this, you need to use the ALTER TABLE…DROP RELATIONSHIP statement in TQL. Then use ALTER TABLE…ADD FOREIGN KEY to create the PK/FK join.
Worksheets are often based on more than one table. The worksheet schema will show schemas for the tables behind the worksheet, as well as the joins between tables that were created as a part of the worksheet.
Click a worksheet, to see it in the Schema Viewer. If the schema view is not showing the schema behind the worksheet, double click the tab on the top right of the worksheet object.
The worksheet view shows the following information:
- All tables in the worksheet, and the relationships between these tables.
Source columns for all columns of a worksheet.
Keys and definitions for each relationship, as well as join paths and types.
Columns that are derived from formulas.
- Correct join paths for newly created chasm trap worksheets. Existing chasm
- trap worksheets created prior to ThoughtSpot version 4.4 will not show the correct join paths.