RLS To Impact only One Fact Table

[tarmogolf]

Hello, I am working to apply Dynamic RLS to my current report.

I have a User table that includes users' role in the company. I also have many fact tables from various parts of the organization, all of which relate back to a dimRoster table that stores the company hierarchy.

I have one table in particular, factFraud, that I would like to apply RLS to without applying RLS to the other fact tables. dimRoster is related to factFraud via a locationID column. Each location in dimRoster has a District and a Market component.

So, if in my user role table I have Market Manager who oversees the "California" market, how do I filter the factFraud table via the dimRoster table so that this manager can see *just* California in the factFraud table , but still be able to see the entire company in all other fact tables?

[jdbuchanan71]

Yeah, it's not ideal but I couldn't think of another way to do it.  Could come in handy if there ends up being other items that need RLS applied to them.  You will already have the table you need in place.

[jdbuchanan71 · Accepted solution]

@tarmogolf 

One thing I can think of is to make a copy of the dimRoster and link it only to the factFraud table.  Then you can apply the RLS to the copy of the dimRoster and it will flow only to the factFraud.  You could make the dimRosterCopy really narrow so it only has the fields you need for the filtering and the relationship.  You can also hide the table in the model so people don't see two dimRoster tables.

[tarmogolf]

Ah! This works. Perhaps not the cleanest solution, but it definitely works and doesn't seem to noticeably impact performance. Thanks!