Visual Totals in MDX and Role Security

February 6, 2008

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.

VT3

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.

VT4

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.

VT2

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”.

VT5

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…

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

22 comments

  1. RicardoFebruary 15, 2008 ב 19:49

    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

    Reply
  2. Ella MaschiachFebruary 17, 2008 ב 10:28

    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

    Reply
  3. RicardoFebruary 18, 2008 ב 12:49

    I will, thanks for your help.

    Ricardo

    Reply
  4. StuartFebruary 18, 2008 ב 23:30

    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

    Reply
  5. Ella MaschiachFebruary 19, 2008 ב 20:08

    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

    Reply
  6. liaFebruary 6, 2009 ב 4:14

    i’ve already using role in my SSAS,but why i can’t see implement of the role in my pivot table..
    thanks

    Reply
  7. Ella MaschiachFebruary 6, 2009 ב 13:28

    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

    Reply
  8. sinmeiApril 7, 2009 ב 5:07

    i wrote mdx like this :
    FILTER([Customer].[Customer].Members ,[Customer].[Customer].Properties(“Customer”)=”Alfa”)
    but i got error,string or numeric was used….
    any one can help me?

    thanks

    Reply
  9. GötzOctober 7, 2009 ב 22:02

    Nice Article, but one question ist still open,
    i have dynamic Dimension securtiy.
    I have lets say a Dimenson Mandants,
    and a Dimension Customers.
    So in Mandants i have no problems.
    I return always a set that contains at least one tuple.

    But i have user that should only see their own customers. Thats also not a problem. Because they will also have a SET with tuples.
    And then i have the big issue i have also an
    user how should see all Data from certain Mandants
    but no customers at all.

    If this user connects. he will see no Data at all.
    I do this with Dimension Data Security.
    This user shouldnt be able to see
    any member of the customer Dimension.
    But no query will retrieve an result in this case.
    I’m using SQL Server 2005 SP3.
    If i remove the Dimension Data Securtiy for Customers, the user will see restricted Data to his mandants.

    I have on all Dimensions Visual Totals on. On all i restrict Member access.
    But why do i get no Data if i denie all on
    Customers.
    Because , grouping the facts over Mandant should still be possible.

    any one who has an idea and can help?

    Reply
  10. RanaJanuary 6, 2010 ב 0:03

    Thanks, this really helped. Keep up the good work

    Reply
  11. ShankarJanuary 18, 2010 ב 12:29

    I have created two Roles on SSAS Cube, Letz us say Role1 and Role2. I have assigned User1 to Role1 and User2 to Role2 in Membership of the Role designer. Now that I want to pass the roles to MOSS 2007 through PPS. In the PPS datasource I have mentioned the Roles (both the roles are assigned: Role1;Role2). When I tring accessing the data for User1 I am able to get the data that is assigned in Role1 as well Role2. How to I restrict the data that I have assigned in Role1 for User1 alone. Plz help on the issue.. Thanks in advance

    Reply
  12. AbedFebruary 11, 2010 ב 11:20

    Need help please.

    I try to use two cube dimension but different attribute:
    Product Brand
    and
    Product Principal,

    how do I mix these two attributes?

    thanks.
    abed.

    Reply
  13. Ella MaschiachFebruary 14, 2010 ב 11:13

    Hi Abed,

    Just choose the two relevant attribute in the two relevant dimensions in the Cube’s Dimension Data tab (as shown in the first picture of this post).

    Hope that helped,
    Ella

    Reply
  14. SaugatApril 13, 2010 ב 8:21

    Hi Ella,
    Same problem as shankar:Please help

    I have created two Roles on SSAS Cube, Letz us say Role1 and Role2. I have assigned User1 to Role1 and User2 to Role2 in Membership of the Role designer. Now that I want to pass the roles to MOSS 2007 through PPS. In the PPS datasource I have mentioned the Roles (both the roles are assigned: Role1;Role2). When I tring accessing the data for User1 I am able to get the data that is assigned in Role1 as well Role2. How to I restrict the data that I have assigned in Role1 for User1 alone. Plz help on the issue.. Thanks in advance

    Reply
  15. Ella MaschiachApril 18, 2010 ב 17:23

    Hi Saugat,

    I’m sorry, but I haven’t got a machine with PPS on it to check for you.

    Sorry I couldn’t help,
    Ella

    Reply
  16. KennyDecember 20, 2010 ב 15:03

    Thanks Ella,

    Your insights on this matter really helped me!

    Was looking for a solution a very long time now, and finally, I’m done looking.

    Merry X-mas 😉

    Reply
  17. Ella MaschiachDecember 22, 2010 ב 11:47

    Glad I could help Kenny 🙂
    Merry Christmas and a Happy New Year to us all!

    Reply
  18. NancyMarch 4, 2011 ב 14:18

    Hi Ella,
    I have a dimension with Cie1, Cie2, … and a calculation Total with is the total of all Cie. I am trying to define a role to see only Cie1 and Total but it shows Total = Cie1. Any ideas ?

    Nancy.

    Reply
  19. Ella MaschiachMarch 9, 2011 ב 15:50

    Hi Nancy,

    Did you check the “Enable Visual Total”? if you did, then that should explain it. Uncheck it.

    All the best,
    Ella

    Reply
  20. RaghuJune 29, 2011 ב 22:32

    Great Article,
    Worked like Charm.

    Thanks Ella.

    Reply
  21. seabastian thomasDecember 4, 2012 ב 2:50

    Hey together,

    okay let´s again start from the above.

    I´d like to understand the difference between ‘enable visual totals’ and ‘Default Member’ when set to the element of the alowed set.

    As you also posted here it might not be useful to the the totals when your rights are constricted to a certain alowed set. the problem is not only the totals but the fact that you are able to see the elements from other Dimenisions (e.g. when you have two dimensions ‘company’ and a dimension that represents the ‘fact table’ containing the company also; and you have an alowed set giving you the right on the dimension ‘company’ to see a specific company only, you are still able to see the data of other companies through other dimensions.)

    if you set the ‘enable visual totals’ flag all dimensions respect the settings done in the dimension ‘company’. that´s what I expected the ‘default member’ to control. in fact this is doing the same.

    1. What is the difference?

    2. What if you want to define the user to see only allowed elements no matter from what dimension – but at the same time to see totals over all the dimension´s members?

    Reply
  22. Ella MaschiachDecember 4, 2012 ב 2:59

    To Boris and Sebastian, I apologize but by mistake I ended up deleting your comment as you were classified as spam by the site. I re – published them under my name and will comment later.

    Reply