These days the standard way to implement the dynamic security requirement in SSAS requires you to build a User Dimension to store all the UserNames that will have permissions and a Bridge table that relates the User Dimension to Dimension X (the one we want to secure the Members), but when someone complains that it cannot see something, how can we debug the issue?
If we think that we can have several environments (ex: Development, Integration, Production), in each one or all of them we can have UserNames from several Active Directory Domains (ex: using Trust relationships), and end user can be using a lot of tools (ex: Excel, PerformancePoint, ReportingServices) … we will agree that debug Dynamic Security Roles can become something hard to do … but with some more MDX it can be done :)
First as example consider this scenario where we want to secure the Members of Currency Dimensions so for example my User (BI4ALL\jlopes) can only see the EUR currency (dummy example). In our User Dimension we will store the UserNames allowed and in the Measure Group UserCurrency we will add the permissions (so it will have one row referring my User and the EUR currency).
On top of this model we will create the Security Role and on the Dimension Data tab use this MDX so the SSAS can reflect the logic of “only allow users to see the Currency Members that have been configured on the Measure Group UserCurrency”
NonEmpty(
[Currency].[CurrencyName].Members,
(
StrToMember("[User].[UserName].&[" + UserName() + "]"),
[Measures].[User Currency Count]
)
)On this point we have a full functional implementation but we lack an easy way to DEBUG it, so as promised we will have to add some mode MDX and make use of the MDX ERROR Function (first real life use case I had for her) that will allow us to pop-up a window to the user (with the UserName that was evaluate by the role) in the case the user trying to connect to the database don’t have any permissions configured (my debug case)
Iif(
Count(
Intersect({StrToMember("[User].[UserName].&[" + UserName() + "]")}, [User].[UserName].Members)
)=0,
ERROR("[User].[UserName].&[" + UserName() + "]"),
NonEmpty(
[Currency].[CurrencyName].Members,
(
StrToMember("[User].[UserName].&[" + UserName() + "]"), [Measures].[User Currency Count]
)
)
)
In case you pretend to debug any other case (like a specific permission) you will only need to change the first part of the IIF statement to evaluate our use case, the second part (the ERROR pop-up) and third part (the Security Evaluation) can remain the same.
The end goal here is to have an easy way to DEBUG and make our life easy working with the Dynamic Security on the Dimension Data (next time we will see how to minimize the cache issues)
.
.
.
.