SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dimension security by using MEMBER_NAME


Dimension security by using MEMBER_NAME

Author
Message
Martin_A
Martin_A
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 441
Hi all,

In a dimension I have one attribute where the very same MEMBER_NAME occur for several members.
For example, all the following unique members share the same member_name "MyMember".
[Dimension].[Hierarchy].[Level].&[1]&[592]
[Dimension].[Hierarchy].[Level].&[2]&[592]
[Dimension].[Hierarchy].[Level].&[3]&[592]

What I would like to do, in dimension security, for a specific role, for this specific attribute, is to make use of the MEMBER_NAME property when defining the allowed member set.
Like this: " all members with the MEMBER_NAME = "MyMember" " should be allowed.

Is this possible to do? If so, I would be really thankful for some help on how to write the correct MDX statement for this.

Thanks,
Martin
Martin_A
Martin_A
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 441
This did the trick:
filter
([Dimension].[AttributeHierarchy].allmembers
,instr([Dimension].[AttributeHierarchy].CurrentMember.MEMBER_VALUE, '592') > 0)


Martin_A
Martin_A
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 441
As per suggestion from Darren Gosbell I changed the way of solving this, to get better performance:
I created a new, hidden attribute where the key is only the second part of the composite key in the other attribute. Then I reference one single member of the new attribute hierarchy as the allowed set:
[Dimension].[AttributeHierarchy_hidden].&[592]


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search