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:

image

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

image

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:

image

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:

image

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 responses

  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
    Ilyas

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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

%d bloggers like this: