I’ve been doing some work with complex dimension security recently and come across a number of issues which I’ve either known about but never blogged about, or only just become aware of. It’s a bit of a random list but hopefully it’ll help someone out there…

First of all, how do you use dimension security to deny access to an entire dimension? It was one of those things that I thought was possible out of the box even back in the days of AS2K but it turned out my memory was playing tricks with me and it’s a lot harder to do than you might think. Dimension security lets you deny access to individual members, so to deny access to the entire dimension you have to deny access to all the members on every single attribute hierarchy on the dimension – which is a right pain in the **** to do. From reading the relevant pages in the book "Microsoft Analysis Services 2005" (which is very good on the subject of security) I had thought it would be enough to set the Allowed Set on the key attribute to be an empty set, ie {}, and indeed this is what I’d been told would work. However this only has the effect of denying access to the key attribute itself and as I said you need to set the Allowed Set to {} for every attribute. Furthermore, once you’ve done that you’ll still see the dimension itself visible even though all you’ll see on each attribute hierarchy is an All Member, and there isn’t a really a good way of hiding it apart from creating a perspective and trying to get all users who are denied access to the dimension to connect using that perspective. Not very nice. I posted something on Connect here and even though it’s been closed as Won’t Fix I had an email conversation with various people in Redmond a while ago and I’m hopeful there’ll be a good solution to this one day.

Then there’s the problem of calculations and security. If you read Mosha’s blog entry here on the subject of cube initialisation you’ll see that the MDX Script is executed after dimension security is applied – so if you reference a member in your MDX Script that has been secured, then it could result in an error. For example, imagine you have the following calculated measure defined on Adventure Works:

Create Member Currentcube.Measures.Test as Measures.[Internet Sales Amount];

If you create a role that denies access to [Internet Sales Amount] and then browse the cube, you’ll still see this calculated measure but it will return an error. The stock answer to this problem is that you should set the cube’s ScriptErrorHandlingMode property from IgnoreNone to IgnoreAll, so that when the MDX Script executes when someone connects through the role the calculated measure doesn’t get created because there’s an error in its definition. I’m not too keen on this solution, and nor is anyone else I’ve talked to who has faced this problem, because what happens when there’s a real bug in your MDX Script? You run the risk of calculations you actually want being ignored and no error being displayed, and in the worse case scenario this could result in incorrect data being returned. Imagine you’ve got a script assignment like this on Adventure Works:

Scope({Measures.[Internet Sales Amount],Measures.[Internet Tax Amount]});
    This=Measures.Currentmember * 2;
End Scope;

If you connect to the cube through a role where [Internet Sales Amount] is denied and if ScriptErrorHandlingMode is set to IgnoreNone, you’ll see a "END SCOPE statement does not match the opening SCOPE statement" error. Set ScriptErrorHandlingMode to IgnoreAll though, and you don’t get an error because the Scope block is ignored – but the value of [Internet Tax Amount] will be half the value that is displayed when someone connects through a role which has access to [Internet Sales Amount].

The best solution to this problem is one I found in this thread on the MSDN forum suggested by Deepak and Mosha. While it doesn’t seem to be possible to conditionally hide calculated members (again using a perspective seems to be the best option although still not ideal) it is possible to make sure they don’t return errors, and similarly it is possible to write scoped assignments that can still work if one or more members they’re scoped on is secured. The key is to use named sets in scoped assignments, as follows:

Create Member Currentcube.Measures.Test as null;

Create Set myset1 As
iif(iserror(strtomember("Measures.[Internet Sales Amount]")), {},
{Measures.Test});

Scope(myset1);
    This=Measures.[Internet Sales Amount];
End scope;

Create Set myset2 As
iif(iserror(strtomember("Measures.[Internet Sales Amount]")), {Measures.[Internet Tax Amount]},
{Measures.[Internet Sales Amount],Measures.[Internet Tax Amount]});

Scope(myset2);
    This=Measures.Currentmember * 2;
End Scope;

For both the calculated measure and the scoped assignment, a named set is created that uses the IsError function to check to see whether [Internet Sales Amount] is secured or not – if it is, then an attempt to use the StrToMember function with its unique name will return an error. The outcome of this then determines the contents of a named set; for the calculated member this named set is used with a scoped assignment that assigns a definition to the member, and in the second example the named set is used instead of the hard-coded set in the Scope statement. This approach is a bit fiddly but it will work even when ScriptErrorHandlingMode is set to IgnoreNone because it avoids an error being raised.

Another thing to note is that if you are using role-playing dimensions in your cube, you will need to set dimension security on the cube dimensions rather than the underlying database dimension. For example, in Adventure Works, if you go to the Date dimension in the list of database dimensions (in the dimensions drop down on the Dimension Data tab of the Roles editor, database dimensions are listed first, then cube dimensions second) and set dimension security say to allow access to one year in the Calendar Year attribute, you’ll get an error like "The ‘Calendar Year’ attribute on the ‘Ship Date’ dimension has a generated dimension security expression that is not valid". Setting the same permissions on each of the role-playing dimensions in the cube based on Date, ie Ship Date, Delivery Date and Date does not cause a problem.

Lastly, what I will say after spending a week or so playing around with dimension security is that even in SP2 it is way more buggy that it should be. I’ve had no end of problems setting up the roles I want: usually everything works for a while and then suddenly the roles stop accepting any changes I make to them, ie you’ll make a change, save it, then test it and find that the change you’ve made has been forgotten about. I suspect something is getting corrupt somewhere. I’ve not managed to get a clear repro so that I can open a case with Microsoft about this, unfortunately, but it’s something to do with large dimensions and/or membership of multiple roles and/or visualtotals and/or securing measures and it’s happening enough to force us to rethink our entire approach to security. Hohum.