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.

Comments

Show correct totals when using SSAS role security — 16 Comments

Joey Babu on October 12, 2011 at 10:51 am said:

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!

Reply ↓
- James Serra on October 12, 2011 at 6:21 pm said:
  
  Hi Joey,
  
  My first thought would be to check to see that on step #3, you checked the “Enable Visual Totals” on the database dimension, but should have checked it on the cube dimension. I talk about that problem in my blog Setting role security in SSAS for a role-playing dimension. Let me know if that was the problem.
  
  Reply ↓
Joey Babu on October 24, 2011 at 10:34 am said:

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!

Reply ↓
- James Serra on October 24, 2011 at 2:55 pm said:
  
  Yep, on cube dimension only. Let me know if that works.
  
  Reply ↓
Maikel Follon on January 16, 2012 at 9:39 am said:

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

Reply ↓
Maikel Follon on January 16, 2012 at 10:53 am said:

Hi James,

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

Maikel

Reply ↓
Daniel on December 18, 2012 at 9:12 am said:

Hi James,

thanks for your info. I worked great.

Regards.

Reply ↓
Gerd on November 4, 2014 at 2:38 am said:

It works. Thanks for the tip.

Reply ↓
Victor on November 11, 2014 at 2:36 am said:

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

Reply ↓
Niraj on March 30, 2015 at 7:36 am said:

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.

Reply ↓
Kashif on October 14, 2015 at 10:30 am said:

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!

Reply ↓
Mani on October 30, 2015 at 11:39 am said:

Hi James,

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

Reply ↓
Pingback:Preparation for the 70-467 SQL BI exam | x86x64
ces on December 2, 2016 at 9:53 am said:

Hi James, how about for tabular tab (dax). where can i set the “Enable Visual Totals” ? Thanks

Reply ↓
Pingback:How to optimize the dimension security performance using partitioning in SSAS Multidimensional – My Blog
Pingback:Cómo optimizar el rendimiento de la seguridad de la dimensión mediante el particionamiento en SSAS multidimensional – My Blog