Use the worksheet join rule to specify when to apply joins when a search is done on a worksheet. 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 worksheet includes several dimension tables and a fact table. With progressive joins, if your search only includes terms from the fact table, you’ll 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 worksheet depends on which tables are part of the search. The worksheet acts like a materialized view. This means that it 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.
Rule-Based Row Level Security (RLS) with worksheets
With Rule-Based RLS, you need to protect every table that contains any sensitive data. To do this, you’ll 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 worksheet, you aren’t limited to referencing only the columns in that table. You can specify columns from other tables in the worksheet as well, as long as the tables are joined to the table you’re creating the rule on. Then, when creating a worksheet on top of them, the behavior is consistent regardless of the worksheet 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 worksheet that contains a
Sales fact table, and
Product dimensions that are joined on
Customer SSN and
columns. In order to secure the
Sales table, you can use
Customer Name from
Customer column to create a row level security rule.
How joins are applied with chasm traps
When working with worksheets 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. (this protects them from having people see the wrong things if they have chasm trap).
For chasm trap worksheets, progressive and non-progressive joins do not apply. There is an entirely different methodology for how worksheet joins on a chasm trap schema work with row level security. So you can safely ignore that setting.
Joins and aggregated worksheets
You have the ability to join an aggregated worksheet with a table. You can join an aggregated worksheet with a system table by creating a relationship. This means aggregated worksheets behave similar to tables, and they can be used in the same way as a table, excluding any TQL manipulation. You also have the capability to create a worksheet on top of an aggregated worksheet. So aggregated worksheets can be included as tables in regular worksheets.