Visual Totals in MDX and Role Security
Visual Totals in MDX and Role Security
Well I thought today we'd go over visual totals in MDX and also see how they may have an impact on how you assign role based security in you SSAS project.
Visual Totals in MDX are there to give you just what they describe – a sum on the children in a certain set. Child members which are not in the specified set will be ignored during the calculation. For instance, if I have a set that consists of: USA, New York, Washington and California, when I look at the total for USA, I will only see it's sum being consisted of that which belongs to New York, Washington and California. All the other states, such as Texas, Florida, Louisiana etc etc will not go into the calculation being run for the total for USA.
The basic syntax for visual totals would be: VisualTotals(Set_Expression)
In which case the "Set_Expression" would be the set you would like your calculations to run on.
I admit that though I had known of visual totals in MDX before I did not have a chance to use them until I added a new kind of role to my cube.
I had built a cube which shows all the matters regarding HR: the positions in the organization, the workers that hold them, the budget for the different units, the salaries being paid and the amount of money written down for them in bookkeeping.
All of my users thus far could see all of the information. Some of them may not have been granted to look at salaries, and so they could not look at salaries at all. This demanded only that I uncheck the boxes next to the measures of the workers salaries.
Now, I was asked to add a new sort of user. My new user was head of HR for the municipality's IT department and should only see data regarding the IT department. In the scope of the IT department, my new user should be able to look at all the available data.
At first what I did was to assign a new role for that user and in his "Dimension Data" tab, I chose the radio button for "Deselect all members" allowing the role to view in my organization only the relevant unit.
As my organization is a Parent Child dimension, this also struck a V sign next to the entire organization and all the children of that unit.
If to be frank, I thought that was all I had to do. But I was wrong… I was looking at my cube through the cube's "Browser" tab and using the "Security Context" of the new role I defined.
When I dragged the organizational structure dimension all was good – I could open it only to the unit I defined and when looking at the entire municipality, I could only see amounts for the various measures stemming just from the organizational unit I selected. But when I brought over the position (a dimension all of its own), I could see all of the positions in the municipality and to each it's own measure. Not good…
So I went back to the definitions of my role. In the "Dimension Data" tab I again selected the organizational unit dimension. I clicked the "Advanced" tab and checked the box next to "Enable Visual Totals".
Though visual totals may slow down the performance of the cube, they are the only way I had left to ensure that my new role won't be able to look at anything which was not connected to the unit he's allowed to look at. I deployed my new definitions and went back to the cube "Browser" tab, again simulating my new user. This time when I dragged the position dimension I could only see the relevant positions and their measures. Same went for all the other dimensions.
Just goes to show that marking a little check box can go a long way…
Comments
Ricardo said:
Hi,
Great article in SSAS.
I was trying to use the same idea but on my SSAS project, when I go to the Role designer->Dimension Data, in the Attribute Hierarchy dropdown, only the atributes are there. Not the Atribute Hierachy defined in the dimension.
Problem: I'm unable to see multi-level hierarchies.
Any idea on how can I enable it to display the hierarchies there?
PS: It happens with all my dimesions. Regular, Time, Accounts (parent dimension), etc
thanks
Ella Maschiach said:
Hi Ricardo,
Well first of al, I'm happy you found the post interesting.
Secondly, did you mean to say that what you see in your "Data Dimension" is only your attribute hierarchies and not your user hierarchies? The attribute hierarchies are the attributes in your dimension (in AW the Product Dimension has: product name, category, subcategory), whereas the user hierarchies are the grouping \ ordering of those attributes (category > subcategory > product name) As user hierarchies are only a certain grouping \ ordering on the existing attributes in the dimension, you would need to define your restrictions on the attributes themselves for them to apply on your user hierarchies. You have, in this case, to uncheck the relevant members in all the attributes that are available in the path of the user hierarchy that you want to hide.
If this hasn't answered your question, please feel free to contact me through the "Contact" at the top menu bar of this post.
All the best,
Ella
Ricardo said:
I will, thanks for your help.
Ricardo
Stuart said:
Great article
I have an interesting addition to the above scenario that I have been unable to resolve. I have set up one role with security based on Dimension data and another using security based on Cell Data. Individually each of the roles does what I am expecting. However, I thought that if I added a user to both roles at the same time the most retrictive result would be returned. However, the opposite is true and the restriction of either role is effectively lost. What do I do to make sure that the restrictions of BOTH (or more) roles are applied to a user?
Thanks in advance
Ella Maschiach said:
Hi Stuart,
Glad you enjoyed the post.
Well as far as I undestood it, if a user belongs to two roles and each role enables him to view members in different levels in the same dimension, the actual result will be that that user will see the intersection of permissions between those two roles.
If on the other hand, a user belongs to two roles and each role enables him to view different dimensions, the actual result will be a union of the permissions granted in both roles.
I found a TechNet article that even refers to the second description under the paragraph of "Multiple Roles and Permissions".
In any case, what I would recommend to you is to create a new role in your cube which includes the MDX script of both roles (assuming that the definitions don't clash) and add your user to that role and to that role only.
In general, as far as I know, it is recommended that each user will belong only to one role so as to avoid these sorts of complications.
Hope this helps.
All the best,
Ella
lia said:
i've already using role in my SSAS,but why i can't see implement of the role in my pivot table..
thanks
Ella Maschiach said:
Hi Lia,
I believe the third photo in this post shows you how to test your role when you browse the cube (either in SSMS or in the BIDS environment). After you define the role and it works correctly, assign users (or user groups) to it from the Active Directory. If authentication is with Windows Credentials, the user browsing your cube will automatically have the role assigned to him as you have defined in your cube.
Hope that helped,
Ella