Efficient way of filtering Dimension properties

Question

Hi.

From what I have read on MDX so far, it is best to try and utilise Bulk execution mode as often as possible when writing a query.

Therefore, if you wanted to Filter a set based on a particular Dimension property value, what is the most efficient way of doing so? As far as I understand it, using FILTER does not operate in bulk mode, but am not sure what the best alternative would be if any.

e.g. I think this kind of thing works:

set myset 
as

Filter({ [Employee].[Employee].[Employee].Members }
, [Employee].[Employee].CurrentMember.Properties("Gender") = "Male")

But is there anything better when the Dimension is many thousands or more members?

Thanks.

Mr HH

Answer 1

if Gender is exposed as an attribute you can use the EXISTS function

eg.

EXISTS( [Employee].[Employee].[Employee].Members, [Employee].[Gender].[Male] )

PS. you will find that declaring sets inline is often much faster than declaring named sets. 

---

http://darren.gosbell.com - please mark correct answers

Answer 2

Hi,

I was wondering if you could do something like: 

with member

Measures.Summator
AS
(
IIF(Employee.Employee.currentmember.properties("Gender")="Male", 1, NULL)
)

//Then later..

NONEMPTY(Employee.Employee.Employee.Members,Measures.Summator) On Rows

Regards,

MrHH