You may have noticed that when you do a search on a data source, ThoughtSpot is only able to aggregate one column by one other column. Because of this, you may come across searches you can’t do in one pass, because they are essentially nested queries. But you can create the equivalent of nested queries using a view, which is an Answer that you have saved for the purpose of building other searches on top of it.
|Only users with the Can administer ThoughtSpot or the Can manage data privilege can create views and link them.|
You can use a view just like any other data source. You can even link it to other sources by defining a relationship. When you save an Answer as a view, and then use it as a source in a new search, it is similar to doing a nested query in SQL, only much easier to create.
|While you can’t apply row-level security (RLS) to a view, you can apply row-level security to the view’s underlying table in order to restrict the data shown when searching on the view.|
Suppose you created a search on the sales fact table that shows the top ten Sales Reps by revenue, for the first quarter. Then you want to do some further investigation on that subset of data, such as ranking them by how much they discounted a specific product based on data from the orders fact table. Unless you save your first Answer as a view, and then search over that view, you can’t get your Answers.
Here are the high-level steps for creating and using views:
Create the first search, and save it as a view.
Create relationships or define joins to connect your view with any other data source.
Create a new search that includes your view and the other sources linked with it.
We recommend that you create a new Worksheet that includes all these data sources.
Creating a Worksheet makes it easier for people to search using your view and any related tables.
When creating views, keep in mind the sizing recommendations for Worksheets, for the final Worksheet that you plan to use in modeling you data.
To be able to join a view with a base table, your installation must be configured to allow this. The view cannot have more than 5 tables, and the number of rows in the view cannot exceed 10 million rows.
The order of the objects being linked (joined) matters, because joins are directional. The table or view with the foreign key must be in the first (left) position. The table or view with the primary key must be in the second (right) position.
For best performance, views should have 50 or fewer columns, and no more than 10 million rows.
For more information, see SQL views.