easyROLES the fastest way to set up SSAS roles

Visual totals in SSAS security

In our previous post we looked at how SSAS role security could be tested in Microsofts SQL Server Management Studio. The role we created is fully functional when we display the level on which security is applied (in this case it was member Pacific Sales Manager from level Title). However if we were to choose a different way to view the data and skip level Title and everything below that, we would get a bit of a surprise:

 Query results before visual totals were enabled

We would expect to see the total sum of Pacific Sales Manager as defined by the role security, but instead we get the total sales count for the whole Sales department. This is default behaviour of a role, but nothing that can not be changed, so no worries. In SSAS cubes (and data cubes in general) most values are pre-calculated for all members and therefore this situation is no different- our cube already knows the value of every measure for Sales department and just shows that value (that is why cubes are so fast to show total values). We need to force our cube to recalculate the values of all members above the one that holds security. For handling such situations MDX statement visualTotals was created. But we need something easier and faster than an MDX statement and cube modification. So we would just edit the role specification, find the secured dimension again, choose advanced tab and click in “Enable Visual Totals” checkbox, like this:

 Enabling visual totals in MS SQL Server Management Studio

Once you click ok and refresh previously created analysis, here is what you get:

 Query results with visual totals enabled

Now no AD user that is a member of this security role will ever see totals above the level of their role definition. SSAS will just sum up all the values from security definition and show that summary value instead.

Here at easyROLES we could never figure out the reasoning behind not having Enable Visual Totals checked in by default. After all, there are relatively so few occasions when visual totals would not be the desired behaviour… I guess by now you understand that we have it checked in by default, so another click saved in 99% of the cases.


Tweet about this on TwitterShare on LinkedInShare on Google+Share on FacebookShare on StumbleUponEmail this to someone

Leave a Reply

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

We want you to be comfortable with your purchase and therefore offer 30 day 100% money back guarantee.