Hi Martin

Well, this is embarrassing.

One of the older versions now works. I used this as the calculated member:

COUNT(

NONEMPTY(

([Dim Employees].[GlobalID].[SGID],[Dim Phone Logins].[Phone Logins].CurrentMember),

[Measures].[Outliers - ACD Calls]

)

)

and it came back with the right result, in about 6 minutes. Previously, it had sat there for about 10 minutes without doing anything; or cancelled itself due to "locking conflicts".

I think the problem is that this cube is a bit of a mess (I didn't do it, honest! I inherited it...), and is hosted on an underpowered server. So I imagined that my earlier attempts were going out and looking at the entire cube (i.e. not the slice I specified in the WHERE clause), when they were working correctly, just not reliably or fast. I've confirmed by changing the slicer that this version isn't actually doing this, but only looking for and counting duplicates in the specified slice.

Lesson learned for me is that I'm going to test anything new like this on AdventureWorks, because the live cube is just too big and strange to be a good testbed. (Try it on a test version of the cube? Test version, what's that? We don't need no steenking test environment here... [facepalm]).

I did find out a lot about the EXISTING keyword. If I wrap the NONEMPTY above in EXISTING(), the query's much faster, but I get the wrong results (1 in every row). The documentation about EXISTING, I've found, is very confusing, in that it doesn't precisely define the "current context" that EXISTING restricts the set to. Some sources suggest that this "current context" is the entire subcube defined by the slicer (WHERE); others that it's also the current tuples in the query axes. In fact, it seems to be both:

a) if I use EXISTING but put only PhoneLogin on the Rows axis, I get the correct result (2 where there's more than one GlobalID)

b) If I then add the GlobalID to the Rows axis (cross-joined with PHoneLogin), I get only 1s: which suggests that EXISTING is also consulting the current members on the axes to work out the current context.

So, finally, thank you for helping me to sort this out - and apologies for leading you down a bit of a false trail.

Seb