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.
Hi Chris,
I have run into the same situation where I make changes to a security role and the project doesn\’t recognize the change. The only solution I have found is to recreate the role from scratch. (Yahoo!)
On another note, I have also seen many instances where securing a measure drastically affects query performance. I have seen two cases where we denied access to a measure, for a role, and then all queries on all roles were negatively affected. To get around this I have instead denied access to the measures using cell security. For some reason this seems to get around the issue. BUT if you have a role using "dynamic security" then either approach will provide poor queries. Not sure if anyone else out there has seen this, but I have seen it replicated a few times.
Keep up the blog. Great stuff.
David Shroyer
obs3.com
Oh yeah, if you use cell security to secure a measure you will no longer have to worry about trapping for errors, in the mdx, because the measure will be "visible" to the mdx script, it will just give a value of "N/A"
Interesting you should say that about cell security, because in my experience (and I\’ve had this confirmed by Mosha) cell security can be a real performance-killer too: apparently having any form of cell security on a cube stops AS doing a lot of optimisations on queries. I guess, as always, you just have to go with the solution that works for your particular cube.
I totally agree. I always use cell security as a last resort. But it seems to get around the buggy behavior that I\’ve seen when a measure is secured.
Thanks
I went far down the path of implementing cell security on a project where we were trying to secure measures at certain levels of granularity for sales people. There were some significant challenges which forced us to reverse course including the fact that non-empty behavior is ignored and users with cell security defined were unable to perform drillthrough actions.
We then looked at simply securing the measures completely within the cube for certain roles. This ended up causing performance problems and errors as these measures were included in calculated measures. The solution we ended up going with, which is far from ideal, was to duplicate our cube, remove the measures and calculated measures from the copy, and give the sales people access to that cube only.
Now whenever we make changes to measure groups, calculations, perspectives, etc, we have to make them in two places. It seems absurd that this is so complicated to implement.
Chris,I came to read this topic from your book "Expert Cube Design", and is wondering if this issue still exists in AS08? I will do some test too, but want to ask first.Thanks,
Dimension security hasn\’t changed in functionality between 2005 and 2008 so yes, this post is still relevant
There is a bug here somewhere, either in the documention or or the security implementation. If you hit the help button on dimension security it suggests you can higlight a dimension and set access to None,Read or Read Write. Asumming "none" is supposed to restrict access to the entire dimension its omission is pretty serious …
I have 7 cubes and 1 perspective cube. All of this data is sales data. I am defining my security by accounts (which works fine). I’m also setting a dimension data security to one of the product defining attributes. At random times it will begin denying access to all attribute values, not just the single value defined. So it rolls up all of the totals but leaves out the details for the items. I have defined the dimension data permission on both the db dimension and the cube dimensions (I have assigned read and drilldown privilages for the roles.) If I duplicate the role, it starts working properly again. But at some random time, or what seems like a random time, it starts denying all values again.
Anyone have an idea on this?
It sounds as though your role is getting corrupted – but I have no idea why…
Chris,
I also made use of the SCOPE command and the named set to set roles on a calculated member. It works great for a user in a certain role. Howerver, when i select a user who can see everything, i have an issue. When i select a measure defined in the scope and another just normal (non calculcated) measure, the normal measure gets blank. When i remove the scope measure from the browser, the normal measure displays values again. What could be the cause of this issue ?
Thanks
Are you using the cube browser in BIDS? If so, it can be a bit buggy when there are scoped assignments – can you try using Excel or another client tool?
It is the same in Excel 2007. Actually if I select just the two measures, two values are displayed. The moment I add a (random) dimension member in the rows or columns, the value for the normal measure gets blank.
The normal measure is a sum and has it’s source straight from the dsv. For the measure defined in the scope i made a named calculation in the dsv and set it’s value to 0.
For the calculation itself, I used following code;
CREATE SET CURRENTCUBE.[setAverageCostPrice]
AS iif(iserror(strtomember(“Measures.[Average cost price]”)), {},
{Measures.[Average cost price]});
SCOPE(setAverageCostPrice);
THIS = Format([Measures].[Material Cost]/[Measures].[Invoiced Qtty],”#,#.00″);
END SCOPE;
Thanks
My guess is that the measure isn’t disappearing, but your calculation returns non-null values so it blows up the size of your pivot table so much you can’t see the measures that were there. Can you try the following please?
CREATE SET CURRENTCUBE.[setAverageCostPrice]
AS iif(iserror(strtomember(“Measures.[Average cost price]“)), {},
{Measures.[Average cost price]});
SCOPE(setAverageCostPrice);
THIS = iif([Measures].[Invoiced Qtty]=0, null, [Measures].[Material Cost]/[Measures].[Invoiced Qtty]);
FORMAT_STRING(THIS) = ‘#,#.00’;
END SCOPE;
Thanks for this article Chris. In regards to your second paragraph, I’ve been securing dimensions for years with just a single attribute allowed set; assuming attribute relations are in place. However, it’s never been to restrict all members (i.e. {}). Do we need to add allowed sets to each attribute for standard dynamic security or just for this specific scenario of restricting all members?
You only need to set the allowed set on all attributes for when you’re restricting all members; for any other scenario, whether it’s dynamic security or not, you just need to set the allowed set on the attributes you want to restrict.
Thanks for the reply. So we need to add allowed sets to any attribute we want secured? I thought that SSAS would use attribute relationships and AutoExists behavior to restrict related attributes if I restrict the key attribute (e.g. if I restrict CustomerID, CustomerName will also be restricted) Is that not the case?
No, sorry for not being clear – what I was trying to say was that if you add an allowed set to one attribute on a dimension, SSAS will automatically restrict all other attributes on the dimension appropriately. There’s no need to add an allowed set to every attribute unless you’re trying to restrict access to every member on every attribute, when you need to use an allowed set of {} for every attribute.
It sounds as though your role is getting corrupted – but I have no idea why
hi, I am new in ssas development.
I want to block a user group from viewing the all measures.
For this i had created a role and in role’s –> Cell Data –> in allow reading cube contents, I put the MDX as,
{NOT Measures.CurrentMember IS [MeasureNameHere]}
It works fine at summery level but when i view detailed report. It took so much time and performs the join operation and shows the repeated values in report.
I might be missing something in my query. Please guide.
Thank you in advance
Why are you using cell security? If these are regular measures you can use dimension security – that will be faster.
Chris, has the Dimension security changed in functionality in 2012? There is a requirement in my company to deny access to an entire dimension and apparently there is no simple/any solution to that in 2012 too.
No, there aren’t any changes to dimension security in 2012. It has never been possible to deny access to an entire dimension in SSAS, the only thing you can do is deny access to all the members on all the attributes.
How strange it is that the definitive source on the internet for SSAS dimensional security is a collection of comments and a blog post. Thanks so much for holistically answering what I’ve been researching for days.
Why do I bother even looking anywhere else, the answers are always in Chris’s blog. Spent days trying to use a specific member in SCOPE and not break everything when dynamic security excludes that member. Thanks again.
Always a pleasure, Harvey!
Thank you very much for your article.
Can also tell, how this approach can be used if dimensions are involved in calculations and they need to be granted with a role?
When we use the settings for “dimension data” it works with error that the element does not exist