I have the following Table:

MsgID MsgName Session Size FromUser FromDomain ToUser
ToDomain
1 aaa 1 504 me
mydomain.com harry aol.com
2 aaa 1 504 me
mydomain.com fred aol.com
3 aaa 2 504 me
mydomain.com mary Microsoft.com
4 bbb 3 234 you
yourDomain.com me mydomain.com
5 ccc 4 861 bill
hisone.com brad anotherone.com
6 ccc 4 861 bill
hisone.com paul anotherone.com
7 ddd 5 195 paul
anotherone.com george aol.com


From a Receiving point of view Domain "aol.com" would have the following
stats:
Messages = 2 (Distinct MsgName)
Recipients = 3 (Distinct ToUser + ToDomain)
Sessions = 2 (Distinct Session)
Size = 699 (Sum(Size) grouped by Session)

aol.com received 2 messages that went to 3 recipients. The cube should show
a total of 504 for the recipients of message "aaa" and 861 for the recipient
of message "ccc", the total being 504 + 195= 699 for the domain as a whole
(not the normal rollup value of 1203)

I've got Dimensions for:
Recipient as
Domain
User

Sender as
Domain
User

Measures as
Size


I want the cube to show the following:
Recipient Size
[All Domain] 2298
[aol.com] 699
[aol.com].[harry] 504
[aol.com].[fred] 504
[aol.com].[george] 195
[Microsoft.com] 504
[Microsoft.com].[mary] 504
[mydomain.com] 234
[mydomain.com].[me] 234
[anotherone.com] 861
[anotherone.com].[brad] 861
[anotherone.com].[paul] 861



How do I get the Size measure to only sum based on distinct session?


regards
Aaron

Hi,

You can create 2 meausres "Session Count" and "Session Size" and create
culculated memmber, whic aggregate "Session Size"/"Session Count" from level
of SessionID.

Sasha

"Aaron Lister" <alister (AT) ems-global (DOT) com> wrote