Hi, 

I'm working a project where we have a dimension with approx 10.000.000 members. To find out which members a user is allowed to see we have a User dimension and a factless fact between that and the dimension where the security is implemented. The way the MDX is implemented I return the members a user is allowed to see based on the rows in the fact less fact using Exists() or NonEmpty(). However when this set is empty the user should have access to all 10.000.000 members. The dimension with the 10.000.000 members is hidden and the user is never able to browse it or use it, it's only used for implementing the security.

The problem is this: when the allowed set is empty the user should see all 10.000.000 members so my MDX return [Dimension].[Dimension Key].[All].Children which just doesn't perform at all. 

There is no problem at all when the user only has access to a few thousands or less members of the 10.000.0000 member dimension.

My question is this: Can I somehow avoid returning [Dimension].[Dimension Key].[All].Children and just do nothing, or somehow smarter return all the members in the dimension as the allowed set?

Thanks.

I assume you mean that you want the data set to be filtered on the 'n'members that the user is allowed to see which

0 <= n <= 10,000,000 

depending on the access level but that the members themselves are not being displayed in 0 or 1 or other axes.

If that is correct, can you simply use a subcube within your MDX. For example, if the Dimension contains Products and only some users are allowed to see sales of certain Products, but the CFO can see sales of all Products, the MDX for the CFO would be something like the below. (you can modify to include your factless fact accordingly)

select 
{
	[Measures].[Order Amount]
} 
on 0,
{
	[Region].[Countries].members
}
on 1
from 
(
	select [All Products].[Product ID].[Product ID]
	on 0
	from [MyCube]
)

This will have the effect of reducing your dataset size right at the very beginning.

(Please mark as answered, if this is what you are looking for)

• Proposed as answer by Shri I Friday, January 30, 2015 2:55 PM

Hi, 

Thanks for the reply, but I'm fraid that's not it. Now the MDX I use in Dimension security is something like this:

IIF(NonEmpty ([Customer].[CustomerNumber].Members,(StrToMember("[Employees].[Login].&[" + Username + "]"),Measures.[Security Filter Count])).Count = 0, 

[Customer].[CustomerNumber].Members, 

NonEmpty ([Customer].[CustomerNumber].Members,(StrToMember("[Employees].[Login].&[" + Username + "]"),Measures.[Security Filter Count])))
The [Customer] dimension is 10.000.000 members so returning 
[Customer].[CustomerNumber].Members is the problem.

Hi,

Create a new role for users who has access to all customers. This is not really dynamic security. All users with limited access will be in dynamic security role and all users with full access will be another role with no restrictions.

Hope this helps.

Arun

Hi,

Unfortunately that will not work because the factless fact table is updated all the time (ROLAP) so all users will need to fall into the same role to handle this.

Since the customer dimension is hidden, I wonder whether you could try {[Customer].[CustomerNumber].[All]} instead of  [Customer].[CustomerNumber].Members

regards

Arun

Thanks Arun,

However, that will not work either. I'm wondering if this is at all possible with a dimension this size or if I will need to consider other designs.