MDX WHERE NOT IN() equivalent in many-to-many dimension

3
votes

I have a many-to-many dimension in my cube (next to other regular dimensions). When I want to exclude fact rows in my row count measure, I usually do something like the following in MDX

SELECT [Measures].[Row Count] on 0
FROM cube
WHERE ([dimension].[attribute].Children - [dimension].[attribute].&[value])

This might seem more complicated than needed in this simple example, but in this case the WHERE can grow sometimes, also including UNIONs.

So this works for regular dimensions, but now I have a many-to-many dimension. If I perform the trick above it does not produce the desired result, namely I want to exclude all rows that have that specific attribute in the many-to-many dimension.

Actually it does exactly what the MDX asks, namely count all rows, but ignore the specified attribute. Since a row in the fact table can have multiple attributes in a many-to-many dimension, the row will still be counted.

That's not what I need, I need it to explicitly exclude rows that have that dimension attribute value. Also, I might exclude multiple values. So what I need is something similar to T-SQL's WHERE .. NOT IN (...)

I realize that I can just subtract the resulting values from [attribute].all and [attribute].&[value], but that won't work any more when UNIONing multiple WHERE statements.

Anybody got a good idea on how to solve this?

Thanks in advance,

Delta

3
ssasmdx

3 Answers

1
votes

I have not tested this, but I think you could do this if you had an attribute that was at the same level of granularity as the rows (so probably implemented as a fact relationship).

So if you wanted to count the number of orders that did NOT have a product category of bikes (assuming a M2M relationship between OrderID and Category) then something like the following should work. (you can find more info on the EXISTS function in Books Online)

[Orders].[Order ID].[Order ID].Members 
- EXISTS([Orders].[Order ID].[Order ID].Members
       , [Product].[Category].&[Bikes]
       , "Order Facts")

Although it could be quite slow as this sort of query is forcing the SSAS engine to add up a lot of facts from a low level.

0
votes

Have you tried the EXCEPT command? It's syntax is like the following:

EXCEPT({the set i want}, {a set of members i dont want})

0
votes

You could use the Filter function:

SELECT [Measures].[Row Count] on 0
FROM [cube]
WHERE Filter([dimension].[attribute].Children, [dimension].CurrentMember.MemberValue <> value)