A couple of weeks ago a colleague asked me a dimension security question I initially wrote off as, well, dumb. But he was persistent and we came up with a solution that is pretty interesting.

The requirements were as follows:

a) A company wants some of its users to see a subset of all the accounts

b) When a user views the all member of the account hierarchy, they should see the total of the accounts they are permitted to see (excluding the ones they are not permitted to see)

c) If a user can see no individual account, they should still see the all member of the account hierarchy with the full amount.

It was the italicized bit I thought bizarre – why should someone who can not see any accounts see the real total where others who could see some accounts not see the real total? But he insisted it was a requirement he’d seen several times.

The first two requirements are simple enough. Set up dimension security and turn on visual totals. Visual Totals can take a minute or two to grasp – when it’s turned on, aggregate values are the subtotal of what is permitted instead of its actual value. For example, if you can see USA and Canada but not Mexico, the visual total for all countries would be USA+Canada instead of USA+Canada+Mexico.

So for someone who can see one or more accounts, the attribute’s denied set would be the list of denied accounts and visual totals would be set to true. For those who can only see the all member (with its actual total), the denied set would every attribute member excluding the all member and visual totals would be set to false.

Our problem here is that we want a single role that has visual totals turned on for some users off for others. The UI does not permit this. When defining dimension security in Business Intelligence Development Studio or SQL Server Management Studio (SSMS) there is a simple check box turning visual totals on or off. However, the spec says it’s an MDX expression – the designers of the UI elected to expose it as a checkbox instead of a text box with an expression. This wasn’t a bad decision as before now I’d never heard of anyone doing what I’m about to suggest even though the engine permits it.

In VS, define the denied set (described just above) and turn on visual totals. Then deploy and script out the database. (In SSMS right click on the database and select the “Script Database as…” menu.). Change the visual totals from this:

<VisualTotals>1</VisualTotals>

to

<VisualTotals>iif( <denied set>.count &lt;= <attribute hierarchy>.members.count-1,1,0 )</VisualTotals>

(Replacing <denied set> and <attribute hierarchy> with the appropriate expressions.) Redeploy the database and process your cube. You’ll find that it works.

This has a major downside – you can no longer define security expressions for this attribute in the UI. If you do, you’ll have to script out the visual totals expression again.