SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. 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 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.


No comments.

Leave a Comment

Please register or log in to leave a comment.