Analysis Services · Multidimensional

Performance Overhead Of Visual Totals On Dimension Security In Analysis Services Multidimensional

Recently I was involved in troubleshooting a mysterious Analysis Services Multidimensional performance problem for a customer: the team worked out that certain queries run by certain users were extremely slow, and that these users were members of roles where dimension security was applied, but the amount of slowdown – queries going through the role were taking over 10 minutes compared to a few seconds when run as an administrator – was unlike anything I had seen before. It turned out that the cause was having the Enable Visual Totals box checked on every attribute on the dimension where security was applied, not just the attributes whose members were secured.

I can’t reproduce the problem with the Adventure Works cube but I can use it to illustrate the problem. Let’s say you have a role that applies dimension security on the Country attribute of the Customer dimension:


Normally, in this scenario, you would only check the Enable Visual Totals box for the Country attribute:

When running a query with this role applied, in the Query Subcube Verbose event in Profiler you will see a slice is put on the Country attribute:

However, if the Enable Visual Totals box is checked for every attribute on the dimension then a slice is put on every attribute that has its hierarchy enabled:

The more of these slices there are the slower everything gets inside Analysis Services: slower scans, slower cache registry lookups and so on. In the case of the cube I was looking at the combination of all of these slices, extremely complex MDX calculations and unprocessed indexes led to the massive performance problem. Obviously if you have to use Enable Visual Totals on your role then you have to use it, and it’s extremely unlikely you will encounter this problem, but it’s good to know about it just in case.

2 thoughts on “Performance Overhead Of Visual Totals On Dimension Security In Analysis Services Multidimensional

  1. Hello Chris,

    Thank you for your nice blog and work here.
    In our company we have the same or similar Problem without Cubes…

    If we enable a security role on a Dimension how have a Relationship with a Direct Query fact table, the Performance trop from sec to Minutes and Timeouts and the produced query’s look like the hell….

    The Enable Visual Totals sound to 100% like our Problem, but we don’t use Cubes and only PowerBI, you have a Solution, can we Enable in end of the year with XMLA read and Write the Enable Visual Totals?

    thank you so much, you are our last straw.

    Best Regrets

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      It sounds like you have a different problem – this one only happens with SSAS Multidimensional. Sorry…

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.