How the Model join rule works
Use the Model join rule to specify when to apply joins when a search is done on a Model. You can either apply joins progressively, as each search term is added (recommended), or apply all joins to every search.
Understand progressive joins
Often, a Model includes several dimension tables and a fact table. With progressive joins, if your search only includes terms from the fact table, you can see all of the rows that satisfy your search. But as you add terms from dimension tables, the total number of rows shown may be reduced, as the joins to each dimension table are applied. It works like this:
-
If you choose Apply joins progressively (recommended for most cases), joins are only applied for tables whose columns are included in the search.
-
If you choose Apply all joins, all possible joins are applied, regardless of which tables are included in the search.
When using Apply joins progressively, the number of rows in a search using the Model depends on which tables are part of the search. The Model contains the results of a defined query in the form of a table. So if a particular dimension table is left out of the search, its joins are not applied.
Apply all joins
The Apply all joins setting forces all tables in a Model to be included in every query, regardless of which columns are actually selected in the search.
|
Apply all joins is considered an anti-pattern and is not recommended for most use cases. Enabling this setting significantly increases query cost because every table in the Model is joined on every search, even when most of those tables are not needed. There are very few business cases that justify this behavior. Use Apply joins progressively unless you have a specific and well-understood reason to do otherwise. |
Behavior by Model type
The effect of the Apply all joins setting varies depending on the schema type of your Model.
| Model schema type | Apply all joins: OFF (progressive) | Apply all joins: ON |
|---|---|---|
Single star schema |
Only the tables needed for the selected columns are joined. |
All tables in the Model are joined on every query, regardless of which columns are selected. |
Fan trap |
Only the tables needed for the selected columns are joined. |
All tables in the Model are joined on every query, regardless of which columns are selected. |
Chasm trap |
The chasm trap methodology applies. Progressive join settings are ignored. |
This setting is ignored. ThoughtSpot always uses the chasm trap join methodology. See How joins are applied with chasm traps. |
Example: Single star schema
Consider a Model with one fact table (factInventory) and three dimension tables (DM_DATE_DIM, DM_PRODUCT, and DM_CATEGORY, where DM_CATEGORY snowflakes off DM_PRODUCT).
-
With Apply joins progressively: a search using only a column from
DM_PRODUCTjoins onlyDM_PRODUCT. The fact table and other dimension tables are not included. -
With Apply all joins: the same search joins all four tables —
factInventory,DM_DATE_DIM,DM_PRODUCT, andDM_CATEGORY— even though onlyDM_PRODUCTcolumns were selected.
Example: Fan trap
Consider a Model with two fact tables (factOrderRows and factOrderHeader), where factOrderHeader has its own set of dimension tables and factOrderRows shares a product and category hierarchy.
-
With Apply joins progressively: only the tables relevant to the selected columns are joined.
-
With Apply all joins: all fact and dimension tables across both star schemas are joined on every query.
How joins are applied with chasm traps
When working with Models and row-level security, you need to understand how joins are applied. This is particularly important with chasm trap schemas. For chasm trap schemas, if row-level security is only set on one of the tables, people could see data they should not see if the scope of their search does not include that table.
For chasm trap Models, progressive and non-progressive joins do not apply. There is an entirely different methodology for how Model joins on a chasm trap schema work with row-level security. The Apply all joins setting is silently ignored for chasm trap Models — ThoughtSpot always executes a simple, optimized query using the chasm trap methodology regardless of how this setting is configured. So you can safely ignore that setting for chasm trap Models.
Rule-based row-level security (RLS) with Models
With rule-based RLS, you need to protect every table that contains any sensitive data. To do this, you can grant access by creating explicit row-level security rules on each of the underlying tables which contain data that row-level security should apply to.
When creating the row-level security rules for a table that’s part of a Model, you aren’t limited to referencing only the columns in that table. You can specify columns from other tables in the Model as well, as long as the tables are joined to the table you’re creating the rule on. Then, when creating a Model on top of them, the behavior is consistent regardless of the Model join rule you choose. Users will never be able to see data they should not, regardless of what their search contains.
Imagine you have a Model that contains a Sales fact table, and Customer and Product dimensions that are joined on Customer SSN and Product Code columns.
In order to secure the Sales table, you can use Customer Name from the Customer column to create a row-level security rule.