Joining dataset with RLS causing duplicates

Question & Answer
,
1

I have two datasets.

One dataset has my contracts. This dataset has the following columns: contract_id, contract_name, client_id, start_date, end_date (it has more but these are the important ones). Example below :

contract_id(guid) contract_name (string) client_id(guid) start_date end_date
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa Contract 1 11a70fa0-b3cb-4cdf-9252-1ee8cc37551d 01/01/2024 01/31/2026
bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb Contract 2 11a70fa0-b3cb-4cdf-9252-1ee8cc37551d 02/01/2024 02/08/2026
cccccccc-cccc-cccc-cccc-cccccccccccc Contract 3 11a70fa0-b3cb-4cdf-9252-1ee8cc37551d 03/01/2024 03/31/2026

The other dataset is for the contract_to_contract_type table. A contract can have multiple contract types assigned to it. The columns in that dataset are contract_type_id and contract_id. Example below.

contract_type_id (guid) contract_id (guid)
gggggggg-gggg-gggg-gggg-gggggggggggg aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa
gggggggg-gggg-gggg-gggg-gggggggggggg bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb
gggggggg-gggg-gggg-gggg-gggggggggggg cccccccc-cccc-cccc-cccc-cccccccccccc
hhhhhhhh-hhhh-hhhh-hhhh-hhhhhhhhhhhh aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa
hhhhhhhh-hhhh-hhhh-hhhh-hhhhhhhhhhhh bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb-
iiiiiiii-iiii-iiii-iiii-iiiiiiiiiiii bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb

The contract to contract types dataset has row level security on the contract type id so that users will only have access to contract types within their permissions. So unfortunately contract type id must be a column in the dataset because of RLS.

This issue is that of course when I join the 2 datasets on contract_id if the user has rls permission to multiple contract types and a contract has multiple contract types then the contracts show up multiple times in the joined dataset. It’s important to get the duplicates out of the dataset because in the visual we’re using the contract name and the contract_id. Is there any way to get the duplicates out of the joined dataset?

An example of a visual would be one that lists the newest 5 contracts with a hyperlink that uses their contract id and client id.

1 Like
2

Hello @jnorah, due to your requirements for the RLS permissions, I don’t think you can really manage the dataset any differently. Unless you were able to add a contract type grouping field into the dataset to merge multiple contract ids into a single contract type field, it is going to repeat the rows. Alternatively, you could maybe configure a wider dataset by including multiple contract type columns. How many contract types could there be for a single contract?

Another idea would be to manage the duplicates within QuickSight. Utilizing something like a denseRank function and applying it as a filter onto your visuals could allow for the RLS functionality to stay the same without impacting the visuals and aggregations.

Rank Filter = denseRank([{contract_type_id} ASC], [{contract_id}], PRE_AGG)

If you apply this calculated field as a custom filter on your visuals, set the value 1 in the text field, and exclude NULLs in the dropdown, you should be able to ensure you only return 1 row per contract_type. Also, since this is PRE_AGG, if you need the user to be able to filter on contract types, it will display the one they have selected.

Let me know what option you prefer and I can answer any lingering questions you have. Thank you!