Show correct totals when using SSAS role security

When using role security in SSAS, one point of confusion is when a person sees the grand total for a measure that includes the total of ALL members in the cube, even though the security has limited the members that person can see.  To fix this, check the “Enable Visual Totals” check box, so that users can only see the total for members they are allowed to see.  It is located on the Advanced tab on the Dimension Data tab, near the bottom.

For example, let’s say the total revenue for all the divisions in a cube is $15,000. You create a role called “Division A”, and set it up so members of that role can only see the revenue for Division A, which totals $3,000. If you use a front-end tool like Excel to access the cube and use the division hierarchy to see the total revenue, you will see the revenue of $3000 for Division A, but will also see the Grand Total for the revenue as $15,000. This is because the “Enable Visual Totals” box is not selected (which is the default). Selecting this box would result in the Grand Total showing as $3,000.

Calling it “Enable Visual Totals” is very confusing.  Something like “Filter Totals” would have made more sense.

About James Serra

James is a big data and data warehousing solution architect at Microsoft. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 25 years of IT experience.
This entry was posted in Security, SQLServerPedia Syndication, SSAS. Bookmark the permalink.

13 Responses to Show correct totals when using SSAS role security

  1. Joey Babu says:

    Hey James, I’m using Roles in SSAS and I want a specific Role to see the Visual Totals for a particular Measure as exactly as you described in this article. I went to the 1.) Dimension Data tab of the specific Role, 2.) clicked on the Advanced tab, 3.) selected the Measures Dimension and the desired Dimension I want the totals to update, and 4.) clicked the “Enable Visual Totals” checkbox. This still proved unsuccessful for me. After processing my Cube, I Browsed the Cube using the Role I just modified and the Totals still aren’t updating as expected. Any other suggestions? Thanks!

  2. Joey Babu says:

    Hey James, sorry for the late reply but I’ve lost touch with this for a bit. Are you saying only “Enable Visual Totals” on the Cube Dimension only and not the Database Dimension? Thanks!

  3. Maikel Follon says:

    Hi James,

    I have exactly the same problem as Joey Babu. In a certain dimension (business unit) I select the members that are relevant to the role. Then I expect to see only the numbers for the selected dimension members. It works when I browse the cube using Management Studio but when I use Excel 2010 the total includes the numbers related to all members. BTW: I am using a dimension that is not role-playing and I tried both scenarios on database (shared dims) and cube level.

    Any comments would be appreciated.
    Thanks,
    Maikel

  4. Maikel Follon says:

    Hi James,

    Forget about my last post. The user I was testing with was member of another role (admin). Everything works just fine.

    Maikel

  5. Daniel says:

    Hi James,

    thanks for your info. I worked great.

    Regards.

  6. Gerd says:

    It works. Thanks for the tip.

  7. Victor says:

    Thanks a lot, saved me a lot of hours of headache.

  8. Niraj says:

    Hi James,
    Thanks for this great post. My problem is a bit different than what you are discussing here, may be if you can suggest anything to me. In the example give above what if I want a user of Division A should be able to see correct totals but when he drill down he should be able to view revenues of only his division, in this case Division A.

    We have a scenario where we are generating a Accounting Cube, which will be accessed by different department people. My client wants to show correct Net profit to every user, but when it comes to base accounts by drilling down the net profit hierarchy he only wants users to see Accounts for which they have access, I wanted to know if there is any feature in SSAS I can use for this.

  9. Kashif says:

    James brother… that is just wonderful.I can’t thank you enough for this post. There was a critical issue that came up this morning and my boss referred me to your website 🙂 It is amazing how this simple little check box can create miracles when it comes to confidential information and data privacy.

    Have a great day and thanks much again!

  10. Mani says:

    Hi James,

    Thanks for this articles, can we have to do same thing in SSAS tabular model using DAX…

  11. Pingback: Preparation for the 70-467 SQL BI exam | x86x64

Leave a Reply

Your email address will not be published. Required fields are marked *