I have created a semantic model of my project which is implemented as SSAS endpoint. Users usually connect to it through excel and browse the model.
Fact tables are connected to dimension tables using ID key. I want the fact table to be filtered based on values on one of the columns in dimension (fact table has millions of records and there are around 500k distinct IDs of the dimension within it).
Question is - if i apply row-level security on one of the columns in dimension - will it apply that to fact records as well? Or i must specifically apply the RLS onto the fact itself?
Thank you
Page 1 / 1
Has anyone tried this?
Hi @gediminas.juskenas
Yes, it will affect the Fact table as well. Provided they are related of course.
As an example. I got a G1 role and a security setup called RG1
This role is made on a dimension called company. I made the role to only include companies in America and the opposite in G2.
The people in UserGroup1 can only see those companies if they were to look at the Value Entry fact table in Excel.
Hi Gediminas,
The following example from a different community question may be helpful to clarify how to use row level security based on a dimension table field value.
I used manual security setups according to the following steps:
Created AD Groups that corresponded to the dimension values. For example, I have a dimension field called “Territory” and it is either populated with values of “East” or “West”. I then created two AD Groups called “EastRegMgrs” and “WestRegMgrs” whose members are comprised of the managers for those respective territories. “FredWilliamson” is my West Regional Manager and “RobertSchlafen” is my East Regional Manager.
I then create two roles “WestRegionalMgrs” and “EastRegionalMgrs”, and each role has the corresponding AD group as its member. “WestRegionalMgrs” has as its member the “WestRegMgrs” AD group.
I then created two Security Setups that map the respective territory values to their role members. The WestRegMgrs Security Setup maps the Territory field value of “West” to all of its role members.
The “WestRegMgr” security setup is mapped to the “WestRegionalMgr” role, so all of those role members will only see values where the “Territory” field value is “West”.
When I launch PowerBI and view the Semantic Model as Fred Williamson, I only see the West values. Conversely, if I launch PowerBI as Robert Schlafen, I only see the East values.
In the screenshot above, Robert Schlafen is on the left showing the East values and Fred Williamson is on the right showing the West values. For the tables displayed, you can see on the right that the Territory field is being selected from the EE dimension table and includes the Sales_Amount value from the Sales fact table.
If I try to open the Tabular model as a user that is not a role member, and is also not an Analysis Services Instance Administrator, then I will get a message stating that the model cannot be opened as the user does not have access to the database.
If you have any questions on the above or require further clarification, please let us know.
