Need to apply Column Level Security in Pivot table visualization

Q&A
1

I’ve a dataset with columns: year, month, zip_code, gender_age, kpis, measure_value

Column “Kpis” has 10 unique values such as Prime streams, Prime Members, Ad-FREE Revenue etc. Column “measure_value” is a numeric column and shows the number of customers/values against each KPI at year, month, zip_code, and gender granularity.

I created a Pivot table (screenshot attached) in quicksight. This dashboard is shared with 5 customers but I want the “AD-FREE NET Revenue” column in pivot to be visible only to certain customers and not all the viewers.

Pivot Screenshot
Pivot Screenshot1142×608 32.5 KB

I tried following things:
(1) Creating RLS dataset using a csv file with all the customers alias in one column and all the values in KPIs column against the alias in the next column. For ex. I’ll have 10 rows with my alias where second column will have all 10 unique values listed and 9 rows for some other user (who I don’t want to show “AD-FREE Net revenue” column) with all the kpis values except Ad-FREE Revenue.
However, it is not working. It hides all the columns on the pivot visualization for every user including users who are on the permission dataset file.

(2) Using Column Level Security - The CLS option restricts the data at column level but not certain values of a column. For eg. I’ve an option to choose column “kpis” and “measure_value” but then it will restrict the whole column. However, I want to restrict only revenue metrics present in the “kpis” column.

Please note - I followed followings link to implement
RLS: Using row-level security with user-based rules to restrict access to a dataset - Amazon QuickSight
CLS: Using column-level security to restrict access to a dataset - Amazon QuickSight

I also have a sage post open for this request - https://sage.amazon.dev/posts/1995499?t=7

2

Hi @abhmaur

The column level resitriction works on the columns. But looking at your explanation it appears you are trying to restrict a specific member in your KPI column from being displayed. The Column Level security does not work that way.

You have two choices

  1. Create separate calculated fields in the dataset for each of your KPIs and then apply the Column level security for the ad free column
  2. Alternatively make an additional field which returns the KPI values as is except for Ad Free where it will either return original value or 0 for your specific customers where you dont want them to know about this particular value
3

Hi @abhmaur,

It’s been awhile since we last heard from you on this thread, did you have any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

4

Thanks for your response. I created a view of this dataset with just the revenue columns and enabled CLS on this view. I created the visualization using this new dataset in the same dashboard and it works for me.

1 Like