Advanced Dimension security in SSAS
Hi ,
Well first, for this new article, we’ll try English for a change, after this quick French foreword :
Nous n’oublions pas les francophones, et sur simple demande dans les commentaires, je serai heureux de vous proposer une traduction en Français et nous répondrons bien sûr en Français aux commentaires en Français.
I’ve recently had to dive in SSAS dimension security for an architecture advisory. And I’ve come across a two frustrating issues which, seeing the couple of unanswered related threads on the Net, deserve I think a blog entry.
All this is valid for SSAS 2005, 2008 and 2008R2.
Allowed/Denied set MDX validation in BIDS and dimension usage
What I wanted to do was to limit the “Geography” dim Members in Adventure Works for a given role, similarly to the following MDX query :
select exists([Geography].[Geography].[Country].&[France].children, [Product].[Product Categories].[Category].&[1], 'Reseller Sales' )on 1, {[Measures].[Reseller Order Count]} ON 0 from [Adventure Works]
The Axis 1 is limited to children members of France (therefore member State-Province level), for which exists Reseller sales.
This basically removes Charente, Gers, Moselle, Somme and Yvelines from the full 18 children list, down to a 13 member list.
Thus I (naively maybe, but I’ve to admit our product documentation is not too detailed here) copy pasted my axis definition in my new role “Dimension Data’ Advanced tab for the State-Province attribute.
Syntax Checking from within the popup MDXBuilder window was happy with it… But, the ‘Check’ button from the Advanced tab didn’t agree ! It threw the following error :
Check MDX script syntax failed because of the following error:
An error occurred in the MDX script for the dimension attribute permission's allowed member set: Query (2, 5) La dimension '[Product]' était introuvable dans le cube lors de l'analyse de la chaîne [Product].[Product Categories].[Category].&[1].
The full english message being :
An error occurred in the MDX script for the dimension attribute permission's allowed member set: Query (2, 5) The dimension '[Product]' was not found in the cube when the string, '[Product].[Product Categories].[Category].&[1]. ' was parsed
I tried many things to get through this message, retyping the syntax differently etc… Finally, I found out that there’s a bug in the GUI that will throw this error whenever a dimension different from the one being secured is referenced.
Even if “Check” is not happy, one can save the role and the security will work ! This GUI bug impacts 2005, 2008 and 2008R2.
Attribute vs User Hierarchy
Well, I had eliminated a first issue, and ignored the warning but then I discovered this new error when browsing the cube in BIDS under my role’s context:
L'attribut 'State-Province' dans la dimension 'Geography' comporte une expression de sécurité de dimension générée qui n'est pas valide.
English version :
The 'State-Province' attribute in the 'Geography' dimension has a generated dimension security expression that is not valid.
What was worrying is that I got the same error when defining my allowedset to the following : {[Geography].[Geography].[State-Province].&[91]&[FR]}. So nothing to do with referencing another dimension.
So I played it humble, and checked what the “Basic” Tab interface would generate if I clicked ‘Essone’ in the member list GUI : it generated {[Geography].[State-Province].&[91]&[FR]}
Notice the subtle difference between a user hierarchy ([Geography].[Geography].[State-Province]) and attribute hierachy ([Geography].[State-Province]) ?
Well we’re securing attributes, not user hierarchies (which may or may not reflect the attribute relationships by the way). So on second thought it’s not surprising that the engine is expecting a list of members of the very attribute hierarchy being secured.
The important thing is what the MDX expression returns. We can use User Hierarchies in the expression, as long as we end up with a set of the secured attribute’s members at the end, so where {[Geography].[Geography].[State-Province].&[91]&[FR]} would fail, this would succeed :
linkmember( [Geography].[Geography].[State-Province].&[91]&[FR], [Geography].[State-Province] )
Therefore I rewrote my initial expression as follows :
Exists( Exists ( [Geography].[State-Province].[State-Province].MEMBERS ,[Geography].[Country].&[France] ),[Product].[Product Categories].[Category].&[1],'Reseller Sales')
Which ends up selecting the same members as with my initial MDX statement, but defined by their attribute hierarchy ‘coordinate’ this time. This expression still generates the ‘Check MDX script syntax failed’ error in BIDS GUI, but as mentioned earlier, ignoring it and saving the role will then enable the expected security behavior when browsing the cube under the role security context.
(NB : linkmember only works against single members, and therefore requires a more complex GENERATE-based query if a set needs to be processed.)
Conversely, using [Geography].[Geography].[City].&[Lille]&[59] (ie a member of the City attribute) as MDX expression for State-Province attribute won’t raise any error with the ‘Check’ button, since it references an attribute hierarchy of the current dimension, but it will expectedly generate an error when we browse the cube and security is evaluated.
Hope this can help avoid some frustration for SSAS users :)
Guillaume Fourrat