allowed member set MDX expression
- From: t.bolt@xxxxxxx
- Date: Tue, 06 Nov 2007 05:20:55 -0800
Hi,
I'm trying to implement dynamic dimension security in a SSAS 2005
dimension. My problem is, that I'm not able to create the required
allowed-member-set MDX expression. :-((
I have the "Cost Object" dimension consisting of two hierarchies:
- Cost Object (which is the primary key attribute)
- Cost Object Hierarchy (parent/child hierarchy)
I have also created a dimension "User", which contains the usernames
of the cube users.
One user can access one or more members of the cost object dimension.
This is stored in a fact table which references the user and the cost
object dimension. This measuregroup contains a member "Is
Allowed" (integer), which is 1 on the allowed combinations of users
and cost objects. Otherwise it is NULL, so there is no need to filter
out 0-values.
What I now need is an MDX expression, which returns a set of [Cost
Object].[Cost Object Hierarchy] members of the current user using the
username() function.
The only way I get this nearly to work, is the following MDX
expression, which sadly returns the members of the [Cost Object].[Cost
Object] hierarchy, which is the wrong hierarchy.
NonEmptyCrossJoin
(
[Cost Object].[Cost Object].[All].Children,
StrToMember("[User].[User Full Loginname].&[" + UserName() + "]"),
[Measures].[Is Allowed],
1
)
If I change it to the right hierarchy the following way, I also get
the Ascendants of the allowed members, which I don't need to set
security.
NonEmptyCrossJoin
(
Descendants
(
[Cost Object].[Cost Object Hierarchy].[All].Children
),
StrToMember("[User].[User Full Loginname].&[" + UserName() + "]"),
[Measures].[Is Allowed],
1
)
The problem is, that the Measure [Is Allowed] gets aggregated along
the [Cost Object Hierarchy] hierarchy if I use the aggregation-
function count or sum. This leads to non-empty ascendant-members,
which are returned by my MDX query.
I then used the aggregation-function "none", which - however - doesn't
work as described in the reference. It always returns NULL on the fact
members instead the fact value of the underlying table.
I hope someone can follow my explanations, and can find-out a solution
for my problem. :-))
Thank you!
.
- Prev by Date: Null totals in all measures for all dimensions
- Next by Date: Re: Null totals in all measures for all dimensions
- Previous by thread: Null totals in all measures for all dimensions
- Next by thread: Can't connect to SSAS 2000 cube
- Index(es):
Relevant Pages
|
