Hi Martin
Thanks for your help with this!
Martin Schoombee (5/22/2015)
First, let's correct some dimensionality issues. Instead of doing the following:
[Dim Phone Logins].[Phone Logins].Members - [Dim Phone Logins].[Phone Logins].[All]Just step one level down:
[Dim Phone Logins].[Phone Logins].[Phone Logins]
That's great advice. I didn't realise I could use the level expression [Dim Phone Logins].[Phone Logins].[Phone Login] (the last has no "s", for some reason) to return all the members of the level. This makes the query far simpler to read.
The real issue with your query is the calculated member. Imagine it as a pseudo-cursor, executing for every combination of Phone ID and Employee Global ID. While you are attempting to make the calculation aware of where you currently are in the Phone Logins dimension (one level too high in the attribute hierarchy though), you are not doing the same for the Employee dimension.
Here the problem is that I'm trying to count the number of of measure values using a different grouping from the main query. For the measure value, I'm effectively doing a GROUP BY PhoneLogin,GlobalID. What I want for the count is the count GROUP BY (only) PhoneLogin; but counting only within the set that the query returns. (Typically, the count of GlobalIDs per PhoneLogin should be 1, but is sometimes 2 or 3: which is the case I'm trying to detect).
Your alternative calculated measure:
count
(
nonempty
(
{[Dim Employees].[GlobalID].currentmember}
* {[Dim Phone Logins].[Phone Logins].currentmember}
,[Measures].[Outliers - ACD Calls]
)
)
(or your even simpler alternative COUNT(NONEMPTY([Measures].[Outliers - ACD Calls])) ) works fast and correctly returns the COUNT "GROUP BY PhoneLogin,GlobalID" - which is always 1.
So what I'm trying to do here is:
- Get results based on summing the measure value by PHoneLogin, GlobalID (in a particular slice of the cube).
- From that set, also get a count of GlobalIDs GROUP BY PhoneLogin.
I can't figure out a way to do this. I did experiment with putting the main query (without the COUNT) into a subquery; but doing that I run into this error
A set has been encountered that cannot contain calculated members.
Apparently I have to set the SubQueries connection property to 1 to avoid this (I'm guessing the calculated member it's objecting to is the cross-joined PhoneLogin/GlobalID on axis 1). I've tried this (I'm using SSMS) but it doesn't seem to make any difference.
I could really use some more help if you have time to look into this again!
thanks and regards
Seb