In SSAS cube, sometimes we will get the request to allow user to see only his/her data . So we will create a user dimension and link it with facts. We will do necessary changes in the role dimension level security. We are refering this as user level security.
What if i need to allow the user data is not linked with fact, like i want to allow user to see the country level data that was assigned to him?
We will create many-to-many relationship with fact table and user dimension by reference table that can have both user key and country key
What if i need to allow user based on 2 dimensions, like i want to allow user to only sales department data for the specific country?
As of now, you may not seen any sample like this in internet. I had this scenario recently. I have to allow user to see some airline information and I have to limit him to see only specific ratio for those airlines. Both Airline and Ratio are different dimensions. Below is the design. I want to allow user to see only {Airline 1, Ratio 2} and {Airline 2, Ratio 1}. How can you limit the user ?
I tried to implement the default way of Dimension Level Security.
I had hope that this will work fine and processed my cube. I see the real problem after completed processing. It show {Airlines 1, Ratio 1}, {Airline 2, Ratio 2}, {Airline 2, Ratio 1}, {Airline 2, Ratio 2}. It allows user to see the result where he didnt have access.
Problem here is SSAS allows user to see Ratio 1 and Ratio 2 by my first configuration and Airline 1 and Airline 2 by my second configuration. I cant able to combine both the query in single place, as both are different dimension and cant able to access.
So after my research i added one more configuration in Cell data.
This check the user access cell by cell and shows null value {Airlines 1, Ratio 1} and{Airline 2, Ratio 2}
which i dont have access. Still i can see those combinations in my result set, but i cant able to see the values.
I am working on hiding those combinations too in the result set. Once i reach that i will update you.
Note: I am trying to access this cube in Excel.
What if i need to allow the user data is not linked with fact, like i want to allow user to see the country level data that was assigned to him?
We will create many-to-many relationship with fact table and user dimension by reference table that can have both user key and country key
What if i need to allow user based on 2 dimensions, like i want to allow user to only sales department data for the specific country?
As of now, you may not seen any sample like this in internet. I had this scenario recently. I have to allow user to see some airline information and I have to limit him to see only specific ratio for those airlines. Both Airline and Ratio are different dimensions. Below is the design. I want to allow user to see only {Airline 1, Ratio 2} and {Airline 2, Ratio 1}. How can you limit the user ?
I tried to implement the default way of Dimension Level Security.
I had hope that this will work fine and processed my cube. I see the real problem after completed processing. It show {Airlines 1, Ratio 1}, {Airline 2, Ratio 2}, {Airline 2, Ratio 1}, {Airline 2, Ratio 2}. It allows user to see the result where he didnt have access.
Problem here is SSAS allows user to see Ratio 1 and Ratio 2 by my first configuration and Airline 1 and Airline 2 by my second configuration. I cant able to combine both the query in single place, as both are different dimension and cant able to access.
So after my research i added one more configuration in Cell data.
which i dont have access. Still i can see those combinations in my result set, but i cant able to see the values.
I am working on hiding those combinations too in the result set. Once i reach that i will update you.
Note: I am trying to access this cube in Excel.
