Skip to main content
To homepage To homepage
Solved

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

R
Starter
Forum|alt.badge.img

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

Best answer by rory.smith

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.

View original
Did this topic help you find an answer to your question?

4 replies

rory.smith
TimeXtender Xpert
Forum|alt.badge.img+7
  • rory.smith
  • TimeXtender Xpert
  • 684 replies
  • March 16, 2023

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.

Thomas Lind
1 person likes this
  • Thomas Lind
    Thomas Lind
R
Starter
Forum|alt.badge.img

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
TimeXtender Xpert
Forum|alt.badge.img+7
  • rory.smith
  • TimeXtender Xpert
  • 684 replies
  • Answer
  • March 22, 2023

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.

G
1 person likes this
R
Starter
Forum|alt.badge.img

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


Most helpful members this week

Terms and ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings