To homepage To homepage
Solved

Combine two Row Level Security columns on Fact within Tabular Cube model with AND instead of OR?

  • 15 March 2023
  • 4 replies
  • 182 views
R
Rank
Badge

Dear Reader,

Dynamic Row Level Security (DRLS) can be implemented by defining a table that contains 1. values for the column to be secured; 2. defining the users email-addresses.

With this specific case, two DRLS columns are defined.

This is shown as depicted in the first picture below:

The first column (_Retailer_Key).

The second column to be secured is also dynamically defined (_CountryAccess_Key):

This works, however suppose I have 4 retailer country combinations

Country Retailer
UK YourOwn
NL YourOwn
UK Theirs
GE Unsere

 

And the _CountryAccess_Key that was defined by DRLS was “UK” and the Retailer was YourOwn, then the Result is that the rows with the red font is returned. This is just (_CountryAccess_Key OR _Retailer_Key). However that’s not the result required. The result should be (_CountryAccess_Key AND _Retailer_Key) as depicted below.

Now the result only returns one row (depicted in the red font) 

Country Retailer
UK YourOwn
NL YourOwn
UK Theirs
GE Unsere

 

How can that be achieved within TX?

With kind regards,

Ronald van Koutrik

icon

Best answer by rory.smith 22 March 2023, 16:09

View original

4 replies

rory.smith
Rank
Userlevel 5
Badge +7

Hi,

 

could you not combine both columns into one? Similarly to a Junk Dimension that replaces all permutations of Country+Retailer with a key reference in the Fact linking to a separate Junk Dimension table. Iirc Junk Dimensions are keyed with varbinaries that Power BI does not like, but you could swap out to an int key I guess.

R
Rank
Badge

Dear Rory,

Thanx for your answer.

The trick/problem I have is the ‘All values’ value provided by TX, to indicate the All value.

 

Suppose someone is allowed to view all Countries, but only one Retailer or vice versa. How can you indicate that, without combining all countries/retailers into a list.

My hesitancy is based on the cost of checking a lot of values before being able to provide RLS by SSAS Tabular.

With kind regards,

Ronald van Koutrik

 

rory.smith
Rank
Userlevel 5
Badge +7

For each dimension you add into row-level security you need to add permutations including All dim1 items / All dim2 items / Only one dim 3 item etc. TX generates only the ‘All’ across all dimensions for you. I do not see a trick, in general, to avoid this.
What you can do (from 20.1.x at least, look for 'Filter Data for Semantic Endpoints with New Project Variables’) is filter endpoints, so you can create a separate endpoint for each country and only have RLS on the retailer. I do not know if those variables are still available in the new release however.

R
Rank
Badge

Dear Rory,

I followed your advice, about the permutations, with a twist.

First I checked the permutations actually used, then only added the permuations required.

Thanx for your support!

 

Reply


Terms and ConditionsCookie settings