I'm customizing a couple of cubes by adding a table with our wealth screening data, but we only want certain user groups to be able to see this data. After reviewing the documentation here: https://documentation.sisense.com/7-1/administration/defining-security-and-access-settings/data-security.htm#gsc.tab=0 and a little experimentation, I see that applying data security to one table in a cube will apply it to the whole cube. Here's what I'm considering doing:
1. The Wealth data table only has rows for customers who have wealth screening data. This table gets copied into the Analytics DB into a table which I will call D_WEALTH.
2. I have a view in the Analytics server which basically looks like this
SELECT w.customer_no,sensitivity=1,w.wealth_data_1,w.wealth_data_2,w.wealth_data_3FROM D_WEALTH wUNIONSELECTc.customer_no,sensitivity=case when w.customer_no is not null then -1 else 0 end,wealth_data_1=null,wealth_data_2=null,wealth_data_3=nullFROM D_CONSTITUENT cLEFT JOIN W_WEALTH w on c.customer_no=w.customer_no
The result is that for customers with no wealth data, the sensitivity column is set to 0, and if the customer does have wealth data, there are two rows, one containing the wealth data with sensitivity set to 1, and one with no wealth data and sensitivity set to -1.
That view will be added to the cube.
Then, in the Data Security area of Sisense, I will set up security on the sensitivity column of the view. The user groups who should be allowed to see the wealth data will have "Accessible Values" set to 0 and 1. Everyone Else will be set to -1 and 0.
Is this the right approach?
Thank you!
-Galen